Average one column w.r.t. another
I have two columns: column A with dates and column B with numbers. There are several records for each date, e.g.
01/01/2011 90.0
01/01/2011 95.0
01/01/2011 97.0
01/01/2011 91.0
02/01/2011 92.0
02/01/2011 95.0
02/01/2011 95.0
I would like to "collapse" it so that each day in column A only appears once, and column B shows the average value for each day. How can I do it?
01/01/2011 90.0
01/01/2011 95.0
01/01/2011 97.0
01/01/2011 91.0
02/01/2011 92.0
02/01/2011 95.0
02/01/2011 95.0
I would like to "collapse" it so that each day in column A only appears once, and column B shows the average value for each day. How can I do it?
Use the subtotal functionality in the data ribbon.
Make sure you have titles (date, amount)
Select your data
Filter on the date columns
Data \Subtotals
Choose group on date, add average to number
Poof, you're done
THomas
Make sure you have titles (date, amount)
Select your data
Filter on the date columns
Data \Subtotals
Choose group on date, add average to number
Poof, you're done
THomas
ASKER
I'm not sure what you mean by "Filter on the date columns". How do I do that? Also, within Subtotal I don't get any options for "group by" and "add average to number".
When I try it, after each sequence of dates I get a date in bold with the average next to it, but that's not what I want - I'd like a separate list with dates and averages. Thanks.
When I try it, after each sequence of dates I get a date in bold with the average next to it, but that's not what I want - I'd like a separate list with dates and averages. Thanks.
ASKER
What I mean is: when I try Subtotal, after each sequence of dates for the same day, I get an entry with a single date in bold followed by an average. I'd like instead a separate list with dates and averages.
Say Group by column-A assuming you have dates in Column-A and average by Column-B assuming you have Values in Column B.
Also if you want a seprate list then go to insert-->pivot table and in row show your dates and in data field show your values and change the same to average which will give you only 1 entry for it which is seprate then what you have..
Also if you want a seprate list then go to insert-->pivot table and in row show your dates and in data field show your values and change the same to average which will give you only 1 entry for it which is seprate then what you have..
ASKER
Should I do the "Group by" before or after the Subtotal? Could you give me a step by step guide?
ASKER
Let's say the two columns are called "Date" and "Price"
Here are the subtotals and the pivot solution. I used the ctrl+; shortcut to select only visible lines in the subtotal.
Check out the screencast.
Thomas
Check out the screencast.
Thomas
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
In excel -2007 go to data-->subtotal and apply subtotal over it which will do what you are looking for...