Excel 2007 : Error message when 1 series in chart has only #NA

Originally Posted by dip11

excel pops an error when the chart comes into vision

which is... ?

A formula in this worksheet contains one or more invalid references.
Verify that your formulas contain a valid path, workbook, range name, and cell reference.


I seem to have done a workaround where if all 12 cells to the left are N/A, make the 12th cell of this range -0.1. And then made sure that the named range was using set ranges via $.

Not turning it into set ranges resulted in if the first few values were NA the range would go from its actual range to 107480 or so.

Not having the check 12 check to set to -0.1 would result in the error and still create the max value point at the first instance.

Had to use both, which is quite a pain.

Part of this, I had to create a separate logic checker:
{=SUM(IF(ISNA(M2:M13),1))]}

Which I can't seem to combine into a different formula as this one is CSE and the other isn't:
=IF(SUM(ISNA(M2:M13)=12,-0.1,IF(ISNA(M13),NA(),1-M13)))