A recent project required editing many formulas to insert an IF function to display the NA error in certain circumstances. Here’s how I did it.

If you are wondering why I wanted to display the NA error, see a previous post on using NA() with charts – link below.

Adding a Vertical Dotted Line to an Excel Line Chart

In the image below cell B1 is named CurrMth.

I needed to convert the formula in cell B6 from.

=B4/B5

To.

=IF(CurrMth>=B3,B4/B5,NA())

This requires two changes.

1. I need to insert  IF(CurrMth>=B3, at the front of the formula.
2. I need to add ,NA()) to the end of the formula.

Because I need to make this change to a number of existing formulas I used the Office clipboard to capture the two parts of the formula to make it easy to amend the other formulas.

After amending the first formula, in the Formula Bar I selected and copied the part of the formula shown below.

=IF(CurrMth>=B3,

I then selected and copied.

,NA())

These two parts are captured separately in the Office Clipboard.

You can see the Clipboard by clicking the small arrow in the bottom right-hand corner of the Clipboard section (far-left) of the ribbon – see image below.

The Clipboard after the two copies is shown below.

With these two options visible it is easy to amend all the existing formulas.

Simply edit an existing formula and select the = sign and then click the first part of the formula in the Clipboard.

Then click at the end of the formula and in the Clipboard click the end part.

Job done.