# 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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

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

#### 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
564
Replies
3
Views
108
Replies
4
Views
148
Replies
1
Views
190
Replies
21
Views
326