Where N > 2, Test to see if all N values are equal

[polarisking]

Obvious answer uses the Transitive property we learned early on; you know, if A = B, and B= C then A = C. Could be written as

=AND(Cell01=Cell02, Cell02=Cell03) = True

For N values, though, is there an elegant using a CSE formula, perhaps? or something else?

Thank you, in advance.

 

[Luke M]

Perhaps something like
=COUNTIF(A:A, A1)=COUNT(A:A)

which checks if all the numbers in col A are equal to A1.

 

[Peter Bartholomew]

You could maintain the symmetry of the operation by using
= AND( range=TRANSPOSE(range) )
or, to avoid CSE, use a name 'identical' to hold the inner formula
{= ( range=TRANSPOSE(range) )}
and then
= AND( identical )
gives the result without CSE.
This works because it is only formulas on the grid that block array calculation by performing implicit intersection.

With modern dynamic arrays available within versions of Office 365, you could use UNIQUE and count the results
= ROWS( UNIQUE( range) ) = 1

p.s. If the cell contents are not numbers @Luke M's formula
= COUNTIF(range, cell01)=COUNTA(range)
would need COUNTA rather than COUNT.

 

[NARAYANK991]

Hi ,

Mathematically , if all the numbers in a set are the same , then SUM(all the numbers) = COUNT(all the numbers) * number.

=SUM($A$1:$A$10) = COUNT($A$1:$A$10)*A1

will be true if all the numbers are the same.

Narayan

 

[Luke M]

Hi ,

Mathematically , if all the numbers in a set are the same , then SUM(all the numbers) = COUNT(all the numbers) * number.

=SUM($A$1:$A$10) = COUNT($A$1:$A$10)*A1

will be true if all the numbers are the same.

Narayan

But it's also true when the numbers are different. Consider {2, 3, 3, 4}.

 

[NARAYANK991]

But it's also true when the numbers are different. Consider {2, 3, 3, 4}.

Hi Luke ,

I agree. Thanks for pointing this out.

Narayan

 

[NARAYANK991]

Hi ,

What about the minimum and the maximum in the set being equal ?

=MIN($A$1:$A$10) = MAX($A$1:$A$10)

Narayan

 

[GraH - Guido]

Since we are open to suggestions, why not use PQ?

Load table of data (numbers or not) to PQ and add an Index
Reference this first query and group by on the index "All Rows"
Add custom column "Transpose" with the following formula = Table.FirstN(Table.Transpose([All]), Table.ColumnCount([All])-1)
Add a second one "All Equal" (or wrap around first) = List.Count(List.Distinct(Table.Column([Transpose], "Column1"))) = 1
Merge both tables on the Index and expand the "All Equal" column only
Load to Excel.

For reference; file attached.

Second alternative could be to remove "nulls" as well. List.Count(List.RemoveNulls(List.Distinct(Table.Column([Transpose], "Column1")))) = 1

Since PQ is case sensitive, the equal is also "exactly the same".

 

[polarisking]

I appreciate all these responses.

My specific situation is that I have 4 dates each contained in a variable name dimmed as DATE.

I suppose I could invoke a temporary step by writing them into a contiguous vertical or horizontal range, and do the COUNTIF technique. I was hoping that someone has some obscure, super elegant way to do the N variable compare returning TRUE if all were equal, and false if any one weren't equal.

Hi ,

What about the minimum and the maximum in the set being equal ?

=MIN($A$1:$A$10) = MAX($A$1:$A$10)

Narayan

This is incredibly elegant. For numbers and dates, this works. Especially in VBA where the variables are discreet and not contiguous.

=worksheetfunction.min(val01,val02,val03,,,,valN) = worksheetfunction.max(val01,val02,val03,,,,valN)

Text values are a whole other animal . . .

 

[Peter Bartholomew]

This is still worksheet formula based rather than VBA. First multi-select every number you are interested in and then define the selection to be a named range 'multiCell'. @NARAYANK991's second solution (post #7) works fine with a multi-area range, so
= ( MIN(multiCell)=MAX(multiCell) )
gives the desired result.

 

[Peter Bartholomew]

It is no longer relevant but a formula to 'get all one's ducks in a row' is
= CHOOSE( {1,2,3}, cell01, cell02, cell03 )
The formula
= AND( array=TRANSPOSE(array) )
works on the resulting array.