Compare multiple cells in Mac Numbers

1.The intuitive way of multiple cell comparison doesn’t work in Mac Numbers

Occasionally, I want to compare not just 2 cells but multiple cells at the same time. For example, I want to make sure Bob, Han, and Jake are at the same age.

Intuitively, I write “Bob = Han = Jake ” in the IF formula. And the result is wrong. All three have the same age. But the result is “No”. The IF formula could not work simply because it doesn’t understand the syntax of multiple equal signs like humans do. Take a look at the screenshots below.

Mac Numbers carries out the comparison one by one and from left to right when I write multiple equal signs like that. In the above example, it compares Bob to Han first. The result is “TRUE”. Then it compares the “TRUE” result to Jake’s age. Because “TRUE” doesn’t match the number 5, the IF formula shows “No” answer.

2.The right way to compare multiple cells in Mac Numbers

After some research, it turns out Mac Numbers can only compare two cells at a time. I must stack multiple two-cell comparisons together and link them with the “AND” operator. Therefore, the correct syntax for the above example is “Bob = Han” AND “Bob = Jake”. I must rewrite the IF formula as follows.

Mac Numbers carries out each comparison in the AND operator before it combines the results. For example, It compares Bob’s age to Han’s age. Then it compares Bob’s age to Jake’s age. Both the comparisons return “TRUE”. Then “TRUE” AND “TRUE” shows a YES result. 

The first time I used multiple single comparisons in the AND operator, I thought it was complicated and not intuitive. You probably feel the same. 

But after a while I got used to it and prefer using it over the old way. The advantage of single comparisons is that you can test them one by one. Simply copy and paste each comparison in the AND operator to a new cell. You can see the result immediately. 

There is no way you can visualize the process behind the complex combined comparison.