Why use the IF function when there is an easier way to do it? If you’re looking for a TRUE or FALSE answer, you don’t need it! Let’s use Boolean logic instead – it will save you a lot of time and struggle.
Let’s look at an example: In this sales report, the sales reps get a bonus if they reach $ 15,000 in sales.
Most people would solve this with an IF statement such as =IF(C5>$C$2,”TRUE”,”FALSE”), but there is an easier way:
This formula will return TRUE or FALSE. Of course, in order to copy the formula down, we need to lock the reference to the bonus hurdle in C2 with dollar signs (Shortcut: F4):
Getting used to this logic also makes conditional formatting a lot easier! Conditional formatting relies on the TRUE/FALSE logic – if the statement is true, the cell is formatted. All you have to do here is to select all the cells in the bonus column and format them with the very same formula as above.
Click on Conditional Formatting from the Home ribbon and choose New Rule (Shortcut: Alt => H => L => N). Choose “Use a formula to determine which cells to format” and type the formula in the formula field.
Click on the Format button and choose a fill colour or font colour.
That’s all, and here is the result:
Bonus tip: If you want to replace TRUE and FALSE with ones and zeroes, it’s done in a second:
Put the logical statement in brackets and add a zero: =(C5>$C$2)+0
Or put the logical statement in brackets and add a double negative: =- -(C5>$C$2)
This could be a good idea if you want to use the results from column D for further calculations:
Note: If you use comma as the decimal separator as default (applies to most non-English users) you have to replace the commas in the formulas with semicolons.
Author: Audun Danielsen