Data Ranges for Charts
Posted by Scott Holmes on September 24, 2001 7:31 PM
I have a spreadsheet used to input student enroll data. I sum the monthly entries to produce a running total over an entire year. Here is an example:
Month Enrolls Cumulative Enrolls (formula)
1 20 20
2 30 50
3 10 60
4
5
...
The cumulative field has a formula applied to all 12 datapoints to add the current month total to last months cumulative total.
My problem: I want to create a chart using 12 data points for the entire year so the user does not have to expand the series data range every month. How do I prevent the chart from showing data points for months that have not had an actual value entered??
Posted by anno on September 24, 2001 9:50 PM
scott
i think this is what you need. using your example above, the first 'month enrolls' number(10) is in A2. in B2 put =A2 and in B3 enter =IF(A3,(B2+A3),"") and copy down as far as required.
Posted by anno on September 24, 2001 10:05 PM
the fact that you'll need to put some character in the rest of the cells in column A before you generate the chart, otherwise it will only show as many data points as you have entries in column A. you can just enter any character(i used a comma) to fill the range A1:A12, build the chart and then delete the commas. the chart will fill as you enter further numbers in column A.
Posted by Scott Holmes on September 25, 2001 4:56 AM
Thanks for your input. I had previously tried a solution similar to the one you presented above. However, the "" adds a space to the cell. How can I set the IF statement to leave the cell totally blank for the not true segment??
Posted by anno on September 25, 2001 4:30 PM
hi scott
i'd thought from your original question that you wanted a chart with 12 points on the 'cumulative enrolled' axis which would be populated as data was entered.
or was it the case that you only wanted the chart to have as many points on the axis as there is data actually entered - using your original example only 3 numbers - 20,50 and 60?
if it's the first, i don't see why the "" adding a space is a problem. if it's the second, sorry but i don't know how to do it. mark w is a whiz with charts (and everything else in excel!)so he may be able to help.
a third option is that i'm just completely dense and can't understand a simple question. : )
good luck
