Graphing 'blank' cells in Excel

[kixkat824]

I'm having some problems with graphing 'blank' cells for my thesis work.

I am filling in data still as I go, and I'm trying to set up my graphs as well so I can easily go in later and modify, fill in more data, or whatever. But all the cells I've formatted as coming back blank are labelled as zeros on the Excel graph. I tried putting in to return #N/A but then Excel is still graphing the cells as zeros. Frustrating. I really do not want to have to recopy over 1000 data points, find the zeros, and cut them out later, I'll have enough to do writing the dang thing. Does anyone know how to modify the formula to have it correctly skip the points when graphing (it's a line graph)?

This is the current formula I'm using. =IFERROR(AVERAGE(C41:Y41), "")
And I put in =IFERROR(AVERAGE(C41:Y41), "#N/A"), which shows the cell as #N/A (which, I'd much rather it be blank, as then I can read the data easier), but it is still graphing as 0.

Tips?

 

[gsistek]

I've run into this same issue. The way I resolved it was force an error:
=IFERROR(AVERAGE(C41:Y41),ERROR.TYPE(3))
After that I use conditional formatting to change the font to white for all cells returning an error.

 

[kixkat824]

Ahh, that worked out. Thank you!!
My only question then is how do I find the text conditional formatting? I can find cell color...but no text. I have Office 2007.

Also, is there a way to get an average of a range of cells that only averages the actual numbers, when the range includes those error cells?

 

[AhoyNC]

Also, is there a way to get an average of a range of cells that only averages the actual numbers, when the range includes those error cells?[/QUOTE]

Try

Excel Workbook
	A	B	C
1	45
2	#N/A
3	34		Average
4	23		34
Sheet

Also if you change
=IFERROR(AVERAGE(C41:Y41), "#N/A") to
=IFERROR(AVERAGE(C41:Y41), NA())
That should stop it from graphing 0, but you will still have #N/A in your data.

 

[kixkat824]

Thank you all so much! Everything is working much better now.