Last week in Write a formula to check few cells have same value [Homework],
Chandoo posed the question: Can you write a formula to check if a few cells are equal?
A lot of people answered with answers like:
=IF(COUNTIF(A1:A4,A1)=COUNT(A1:A4),TRUE,FALSE)
or
=IF(SUM(A1:A4)/COUNT(A1:A4)=A1,TRUE,FALSE)
or
=IF(PRODUCT($A:$A)=A1^(COUNTIF($A:$A,A1)),”Yes”,”No”)
etc.
Although these are all correct, the formulas can be simplified by using a built in function of Excel
The function is =
=
In Excel the = sign does two things
1. When the = sign is the first character of a Cell, Named Formula or Reference it tells the Cell, Named Formula or Reference that it contains a formula
2. It acts as an equality function when it has a value or formula on either side of it
What does the = function do?
What does the = function do?
The = function returns a Boolean or TRUE / FALSE value depending on the equality of the values/formula on either side of it
Example:
In a blank cell enter =2=1
Excel will display FALSE to tell us that the values aren’t equal
Now change the formula to =2=2
Excel will display TRUE to tell us that the values are equal
Either side of the = sign can contain a Formula, Cell Reference etc
=(200/100)=2 will return TRUE
=SIN(PI()/2)=1 will return TRUE
=SIN(PI()/2)=15 will return FALSE
=-1=COS(PI()) will Return TRUE
=SIN(PI()/2)=A1 will return TRUE if cell A1 contains either a 1 or a formula that returns the value 1.
It will return False for all other values.
So the above, Homework, formulas can be simplified as :
Original: =IF(COUNTIF(A1:A4,A1)=COUNT(A1:A4),TRUE,FALSE)
Simplified: =COUNTIF(A1:A4,A1)=COUNT(A1:A4)
or
Original: =IF(SUM(A1:A4)/COUNT(A1:A4)=A1,TRUE,FALSE)
Simplified: =SUM(A1:A4)/COUNT(A1:A4)=A1
or
Original: =IF(PRODUCT($A:$A)=A1^(COUNTIF($A:$A,A1)),”Yes”,”No”)
Simplified: =PRODUCT($A:$A)=A1^(COUNTIF($A:$A,A1))
and all will still, correctly, answer the question
Challenge:
If your formula started with an If(), see if you can rewrite it based on the above.
19 Responses
Sorry but =SUM(A1:A4)/COUNT(A1:A4)=A1 will fail in the case of: {2,1,3,2}
Sum = 8, Count = 4 Sum/Count = 2 = A1 = True but 2<>1<>3<>2 although 2 =2!
Why not something even more simple?
=AND(G2:G5=H2:H5)
Ctrl+Shift+Enter
If you insist on the “If..”, then
=If (And(G2:G5=H2:H5),”True”,False”)
Ctrl+Shift+Enter
Please ignore above…I misread the question
Please use the below
={If (And(G2:G5=G2),”True”,False”)}
Ctrl+Shift+ Enter
Or
={AND(G2:G5=G2)}
Ctrl+Shift+Enter
Went back and saw that the formula I suggested is very similar to the obvious answer…so here is another
=IF(COUNTA(I4:I7)>SUMPRODUCT(–(I4:I7=I4)),”False”,”True”)
Dan u good ! Learned a new thing today.
Here’s my new formula
Old Formula: {=IF(SUM(IF(A1=$A:$A,1,0))=COUNTA($A:$A),TRUE,FALSE)}
New Formula: {=SUM(IF(A1=$A:$A,1,0))=COUNTA($A:$A)}
your formula with the equal sign to check ceels for equality works correctly with numbers but not with letters.
Should you want to compare cellular equality with letters or text, use the =EXACT(D4,D) The result will be either TRUE or FALSE.
MC
Quick question, what does the ^ in
=PRODUCT($A:$A)=A1^(COUNTIF($A:$A,A1)) do?
^ is “to the power of
e.g. 2*2 could be written as 2^2
2*2*2 could be written as 2^3
^ raises to the power of.
But that formula fails for {2,2,1,1} Product is 4, countif is 2, 2^2 is 4 but whilst 2=2 & 1=1, 1<>2.
My solution is: COUNTDIFF(A1:A4,1,)=1
Excel function COUNTDIFF counts the number of unique values in a range or an array.
SYNTAX :
=COUNTDIFF(Array,Blanks,Exclude)
– Array (range or array) : can contain any type of data (numbers, text, empty cells…).
– Blanks (boolean, optional) : specifies if empty cells must be taken into account (default : FALSE).
– Exclude (value, range or array, optional) : value(s) which must be excluded from the count.
Unlike many posted solutions, this function allows handling blank cells.
My bad.
COUNTDIFF is not Excel build in function, but part of free MoreFunc add-in.
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html
A free library of 66 new worksheet functions for Excel 95 or above.
@leonid
Thank you! this is a great function. Did not know about it.
Ah must be in 2007+. It no worky in 2003
@Andrew
Did you install Analysis ToolPak?
=COUNTIF($A$1:$A$4,A1)>1
is this correct? not sure if I understood the question correctly.
At the moment I am trying to learn array formulas,
is this also correct ?
{=A1:A4=A1}
sry this was wrong, but the following should work
{=MIN((E15:E19=E15)*1)}
Thanks man. You are a live saver 😀