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?
RothbardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:

In excel -2007 go to data-->subtotal and apply subtotal over it which will do what you are looking for...
0
nutschCommented:
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

0
RothbardAuthor Commented:
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.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

RothbardAuthor Commented:
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.
0
Saurabh Singh TeotiaCommented:
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..
0
RothbardAuthor Commented:
Should I do the "Group by" before or after the Subtotal? Could you give me a step by step guide?
0
RothbardAuthor Commented:
Let's say the two columns are called "Date" and "Price"
0
nutschCommented:
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
0
nutschCommented:
Shortcut is Alt+;
I had to remove the pivot before being able to do the subtotal as the two are incompatible.

Thomas
nutsch-440476.flv
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RothbardAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.