Countif IfAndOr VLookup

  

IF

Apart from the fact that IF is a rather good word, it can also help get rid of #DIV/0! in Excel. You can make a lot of spreadsheets look better and a lot of people happy.

Whenever you divide something by zero, which shouldn't happen but is always the case, the cell shows #DIV!0. For example, there are two columns with numbers (e.g. Actual versus Plan, versus the last ten or twenty years...) you want to compare. A third column is added in order to calculate a percentage, e.g. =C4/C3. A small function avoids #DIV!0 showing up in all those percentage cells that happen to divide by zero.
All functions need arguments: in Excel arguments are seperated by a ";"

The IF function has 3 arguments: IF(this is the case;then do this;otherwise do this)

  

=IF(B3>0;C3/B3;"") If the number in B3 is bigger than zero, then divide C3 by B3, otherwise show an empty cell.
=IF(B3>0;C3/B3;"increase!") If the number in B3 is bigger than zero, then divide C3 by B3, otherwise write increase!
=IF(B3>0;C3/B3;"--") If the number in B3 is bigger than zero, then divide C3 by B3, otherwise write --

The first and third option seem to be very popular (leave an empty cell or write --). However, whatever is written between those two quotes of the last "otherwise" argument will appear when the first argument is false. The two quotes turn the cell into text > see IF, AND, OR. Sure there is the possibility to write a formula into the third otherwise-argument, in that case no quotes are needed, as calculation with numbers doesn't use quotes, whereas defining text in a function requires quotes.

PDF File  These pages are available as print-friendly PDF (click icon).
About