Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing Quarters
Can anyone devise a formula to summarize monthly data by adding each three
months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing this manually, but there must be a better way. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing Quarters
Hi,
Let's say your information is in sheet2 and you want the Q in sheet 1 so enter the formula as follow =SUM(OFFSET(INDEX(Sheet2!$1:$1,1,(COLUMN()*3)-2),,,,3)) hope this helps "Jeff" wrote: Can anyone devise a formula to summarize monthly data by adding each three months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing this manually, but there must be a better way. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing Quarters
Assuming data is in row 2
=sum(a2:c2), =sum(d2:f2) and =sum(g2:i2) formulas in Column D, Column E, Column F "Jeff" wrote: Can anyone devise a formula to summarize monthly data by adding each three months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing this manually, but there must be a better way. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing Quarters
Thanks, David, but I'm trying to find a way to automate this. Assuming data
for the first year is in A1..L1, I want quarterly totals in A2..D2. "David" wrote: Assuming data is in row 2 =sum(a2:c2), =sum(d2:f2) and =sum(g2:i2) formulas in Column D, Column E, Column F "Jeff" wrote: Can anyone devise a formula to summarize monthly data by adding each three months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing this manually, but there must be a better way. Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing Quarters
It depends on where you want to put the info..
do you want to put the information on another worksheet or on the same worksheet? also do you want to put the info at the end of all of those columns or do you want to do put it at the end...in between add a column between c and d then put a = sign in the column next to the 3 boxes you want to add, then the reference numbers to those boxes example =a1+b1+c1 press enter and the box will show the answer. You can then select the box with your mouse and you will highlight the box (note the box will have a dark black small box in the bottom right hand corner) grab that little box and drag to end of column where info is to be added... it will automatically populate boxes.. let me know if this helps... let me know if you have any questions "Jeff" wrote: Can anyone devise a formula to summarize monthly data by adding each three months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing this manually, but there must be a better way. Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing Quarters
are you wanting a macro?
Sub Macro1() ' ' Macro1 Macro ' ' Range("A2").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-1]C[2])" Range("B2").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[2]:R[-1]C[4])" Range("C2").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[4]:R[-1]C[6])" Range("D2").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[6]:R[-1]C[8])" Range("D3").Select End Sub "Jeff" wrote: Thanks, David, but I'm trying to find a way to automate this. Assuming data for the first year is in A1..L1, I want quarterly totals in A2..D2. "David" wrote: Assuming data is in row 2 =sum(a2:c2), =sum(d2:f2) and =sum(g2:i2) formulas in Column D, Column E, Column F "Jeff" wrote: Can anyone devise a formula to summarize monthly data by adding each three months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing this manually, but there must be a better way. Thanks. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing Quarters
Hi,
in A2 enter =SUM(OFFSET(INDEX(1:1,1,(COLUMN()*3)-2),,,,3)) "Jeff" wrote: Thanks, David, but I'm trying to find a way to automate this. Assuming data for the first year is in A1..L1, I want quarterly totals in A2..D2. "David" wrote: Assuming data is in row 2 =sum(a2:c2), =sum(d2:f2) and =sum(g2:i2) formulas in Column D, Column E, Column F "Jeff" wrote: Can anyone devise a formula to summarize monthly data by adding each three months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing this manually, but there must be a better way. Thanks. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing Quarters
Eduardo - I double-checked that I copied your formula accurately, but the
answer comes back as 0. "Eduardo" wrote: Hi, in A2 enter =SUM(OFFSET(INDEX(1:1,1,(COLUMN()*3)-2),,,,3)) "Jeff" wrote: Thanks, David, but I'm trying to find a way to automate this. Assuming data for the first year is in A1..L1, I want quarterly totals in A2..D2. "David" wrote: Assuming data is in row 2 =sum(a2:c2), =sum(d2:f2) and =sum(g2:i2) formulas in Column D, Column E, Column F "Jeff" wrote: Can anyone devise a formula to summarize monthly data by adding each three months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing this manually, but there must be a better way. Thanks. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing Quarters
Alex - Data is in Row 1, so monthly data for year 1 is in A1..L1. Right now,
I'm typing sum(a1.c1) in A2, sum(d1.f1) in B2, etc. I want to automate this. "Alex" wrote: It depends on where you want to put the info.. do you want to put the information on another worksheet or on the same worksheet? also do you want to put the info at the end of all of those columns or do you want to do put it at the end...in between add a column between c and d then put a = sign in the column next to the 3 boxes you want to add, then the reference numbers to those boxes example =a1+b1+c1 press enter and the box will show the answer. You can then select the box with your mouse and you will highlight the box (note the box will have a dark black small box in the bottom right hand corner) grab that little box and drag to end of column where info is to be added... it will automatically populate boxes.. let me know if this helps... let me know if you have any questions "Jeff" wrote: Can anyone devise a formula to summarize monthly data by adding each three months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing this manually, but there must be a better way. Thanks. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing Quarters
sorry jeff scratch that i see what you want to do... simply you can enter
=a1+b1+c1+d1 in A2 and that should do it automatically.. and then in in b2 put =e1+f1+g1+h1 and so on ... these will automatically poplulate as numbers enter your boxes a1thru l1 "Alex" wrote: It depends on where you want to put the info.. do you want to put the information on another worksheet or on the same worksheet? also do you want to put the info at the end of all of those columns or do you want to do put it at the end...in between add a column between c and d then put a = sign in the column next to the 3 boxes you want to add, then the reference numbers to those boxes example =a1+b1+c1 press enter and the box will show the answer. You can then select the box with your mouse and you will highlight the box (note the box will have a dark black small box in the bottom right hand corner) grab that little box and drag to end of column where info is to be added... it will automatically populate boxes.. let me know if this helps... let me know if you have any questions "Jeff" wrote: Can anyone devise a formula to summarize monthly data by adding each three months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing this manually, but there must be a better way. Thanks. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing Quarters
put a equals sign in from of sum and put a ":" between a1:c1
"Jeff" wrote: Alex - Data is in Row 1, so monthly data for year 1 is in A1..L1. Right now, I'm typing sum(a1.c1) in A2, sum(d1.f1) in B2, etc. I want to automate this. "Alex" wrote: It depends on where you want to put the info.. do you want to put the information on another worksheet or on the same worksheet? also do you want to put the info at the end of all of those columns or do you want to do put it at the end...in between add a column between c and d then put a = sign in the column next to the 3 boxes you want to add, then the reference numbers to those boxes example =a1+b1+c1 press enter and the box will show the answer. You can then select the box with your mouse and you will highlight the box (note the box will have a dark black small box in the bottom right hand corner) grab that little box and drag to end of column where info is to be added... it will automatically populate boxes.. let me know if this helps... let me know if you have any questions "Jeff" wrote: Can anyone devise a formula to summarize monthly data by adding each three months? That is, I need to add columns A-C, D-F, G-I, etc. I've been doing this manually, but there must be a better way. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Quarters | Excel Worksheet Functions | |||
I want quarters not months | Excel Worksheet Functions | |||
Sum months into quarters? | Excel Discussion (Misc queries) | |||
Matching Quarters | Excel Worksheet Functions | |||
End of the 4 quarters | Excel Discussion (Misc queries) |