# How to return the #N/A error if a formula evaluates to zero?

#### Mr. Snrub

##### Board Regular
I have a very long formula that will evaluate to a value that I use in an Excel time series line chart. If the formula's value evaluates to zero, I want the "#NA" error to appear instead. This ensures that the line plot does not get drawn for that specific X-value. Since it is a time series line chart, I really don't want a bunch of unnecessary lines drawn on the X-axis. So I can think of two possible ways to do this:

Option 1:
Excel Formula:
``=IF([incredibly long formula]=0,NA(),[incredibly long formula])``

Option 2:
Keep the formula as-is and then create a second column Column2 with the formula:
Excel Formula:
``=IF([@[Column1]]=0,NA(),[@[Column1]])``
...and then use Column2 as the Y-value series in my line chart.

I assume Option 1 is the worse of the two since it necessitates calculating the incredibly long formula twice for values that are not equal to zero. However, Option 2 has the disadvantage of increasing the size and complexity of the table. What is the correct approach?

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Fluff

##### MrExcel MVP, Moderator
If your formula always returns numbers, another option would be
Excel Formula:
``=IFERROR(1/(1/[incredibly long formula]),na())``

#### RoryA

##### MrExcel MVP, Moderator
What is the correct approach?
Depends on your definition of 'correct'. I'd prefer option 2, personally.

• Domenic

#### Mr. Snrub

##### Board Regular
If your formula always returns numbers, another option would be
Excel Formula:
``=IFERROR(1/(1/[incredibly long formula]),na())``
This worked great. It all stays in one column and I only calculate the incredibly long formula once. Thanks!

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
2
Views
547
Replies
3
Views
99
Replies
4
Views
146
Replies
1
Views
185
Replies
21
Views
317