Alternative to IF

I use an IF function to compare two values to confirm they balance and put a 1 if they don’t balance and a 0 if they do balance. Something like =IF(A1=B1,0,1). I then SUM the 1’s to see how many don’t balance. Is there an easier way?

By Neale Blackwood
In Excel you don’t need to use the IF function to do a logical comparison between two values. Assume columns A and B have values. In C1 you could enter the formula =(A1<>B1)*1 to display 1if the two numbers DON’T balance and 0 if they do balance. Using <> (the less than symbol followed by the greater than symbol) means “not equal to” in Excel.

You can then use a SUM formula to find out how many errors there are.

The logical test (A1<>B1) will calculate to either TRUE or FALSE. In computing (binary) terms TRUE = 1 and FALSE = 0. So when the logical test in the brackets is multiplied by 1 they calculate to 1 or 0, which can then be added up.

 

Related Articles
Using MAX and MIN instead of IF function – I must calculate the time-and-a half and double-time hours from a total overtime figure – in column C. The first two hours of overtime are time-and-a-half, the rest is double-time. Can you provide IF functions to do the calculations?
Many of the questions I receive are based on IF functions and how to use them correctly. Assuming the first row used is Row 2, the formula for time-and-a-half would be =IF(C2>2,2,C2) …
Using the AND + OR functions – I struggle to use the AND and OR functions. Could you explain how they should be used?
AND and OR functions are typically used with the IF function. They allow you to examine multiple logical tests in a single function rather than use numerous IF functions to achieve the same result …