PDA

View Full Version : Microsoft excel - question

V.Melb
May 3rd, 2005, 04:17 AM
If i have a list of info as the following.
Apr-82113.2May-82115.3Jun-82108.2Jul-82114Aug-82117.7Sep-82120.7Oct-82124.6Nov-82153.9Dec-82306.5Jan-83131.5Feb-83122.3Mar-83142Apr-83118.4May-83130.4Jun-83119.4Jul-83121.3Aug-83131.4Sep-83139Oct-83155.3Nov-83183.6Dec-83345.8

What is the proceedure on excel to make the data 'quarterly'?
Is there ahy other way to do it other then take moving averages? ie. add up3 months and divide.

Thanks heaps for anyone who can help!

V.Melb
May 3rd, 2005, 01:08 PM
hahaha. I can see people take a look at this thread and think BORING! - I don't come onto the message board to answer geeky questions - i come for pleasure.

But i am resiliant, if anyone knows the answer please tell me!

Bright Red
May 3rd, 2005, 01:34 PM
If i have a list of info as the following.
Apr-82113.2May-82115.3Jun-82108.2Jul-82114Aug-82117.7Sep-82120.7Oct-82124.6Nov-82153.9Dec-82306.5Jan-83131.5Feb-83122.3Mar-83142Apr-83118.4May-83130.4Jun-83119.4Jul-83121.3Aug-83131.4Sep-83139Oct-83155.3Nov-83183.6Dec-83345.8

What is the proceedure on excel to make the data 'quarterly'?
Is there ahy other way to do it other then take moving averages? ie. add up3 months and divide.

Thanks heaps for anyone who can help!

There's another way. But first, I have to ask why are you taking averages of quarterly data? Quarterly data is usually not averaged--it's usually the sum of all three months of the quarter. You should probably label your "quarterly" data as "monthly average for the quarter" to avoid confusing those who receive the data.

The easiest way is the one you describe above (without taking average :) ). But to sum up three months of data automatically, you should put your data in columnar form, e.g:

Month Amount
Jan 82134
Feb 76122
etc...

Then you should relabel the name of the months with the name of the quarter. For example, in the month column above, you'd substitute each of the words Jan, Feb, and Mar with "Q1". Then you'd substitute Apr thru Jun with Q2, and so on. Then, you can use the formula "SUMIF". Here's how it works:

Let's assume the word "Month" above is in cell A1, and the word "Amount" is in B1.

=SUMIF(A2:A13,"Q1",B3:B13)

Excel will sum up only the amounts that have the label Q1 in column A. Then you need only change Q1 to Q2 inside the sumif formula to have it sum up Q2 for you. If you want the average, then of course would divide the expression by 3 as follows:

=SUMIF(A2:A13,"Q1",B3:B13)/3

There's also a way to do it with Pivot Tables. It's a bit complicated though.

Hope this helps.