Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


Pages in this thread: 1 | 2 | (show all)   Print Thread
Standard User cheshire_man
(knowledge is power) Thu 20-Dec-12 11:55:55
Print Post

Excel 2010 graph question


[link to this post]
 
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.

Tony
Standard User camieabz
(sensei) Thu 20-Dec-12 14:07:05
Print Post

Re: Excel 2010 graph question


[re: cheshire_man] [link to this post]
 
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.

~ Camieabz ~

All Connection Data ~ Some plusnet links

mod'er·a'tion n.
Synonyms: temperance, restraint, modesty.
Standard User camieabz
(sensei) Thu 20-Dec-12 14:12:30
Print Post

Re: Excel 2010 graph question


[re: cheshire_man] [link to this post]
 
Linky

~ Camieabz ~

All Connection Data ~ Some plusnet links

mod'er·a'tion n.
Synonyms: temperance, restraint, modesty.


Register (or login) on our website and you will not see this ad.

Standard User cheshire_man
(knowledge is power) Thu 20-Dec-12 22:38:45
Print Post

Re: Excel 2010 graph question


[re: camieabz] [link to this post]
 
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.

Tony
Standard User BatBoy
(legend) Thu 20-Dec-12 22:53:53
Print Post

Re: Excel 2010 graph question


[re: cheshire_man] [link to this post]
 
Can you explicitly set it to blank?
=IF(B45="","",(B45-32)*5/9,"")


_____________________________________________________________________________________________ this is not usenet __________________
Standard User camieabz
(sensei) Thu 20-Dec-12 23:08:17
Print Post

Re: Excel 2010 graph question


[re: cheshire_man] [link to this post]
 
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).

~ Camieabz ~

All Connection Data ~ Some plusnet links

mod'er·a'tion n.
Synonyms: temperance, restraint, modesty.
Standard User cheshire_man
(knowledge is power) Fri 21-Dec-12 07:43:11
Print Post

Re: Excel 2010 graph question


[re: camieabz] [link to this post]
 
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.

Tony

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

Standard User ian72
(knowledge is power) Fri 21-Dec-12 10:29:11
Print Post

Re: Excel 2010 graph question


[re: cheshire_man] [link to this post]
 
This site has a solution - instead of blanking the cell use the NA() formula. For line graphs this is treated as a blank...
Standard User camieabz
(sensei) Fri 21-Dec-12 11:17:20
Print Post

Re: Excel 2010 graph question


[re: ian72] [link to this post]
 
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.

~ Camieabz ~

All Connection Data ~ Some plusnet links

mod'er·a'tion n.
Synonyms: temperance, restraint, modesty.
Standard User BatBoy
(legend) Fri 21-Dec-12 11:19:44
Print Post

Re: Excel 2010 graph question


[re: ian72] [link to this post]
 
So the formula should be
=IF(B45="",NA(),(B45-32)*5/9)


_____________________________________________________________________________________________ this is not usenet __________________
Pages in this thread: 1 | 2 | (show all)   Print Thread

Jump to