How can I check if the values of multiple cells are equal?

Let's say I have 6 different cells (that are not all in a line). I want to check if the values in these cells are equal. How could I do this with a function? I'd want the function simply to display "EQUAL" or "NOT EQUAL" (or maybe change the cell background color?).

Answers


One option for 6 cells would be this:

=IF(AND(A1=B2,B2=C3,C3=D4,D4=E5,E5=F6),"EQUAL","NOT EQUAL")

Another option - this way you don't need to reference the same cell twice:

=IF(AND(ARRAYFORMULA(A1={B2,C3,D4,E5,F6})),"EQUAL","NOT EQUAL")

If you wanted to color some cells if values in these cells are equal, you would need to create a Conditional Formatting rule with a similar formula:

  1. Select the cells you want to color
  2. Format > Conditional Formatting
  3. Select "Custom formula is"
  4. Fill in one of the above formulas without the IF part of formula, e.g. =AND(ARRAYFORMULA(A1={B2,C3,D4,E5,F6}))
  5. Select the formatting style (color)
  6. Done

as formula for conditional formatting:

=countunique({A1,B2,C3,D4,E5,F6})=1

as function it would be similar: =if([formula],"EQUAL","NOT EQUAL"):

update: the requested clarification:

  • put the wanted cells in a custom array {A1,B2,C3,D4,E5,F6} (delimiters: , = new column, ; = new row; for countunique either is fine)
  • get the unique values countunique(...)
  • if the outcome is 1 then all values are the same

update 2: original answer used =count(unique(...)) instead of the combined function =countunique(...)


Here's an option without using an array that is easier to implement for large numbers of cells:

=IF(MIN(A1,B2,C3,D4,E5,F6)=MAX(A1,B2,C3,D4,E5,F6),"Equal", "Not Equal")

Need Your Help

Does Azure offer https for "cloudapp.net"?

azure https azure-web-roles azure-web-sites azure-vm-role

One great advantage of using Azure Websites is that I can get secure HTTP (HTTPS) without doing nothing: I simply type https://xyz.azurewebsites.net and it works. I don't have to worry about certif...

Call a C++ base class method automatically

c++ class methods base-class

I'm trying to implement the command design pattern, but I'm stumbling accross a conceptual problem. Let's say you have a base class and a few subclasses like in the example below: