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:
Option 2:
Keep the formula as-is and then create a second column Column2 with the formula:
...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?
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]])
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?