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

Mr. Snrub

Board Regular
Joined
May 22, 2005
Messages
129
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?
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
51,062
Office Version
  1. 365
Platform
  1. Windows
If your formula always returns numbers, another option would be
Excel Formula:
=IFERROR(1/(1/[incredibly long formula]),na())
 
Solution

Mr. Snrub

Board Regular
Joined
May 22, 2005
Messages
129
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
Joined
Jun 12, 2014
Messages
51,062
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,118
Messages
5,576,202
Members
412,706
Latest member
msousa25
Top