Excel 2010 graph question

I have a very simple spreadsheet plotting the minimum overnight temperature in degrees F and degrees C.

I enter the degrees F reading each morning and the degrees C column appears automatically. I have a graph generated from the date and the two temperature columns. There are generally a week or two of spare rows awaiting the figures.

By setting the Hidden and Empty Cell Settings to Show empty cells as Gaps any forward dates are not graphed and terminates at the latest date - for the degrees F line at least. However the degrees C line, for future dates drops to the zero axis and continues along that axis to the end.

The formula I use in the C cells is

=IF(B45="","",(B45-32)*5/9)

where B45 is the corresponding Fahrenheit cell.

It would seem that having a formula there, even if it results in a null entry, doesn't count as an empty cell for the purposes of stopping the graph line dropping to zero.

Any thoughts on how to achieve this? I could remove the formulas from the forward Celsius cells but would prefer not to.

In my version (2007) there's an option when you 'select data' for the chart:

"Hidden & Empty Cells" - Show empty cells as:

- Gaps
- Zero
- Connect data points with line

Option 3.

Linky

Same option is in Excel 2010.

Whether I choose the Show empty cells as Gaps or Connect data points with line makes no difference in this case. The later option is, I think, for when some data points in the middle of a sequence are missing and the points either side of the missing data are connected rather than a gap left in the graph line, it's not for when the data points have ended.

Whichever of these two options I choose the C line for all future dates descends to the zero axis for the first future date (tomorrow), then continues along that axis for the remaining future dates.

The F line stops at the last entered value, and is non-existent for all future dates.

If I remove the formula from all future dates then the C line is drawn the same as the F line. I think this is due to the difference between a cell being completely empty, and one with a formula returning "". They both display as blank cells but the latter isn't an empty cell.

Can you explicitly set it to blank?
=IF(B45="","",(B45-32)*5/9,"")

I suggest you delete any blank cells to ensure they are empty/blank. I have seen occasions where Excel treats a cell as zero as opposed to blank (or the other way round).

In reply to a post by camieabz:

I suggest you delete any blank cells to ensure they are empty/blank.

From the OP

In reply to a post by cheshire_man:

I could remove the formulas from the forward Celsius cells but would prefer not to.

I think the answer to my original question is, quite simply, no. Excel treats a non-blank cell as a non-blank cell. And an empty cell, for graphing purposes, must be truly empty, not just one displaying nothing.

Ah well... thanks for all your thoughts.

Edited by cheshire_man (Fri 21-Dec-12 07:43:43)

This site has a solution - instead of blanking the cell use the NA() formula. For line graphs this is treated as a blank...

Indeed.

I saw a similar link yesterday, but the suggestion was to return an "N/A" result, which didn't work. =NA() does work though.

So the formula should be
=IF(B45="",NA(),(B45-32)*5/9)