Excel 2003 average data in charts

[RonCz]

I would like to record amounts in Excel 2003 in one column along with the date of the amount. I then want to view a chart of the amounts for the month, quarter, or year. However I will have missing amounts for some of the days. I want excel to calculate an average for the missing amounts. Here is an example of data.

Date Amount
3/1/2016 10.00
3/2/2016 20.00
3/3/2016 0.00
3/4/2016 8.00
3/5/2016 6.00
3/6/2016 0.00
3/7/2016 0.00
3/8/2016 18.00
3/9/2016 0.00
3/10/2016 15.00
3/11/2016 0.00
3/12/2016 19.00

Maybe I would not care to make an entry if there is no amount that day but still want to see it averaged between the two given amounts.

 

[RonCz]

Maybe I should be using Access, log in Excel and transfer to Access.

 

[ignatzatsonic]

Not sure I'm following you.

Excel will include the zero amounts as part of an average. The average of 5, 4, and 0 will be shown as 3.

If you instead make no entry for the zero month and leave it blank, the average of the same 3 cells will be calculated as 4.5 (9 divided by 2 rather than by 3). The empty cell would be ignored.

I suspect you already know that, so I'm not clear on your question.

As written in your post, the sum of those 12 cells is 96 and the average is 8.

If you instead leave the 0.00 months empty, the sum is still 96, but the average is 13.71 (96 divided by 7) because the 5 empty cells are completely ignored.

Given that, what's the question or problem?

 

[RonCz]

I think this graph of the values above should clear up what I am trying to do.
3-3-16 is plotting zero but I want it to plot 14.

It answers the question how much water did I drink on March 3rd if I only plotted from March 2 to March 4th when I wrote done the amounts.

If on the 2nd I poured 20oz of water in the pitcher and on the 4th I added 8 more oz, then on the 3rd I would have drank 14oz.

I am trying to get a chart of the average water I drank during the week, month, quarter.

 

[RonCz]

Or should I be using Access? log the information in Excel and transfer to Access.

 

[ignatzatsonic]

The blue line in your graph accurately represents the values you list in your first post.

I don't know why you think "3-2-16 should be 14". The value in your list for that date is 20. The blue line shows that.

I don't know why you think "3/6th and 3/7th should be 10 and 14 plotted points". The values you list for those dates are zero and the blue line shows that.

But I'm something of a dim bulb, despite English being my first language.

Maybe someone else understands you.

Good luck.

 

[RonCz]

Thanks for trying

Yes you are right! if I put zero for that day it should show as zero on the graph. Let's just say I just did not put anymore water in the pitcher that day however I did drink some of that water.

Just because I did not plot a value for 3-3-16 I still drunk water on that day. Without writing a value I want the graph to show a value.