Make decisions with IF()

=IF(condition, what to return if it is true, what to return if it is false)

  • Condition: A logical comparison that may be true or false. A question.
  • What to return if it is true: A number, text (enclosed in quotes), or formula to perform if the condition is met.
  • What to return if it is false: If the condition is not true, this number, text (enclosed in quotes), or formula is returned.

For example, let's say a company pays its salespeople 10% commission if their weekly sales were under $1000, but pays 15% if sales were equal to or greater than $1000. Assuming you had named a column "WeeklySales" (remember how important creating names is?), you could calculate commissions with this formula:

=IF(WeeklySales<1000,WeeklySales*10%,WeeklySales*15%)

Or, you might simply say:

=IF(WeeklySales<1000,"Work Harder","Great Job!")

IF functions are particularly useful to avoid seeing error messages or zeros. Let's say you have an invoice form on a worksheet. When someone orders an item, a line is filled in and the extended price column produces the result with the formula =Price*Quantity. But for lines that are blank, you will get a lot of zeros (=0*0). To make your invoice more aesthetic, put the extended price formula inside an IF function:

=IF(Quantity="","",Price*Quantity)

  • Note a blank is indicated with two double-quotes, "".

If there is nothing ordered on that line (i.e. Quantity is blank) then a blank is returned. If there is a number in the Quantity column, the extended price formula is evaluated and returned.

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish