Graphing a daily average

[scootamcgruta]

So I'm trying to set up a chart that looks at what the daily average is for a number of entries.

My data looks something like this:

Please Login or Register  to view this content.

Ideally what I want is to have the date along the x-axis and then one data point for that day, showing the average value of the day, probably in a column graph.

I know I could go and calculate each day by hand and then put that value into the graph, but I'd like to know if there is a formula or a feature I'm overlooking that might be able to do this for me.

Thanks for your help!

[Richard Buttrey]

Hi,

First you need to get your data into two columns A & B. A date column and value column. If the example data is in a single column then use Data Text to Columns to Parse it. Then make sure that the date column contains proper Excel date numbers.

Now use Data Filter Advanced Unique to extract a unique list of dates in another column, say column D.

Now in E1 enter with Ctrl-Shift-Enter

Please Login or Register  to view this content.

Adjust the row ref 100 to suit your data and then copy this down as necessary.

Regards

[scootamcgruta]

Ok, the data is already in two different columns. I apologize for not making that more clear in my OP.

One other point I should make clear is that row 1 is used as a heading row and is frozen in this sheet.

I was able to extract the unique dates, but I'm having trouble making the formula work. Maybe because of the frozen row? I tried altering the data in the formula to try to make it work, but that didn't do anything either.

I should also point out that I'm looking for a solution that doesn't require a very intensive process each time new data is added to the sheet. Data gets added every day, so repeating the process of filtering out unique dates each day and redoing the formula and the chart could prove to be a bit cumbersome.

Perhaps the solution I'm looking for isn't possible? I really have no idea... lol

[Andy Pope]

Perfect for a pivot table and chart