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.
|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 …|