Excel graphing question

Factual Questions
1

There’s probably some ridiculously easy answer to this that I can’t find…

I want to do a line chart of monthly attendance to various classes we offer, Jan - Dec. In May, we have numbers entered for Jan throuh April, and the line runs through those four numbers and stops, which is exactly what I want.

But there is another line I want to chart, which is the sum of those monthly numbers. If the raw numbers for May - Dec haven’t been added, the sum cells displays a ‘0’. The chart displays the real numbers for through April, then the line drops down to the ‘0’ value and charts as a zero through Dec.

If i modify the formula to an if statement, eg. =IF(SUM(a1:a10)=0,"",SUM(a1:a10)), then the cell displays nothing until the raw numbers have been entered, but the chart still charts the values as zeroes.

The only way I have found to avoid this is to chart only the numbers that have values entered, and stretch the “source data” box every month to include the next month’s entries. This is not acceptable to the person entering the data, as she is simply unwilling to learn how to modify the charted data. She wants to enter her data into the boxes, and have the charts update automatically.

So can I trick the charts to handle the data the same way? I want to chart a blank cell exactly the same as one with a value of “”. Or I want to modify the formula so a zero sum result returns some other value that the chart treats the same way as it would a blank cell.

2

Use the NA() function instead of “” in your IF statement.

3

Thank you. That works for the chart.

Now #N/A displays in the cell. Is there any way to accomplish both – make the cell display blank, and chart no value for the cell? FYI, I tried another modification of the formula – using ISNA() with a nested “if” statement, so if a value of ‘#N/A’ was returned the cell would display “”. But while the formula worked, I was back to the original problem – the chart reverted to charting it as a value of zero.

4

You can set up another column that is hidden - this one will have values for the chart, and the original will display.

5

When I hide the row, the line disappears from the chart, and reappears when I unhide.

6

Found the solution to that one myself – while you have the chart selected, click on Tools–Options-- and uncheck the bok that says to plot visible cells only.

Thanks so much for your help.

You are the master. I am the student.

7

In the future, if you have weird charting problems you can mirror the information you want in another sheet for the purposes of charting. This is also helpful when the information is not contiguous in it’s normal form. You can then hide the sheet so nobody tinkers with it accidentally.

8

Well, I did not expect it to behave that way, how weird, why would Bill Gates do that?

I learned something new today too!

9

OTOH, why would a cell with a formula resulting in a value of “”, chart as a value of zero when a blank cell doesn’t chart at all?

Pleased to pay a little back! :slight_smile: