Empty Cells Graph Zero on Chart

**brcox7** · 01-07-2011, 05:29 PM

I have a series of lookup functions that populate a table and it works great. From the populated table, I wish to graph the values on a chart.

The lookup function doesent populate every cell in the table, thus sometimes returns "", which is the problem. The graph reads the blank cell as 0 and plots it on the chart. What can i do to have excell ignore the empty cell?

I have attached the spreadsheet for ease of understanding. Look for the yellow and red highlighted cells!

Thanks!
-brcox

**martindwilson** · 01-07-2011, 07:52 PM

use another column for your data na() does not plot see attached

**brcox7** · 01-09-2011, 02:33 PM

I like that idea, but is there another way to do it without returning #N/A?

It makes the table a little more difficult to understand and reviewers of the spreadsheet may think there is an error.

**teylyn** · 01-09-2011, 04:30 PM

Hello,

you could use conditional formatting in the chart source to format the cells with white font on white background when they contain N/A.

This way the error codes are not visible.

Or you could use a hidden helper column for your chart source.