area chart won't ignore empty cells, data descends to zero
obe one
I have an area chart of costs per month, with data to september, and a time
scale to december. The chart, instead of just stopping at september, drops
down to zero at october.
I have tried, unsucessfully, the following
-options > chart > empty cells not plotted
-stopping data ranges at september, but then the x axis also stops at
september
-the same as previous, using a mixed chart type (with a line chart) to have
the line extend to end of time series, formatted white color to be
invisible, but xl did not like mixing line and area chart.
THis isn't rocket science, is the area chart buggy for the empty cell option
and is this fixed in xl2000?
I have tried this at work and at home and same result.
thanks
doug
Richard J
empty, or do they have a formula such as
=IF(<no data>,"",<result-expression>)
In such a case, Excel does not regard the cell as empty, even if its
value is just "". The remedy is to change the formula to
=IF(<no data>,NA(),<result-expression>)
Richard J.
"obe one" <ev...@evildude.org> wrote in message
news:uGByfvR...@cppssbbsa02.microsoft.com...
obe one
question. Sadly, however it does not work...
If you mean, put =NA() into blank cells, I do this and it has no effect at
all, I read other postings and tried this before my first message (sorry
forgot to mention it).
I urge you to validate any proposed solution with the exact conditions
discussed in the please-help-me post first before offering it up as a
solution (I sound ungrateful, but I am just deeply frustrated - you should
see me talk about the Excel's incredlbly useless dsum functions which need
criteria tables - the much superior lotus 1-2-3 fixed this around 1991 with
putting criteria in the formula itself - I know, use Tools>Data Tables, but
this is rocket science to anyone less than super power user) I am grateful
for the response Richard J, I really am!
The NA trick & ignore-empty-cells does work fine with, for instance, line
chart. But it is an AREA chart that I want to use, and it seems to not work,
thanks Microsoft Quality Control (or lack there of)! Note I have tried this
exactly as Richard J proposes with an if statement.
I would appreciate if someone could tell me if they get the same result as
me with an area chart, and if they have any solution other than the
much-profferred but ineffective =na() non-solution.
SIncerely thanking all who give this more than 3 seconds thought and can
come up with a solution beyond the standard solution that SHOULD work but
doesn't. The area chart is one of the best there is at making data visible
in graphic form, and I want to use it, I really really do, and my boss
thinks I am useless for hitting this roadblock. There goes my raise! Aieee!
doug
Richard J wrote in message ...
Richard J
Sorry, I should know better than to assume that Excel works the same way
for Area charts as for lines.
Yes, you're right. Whatever you set the "plot empty cells" option to,
you get the area sloping down to zero for the first date without data.
However, I've found a way round this.
The answer is to force Excel to use 1 hour as its time interval for the
x-axis instead of 1 month. If your last data is for September, you just
add an extra cell in the row or column in your worksheet that lists the
months, and type in this date/time (US date notation):
9/1/2000 01:00
i.e. 1 a.m. on 1st September 2000, with no corresponding value. Excel
will force this non-value to zero on the chart, but since it's only 1
hour after your September month value (which Excel regards as 1st
September at 00:00), the drop to zero will appear vertical.
If this doesn't work for you,
(a) make sure that your month values are configured in the cells as
dates,
(b) with the chart selected, do Chart / Chart Options / Axes and select
Time-scale for the Primary X-axis. (Automatic should work too, but not
Category).
When you add a new month's data, just change the date in the extra cell
to 01:00 on the 1st of that month. There's no need to move the cell.
HTH
Richard J.
Reading, England.
"obe one" <ev...@evildude.org> wrote in message
news:uXNLYvZQAHA.75@cppssbbsa05...
> Dear Richard J. And all who read my post. Thank you for responding to
> my question. Sadly, however it does not work...
<snip>
>
> doug
>
>
> Richard J wrote in message ...
>> What's in the "empty" cells that get plotted as zero? Are they
>> really empty, or do they have a formula such as
>> =IF(<no data>,"",<result-expression>)
>>
>> In such a case, Excel does not regard the cell as empty, even if its
>> value is just "". The remedy is to change the formula to
>> =IF(<no data>,NA(),<result-expression>)
>>
>> Richard J.
>>
>>
>> "obe one" <ev...@evildude.org> wrote in message
>> news:uGByfvR...@cppssbbsa02.microsoft.com...
>>>
>>> I have an area chart of costs per month, with data to september, and
>>> a time scale to december. The chart, instead of just stopping at
>>> september, drops down to zero at october.
<snip>
>>> doug
Tushar Mehta
email copy to obe one.
Please use the newsgroup for further discussion.]
If I understand your requirement, you should use a Named reference for
charting. Look at funchrt1.zip on Stephen Bullen's web site
(http://www.bmsltd.co.uk/Excel/Default.htm)
--
Regards,
Tushar Mehta
www.tushar-mehta.com
--
In <uXNLYvZQAHA.75@cppssbbsa05>, obe one <ev...@evildude.org> wrote
obe one
I added an extra time period between my latest actual period (i.e with data)
and the next period (the should-be "blank/empty/=na()" period) and in that
new row of data added a x-axis date one hour after the actual data's period,
this made the chart atill descend to zero but in a hair's width and looks
fine.
Thanks
I will try the other solution as time permits.
Doug
Richard J
"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message
news:MPG.146636a7b...@msnews.microsoft.com...
> [This followup was posted to microsoft.public.excel.charting with an
> email copy to obe one.
> Please use the newsgroup for further discussion.]
>
> If I understand your requirement, you should use a Named reference for
> charting. Look at funchrt1.zip on Stephen Bullen's web site
> (http://www.bmsltd.co.uk/Excel/Default.htm)
All that funchrt1.zip does, AIUI, is to extend the axis automatically as
new data is received. But Doug said he wants the x-axis to run to
December anyway. His problem is how to avoid the month after the latest
data being shown as a zero value. (See my response of a few hours ago.)
Richard J.
obe one
Here are all the steps to have your area chart stop at the last period
containing data
(To solve bug of the chart graphics all descending to zero at the next
period's tick mark instead of ignoring blank data cells))
- the Month labels for the x-axis in the spreadsheet are real dates
(12/1/2000) and not "jan, feb, etc" typed in cells
- add an extra row of data (an extra time period) between my latest actual
period (September) and the next period (October).
-In the x-axis data for this new row the value is one hour plus value of the
previous month cell.
- select the x axis on the chart
-Right Click Mouse>Format Axis>Number is set to some flavor of months
(of some sort, unfortunately three letter month (jan, feb, etc) is not a
choice)
- Right Click Mouse>Format Axis>Axis can probably be fooled around with
but I use defaults
-Select entire chart
- Tools>options>chart>Plot empty cells as> makes no difference, that's
why I have to bend over backwards to make excel area chart work like other
forms of charts.
And now the area chart is fooled, heeheehee, into stopping at the edge of
the actual data instead of dropping down to zero at the next month's tick
mark, all-the-while the x-axis continues to the end of the 12 months of
reporting periods.
I also learned to use functions for named ranges which is very cool and will
wow then back on the farm.
Thanks to both of you for caring, and for your help!
doug
montreal (snowed today first time this fall)
Thanks
The format Axis>scale
- All blank data cells contain =na() so there is no value there
- Chart>chart options>axes>category (x) axis>select Time-scale
(Important!)
and now it should work
Thanks
Richard J wrote in message ...
"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message
Tushar Mehta
Here's what I think is a much simpler way. It's tested with XL2000 and
it needs no ongoing maintenance when you want to add new data.
Start with the foll. data:
01-Jan 0 1.555654658
01-Feb 0 1.5070469
01-Mar 0 2.130255031
01-Apr 0 4.657313017
01-May 0 4.893795676
01-Jun 0 5.389985523
01-Jul 0 6.470475103
01-Aug 0 7.95863926
01-Sep 0 8.063041747
01-Oct 0
01-Nov 0
01-Dec 0
where the zeros in column B are dummy data I added and the next column
(C) contains the real data. In my case, I created a bunch of random
numbers. Create a name, say RealData, and set it to the formula
=OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C),1)
Select the first 2 columns and create a line chart. That's right, a
line chart. Now select the chart, then the menu item Chart | Source
Data... | Series tab. Add a new series and set the values to
Book1!RealData (where Book1 should be the name of your saved workbook).
Select the new series and then the menu item Chart | Chart Type... and
set the type to Area.
Now, the area chart stops exactly where the data end, but the x-axis
goes all the way to the end, which in this case is Dec 2000.
I will leave the rest of the formatting to your taste.
If you want, I can send you the example workbook. This offer is good
for 2 days, after which I will delete the workbook at my discretion.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
--
In <#qPRrQgQAHA.243@cppssbbsa04>, obe one <ev...@evildude.org> wrote
Richard J
The trick is to create the line first. I had tried adding a line to the
area chart, and that doesn't work.
Richard J.
"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message
news:MPG.1466a4114...@msnews.microsoft.com...
daniel...@gmail.com
> I have an area chart of costs per month, with data to september, and a time
> scale to december. The chart, instead of just stopping at september, drops
> down to zero at october.
>
> I have tried, unsucessfully, the following
> -options > chart > empty cells not plotted
> -stopping data ranges at september, but then the x axis also stops at
> september
> -the same as previous, using a mixed chart type (with a line chart) to have
> the line extend to end of time series, formatted white color to be
> invisible, but xl did not like mixing line and area chart.
>