Free tips and case studies
Free weekly tip and monthly journal of tips and case studies where you can see what others achieve with Excel
Example weekly tip
Make your nested IF formulas easier to understand
A formula like that below is hard to follow. It just wraps around in the Formula bar. It's a typical formula with nested IFs.
=IF(AO505=0,"N/A",IF(OR($AP$505="Tiles supplied by customer",AO498="No"),"Coping tiles supplied by customer.",IF($AR$505,"See Options",IF(OR($AP$505="",$AP$505="Not selected"), AP507,"Coping tiles supplied from "&AP505&" - "&AQ505))))
You can rearrange the formula as below to make it easier to follow. IF has three arguments: (1) Test (2) Value if test is true (3) Value if test is false. Note the layout below where the second argument is below the IF and indented by 4 spaces. The third argument is on the next line down and also indented by 4 spaces.
With the indentation below, you can see the four levels; the first IF contains another IF, which contains another, which contains another. The logic flow is now much easier to follow.
=IF(AO505=0,
"N/A",
IF(OR($AP$505="Tiles supplied by customer",AO498="No"),
"Coping tiles supplied by customer.",
IF($AR$505,
"See Options",
IF(OR($AP$505="", $AP$505="Not selected"),
AP507,
"Coping tiles supplied from "&AP505&" - "&AQ505))))
In creating a formula, to start a new line, press Alt+Enter
To indent, simply type spaces. We recommend you enter 4 spaces to indent a level.
Free weekly tip and monthly journal of tips and case studies where you can see what others
achieve with Excel