[CHART] Area chart with #N/A data

[Christian_Ku]

Hi there,

Currently I'm working with an amount of data that corresponds with one data point every day in one year. Not every day is measured thus nothing is entered, some days can also be 0. Therefore I've made an if statement in the column next to the raw data column with an if statement returning #N/A if a cell contains nothing or 0.
This gives me normally no problems when I want to make a Line chart, excel just skips the #N/A cells and draws the line to the next data point (that's the whole reason for the if statement). However this is impossible with area charts. I can understand why, but I still want it as nothing or 0 in a cell not necessarily mean that nothing happened.

Is this possible?

 

[Jon Peltier]

Area charts treat #N/A and blank cells differently than line charts, as you've discovered.

Instead of the macro writing #N/A into the cells, how about a formula that averages the values on either side. If the values are in column A, and cell A4 is missing a value, put this formula in A4: =(A3+A5)/2. The R1C1 formula is even easier, since it doesn't change with the row number: =(R[-1]C+R[1]C)/2.

 

[Christian_Ku]

Hmm, that could work were it not for the fact that it is scientific data and that formula erases most peaks and drops of the curve. I was afraid that this might not be possible, now it seems so.

 

[Jon Peltier]

Yeah, scientific data is so much different than business data. Though I wouldn't know, since I'm a scientist, not a businessman.

How do the formulas erase peaks and drops? Maybe a sample of data would help us understand what you haven't explained.

Keep the original data intact, put formulas into the next column, like =IF(RC[-1]=0,(R[-1]C[-1]+R[1]C[-1])/2,RC[-1])

How about using an XY or Line chart for your display?

 

[Christian_Ku]

Ha, you're right there, data is data.

Anyway, the formula erases peaks and drops because f.e. if I have this array:

data avg data
5000
4000 5500
8000 6000
4000 6000
6000 5000
6000 6000
5000 5500

(sorry for formatting)

removing the peak of 8000 entirely, I must admit I tested it this morning quickly and then moved onto another ongoing project. Will put some effort in it this afternoon.

The reason I want to use an area is that this data is theoretical and should resemble the actual results, that is difficult with multiple lines and many datapoints.

 

[Jon Peltier]

I don't understand your smoothing. What's the original data like, and where are the blanks/zeros?

 

[Christian_Ku]

Blanks and zeros are usually in the weekends, thus 5 data points, than 2 #N/A and 5 data points again, etc. But, sometimes someone forgets to measure, or there is a bank holiday or I don't know what for other reasons there can be. Anyway, blanks and zeros are somewhat constant (weekends) with variations in the week.

 

[Jon Peltier]

The reason I want to use an area is that this data is theoretical and should resemble the actual results, that is difficult with multiple lines and many datapoints.

I don't know what you mean. Sometimes an area chart will be used to show a benchmark or target and a line will show actuals. But if the chart is too cluttered, there are other problems.

 

[Jon Peltier]

Blanks and zeros are usually in the weekends, thus 5 data points, than 2 #N/A and 5 data points again, etc. But, sometimes someone forgets to measure, or there is a bank holiday or I don't know what for other reasons there can be. Anyway, blanks and zeros are somewhat constant (weekends) with variations in the week.

This is going to be complicated to try to fix with formulas. Instead just keep the dates for which you have values, and don't keep a date next to a blank cell (missing observation). When you chart this, the area chart draws lines between actual points, and there are no points with zeros or blanks.

 

[Christian_Ku]

That's a good one, solve it through the X-values. Will try that, hadn't come up with that myself me thinks....