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

[Mr. Snrub]

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:

=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:

=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?

 

[Fluff]

If your formula always returns numbers, another option would be

=IFERROR(1/(1/[incredibly long formula]),na())

 

[RoryA]

What is the correct approach?

Depends on your definition of 'correct'.

I'd prefer option 2, personally.

 

[Mr. Snrub]

If your formula always returns numbers, another option would be

=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]

Glad we could help & thanks for the feedback.