Check Cells for Equality – Follow Up [Quick Tip]

Posted on October 16th, 2012 in Huis , Posts by Hui , Quick Tip - 19 comments

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.

Written by Hui...
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

19 Responses to “Check Cells for Equality – Follow Up [Quick Tip]”

  1. Mawdo81 says:

    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!

  2. Renjith M Das says:

    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

  3. Renjith M Das says:

    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

  4. Renjith M Das says:

    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")

  5. Jorrie says:

    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)}

  6. Valentin says:

    your formula with the equal sign to check ceels for equality works correctly with numbers but not with letters.

    • Mike Coffey says:

      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

  7. shairal says:

    Quick question, what does the ^ in
    =PRODUCT($A:$A)=A1^(COUNTIF($A:$A,A1)) do?

     

  8. ^ 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.

  9. Leonid K. says:

    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.
     

  10. Andrew says:

    @leonid

    Thank you! this is a great function. Did not know about it. 

  11. jeje says:

    =COUNTIF($A$1:$A$4,A1)>1

    is this correct? not sure if I understood the question correctly. 

  12. Quotenjunkie says:

    At the moment I am trying to learn array formulas,

    is this also correct ?

     {=A1:A4=A1}

  13. Ankit says:

    Thanks man. You are a live saver 😀

Leave a Reply