• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

polarisking

polarisking

Member
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.
 
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.
 
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
 
NARAYANK991 said:
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
Click to expand...
But it's also true when the numbers are different. Consider {2, 3, 3, 4}.
 
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".
 

Attachments

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 :awesome: 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.
NARAYANK991 said:
Hi ,

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

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

Narayan
Click to expand...
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 . . .
 
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.
 
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.
 
You must log in or register to reply here.
Back
Top