Sunday 28 March 2021

Excel basics - The IF Formula

Occasionally when using Excel you want to analyse the value in a cell and either calculate something differently as a result of the analysis or write something to a cell.  To do this, your friend is the "IF formula".  

Here's an example where I want to analyse the values in column A and write "TRUE" or "FALSE" to column B depending on whether the column A number is positive or negative.  In the image below I show some simple data I've setup in column A and where I've started typing the IF formula in column B to show the different elements of the formula:









Taking each element of the formula in turn:

  • logical_test - This is the test you're doing on the values in column A.  So for this formula I'll write "A2>0" meaning "test if A2 is greater than zero".
  • value_if_true - This is what to put in column B if the logical_test is true.
  • value_if_false - This is what to put in column B if the logical_test is false.

Filling out the first formula I now have:











...and copying the formula to the rest of column B I see:













Note how Excel has used it's clever relative references capability to update the formula as it's copied down to refer to the correct row.

So that's it.  Let me know in the comments below how you use the IF formula.

No comments:

Post a Comment