Microsoft excel - question - TennisForum.com
 
LinkBack Thread Tools
post #1 of 3 (permalink) Old May 3rd, 2005, 04:17 AM Thread Starter
Senior Member
 
Join Date: Jul 2001
Posts: 3,813
                     
Microsoft excel - question

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 is offline  
Sponsored Links
Advertisement
 
post #2 of 3 (permalink) Old May 3rd, 2005, 01:08 PM Thread Starter
Senior Member
 
Join Date: Jul 2001
Posts: 3,813
                     
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!
V.Melb is offline  
post #3 of 3 (permalink) Old May 3rd, 2005, 01:34 PM
Senior Member
 
Join Date: Jan 2002
Location: the sexy side of the colo
Posts: 3,402
                     
Quote:
Originally Posted by V.Melb
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.

Venus and Serena are sitting on top of the WORLD

~Venus and now Serena are the worst thing to ever happen to the crow~

Official POPLOCKER of the Royal Court
Bright Red is offline  
Reply

Quick Reply
Message:
Options

Register Now



In order to be able to post messages on the TennisForum.com forums, you must first register.
Please enter your desired user name, your email address and other required details in the form below.

User Name:
Password
Please enter a password for your user account. Note that passwords are case-sensitive.

Password:


Confirm Password:
Email Address
Please enter a valid email address for yourself.

Email Address:
OR

Log-in









Human Verification

In order to verify that you are a human and not a spam bot, please enter the answer into the following box below based on the instructions contained in the graphic.



Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page



Posting Rules  
You may not post new threads
You may post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

 
For the best viewing experience please update your browser to Google Chrome