##### 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