Add to formula
This utility allows the user to add another IF statement to a preexisting formula. In Figure 1, cell A20 is selected. It already has a formula in it which is:
=IF(OR(ISERROR($A$8),ISBLANK($A$8)),””,$A8)
When this cell is selected, and the Add to Formula utility is run, the form automatically retrieves the source cell (A8) that the formula is referring to. If there are multiple source cells, the utility picks the first one. The user can then set what value this cell should equal (or any other comparator function can be typed here, including a more complicated formula involving multiple source cells for evaluation) for the target cell to be set to the new user defined value (typed in the “Value or Formula” field). Likewise, the user is free to enter a single numeric value or even another formula if they so wish.
In our example, we want the formula in A20 to give the value 1 x 10-10 if A8 equals 0 otherwise we want the original formula to be active for all other values.
Therefore, the condition is A8=0
And the Value or Formula is equal to 1e-10 (this is the way scientific notation is entered in Excel). Finally, we want this value to be set if the condition is True, so we leave the “Value or Formula if condition is” option box set to True as shown in Figure 1.
After pushing the OK button, A20’s formula will now be:
=IF(A8=0,0.0000000001,IF(OR(ISERROR($A$8),ISBLANK($A$8)),””,$A$8))
The text in blue is the new If statement with the condition and value that has been added to the formula by the Add to Formula utility. In summary, this little utility helps simplify the building of more complex Excel IF formulas and can be used sequentially to create difficult to construct formulas which can be confusing to write in a single attempt inside an empty cell.
If the user enters a formula that does not evaluate correctly then the following error message will be displayed.
Likewise, if the user has multiple cells selected and they try to run this utility then the following error message will be displayed.
