Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Sum Formulas
Hello,
In a lot of my work, I take monthly data and compress it into quarterly data for reports. I use the Sum function to do this and I am looking for an easy way to replicate the formula. Here's my problem: Say I have data in a column covering January through December. Elsewhere, I have a formula to calculate the First Quarter results SUM(JAN:MAR). But when I copy this formula, I usually end up with something like this in the subsequent cells: SUM(FEB:APR) SUM(MAR:MAY) SUM(APR:JUN) And so forth. I realize that this is because of the position of the formulas in relation to the original data on the spreadsheet. I could simply copy and paste the formula every three spaces down, but I don't want to have unused space between my quarterly formulas. Is there something I can do to the original formula so that when I copy it, it knows to move down three cells, rather than one? I.e., so I can copy it many times and get this: SUM(JAN:MAR) SUM(APR:JUN) SUM(JUL:SEP) SUM(OCT:DEC) SUM(JAN:MAR) And so forth . . . . Thanks for your help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Sum Formulas
It's not a problem but we need to know what JAN:MAR means? Is JAN a defined
name for a group of cells like A1:A30 or is JAN the name of one cell? If the latter you could use =SUM(INDEX(MyRange,ROWS($A$1:A1)*3-2):INDEX(MyRange,ROWS($A$1:A1)*3)) and copy down will sum first 3 cells, then starting with the 4th cell next 3 cells and so on, this can of course be applied to larger ranges as well and although it might be shorter to use OFFSET this version is non volatile whereas OFFSET or INDIRECT are not -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Justin Hoffmann" wrote in message ... Hello, In a lot of my work, I take monthly data and compress it into quarterly data for reports. I use the Sum function to do this and I am looking for an easy way to replicate the formula. Here's my problem: Say I have data in a column covering January through December. Elsewhere, I have a formula to calculate the First Quarter results SUM(JAN:MAR). But when I copy this formula, I usually end up with something like this in the subsequent cells: SUM(FEB:APR) SUM(MAR:MAY) SUM(APR:JUN) And so forth. I realize that this is because of the position of the formulas in relation to the original data on the spreadsheet. I could simply copy and paste the formula every three spaces down, but I don't want to have unused space between my quarterly formulas. Is there something I can do to the original formula so that when I copy it, it knows to move down three cells, rather than one? I.e., so I can copy it many times and get this: SUM(JAN:MAR) SUM(APR:JUN) SUM(JUL:SEP) SUM(OCT:DEC) SUM(JAN:MAR) And so forth . . . . Thanks for your help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Sum Formulas
JAN (for January) is any particular cell. Let's say it is A1, February is
A2, March is A3, and so on. So my first formula is SUM(A1:A3). But when I copy it to a cell below, I get SUM(A2:A4), when what I really want is SUM(A4:A6), followed by SUM(A7:A9), and SUM(A10:A12) to round out the year. I also want to be able to use this formula across columns as well. I tried playing around with the formula you gave below, but I'm really a novice when it comes to excel formulas, and it gives me a #NAME? error. in article , Peo Sjoblom at wrote on 5/11/06 3:49 PM: It's not a problem but we need to know what JAN:MAR means? Is JAN a defined name for a group of cells like A1:A30 or is JAN the name of one cell? If the latter you could use =SUM(INDEX(MyRange,ROWS($A$1:A1)*3-2):INDEX(MyRange,ROWS($A$1:A1)*3)) and copy down will sum first 3 cells, then starting with the 4th cell next 3 cells and so on, this can of course be applied to larger ranges as well and although it might be shorter to use OFFSET this version is non volatile whereas OFFSET or INDIRECT are not |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying Sum Formulas
Then you can use the formula I gave you, assume all the cells are A1:A12
=SUM(INDEX($A$1:$A$12,ROWS($A$1:A1)*3-2):INDEX($A$1:$A$12,ROWS($A$1:A1)*3)) copy down will sum A1:A3, A4:A6 and so on -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Justin Hoffmann" wrote in message ... JAN (for January) is any particular cell. Let's say it is A1, February is A2, March is A3, and so on. So my first formula is SUM(A1:A3). But when I copy it to a cell below, I get SUM(A2:A4), when what I really want is SUM(A4:A6), followed by SUM(A7:A9), and SUM(A10:A12) to round out the year. I also want to be able to use this formula across columns as well. I tried playing around with the formula you gave below, but I'm really a novice when it comes to excel formulas, and it gives me a #NAME? error. in article , Peo Sjoblom at wrote on 5/11/06 3:49 PM: It's not a problem but we need to know what JAN:MAR means? Is JAN a defined name for a group of cells like A1:A30 or is JAN the name of one cell? If the latter you could use =SUM(INDEX(MyRange,ROWS($A$1:A1)*3-2):INDEX(MyRange,ROWS($A$1:A1)*3)) and copy down will sum first 3 cells, then starting with the 4th cell next 3 cells and so on, this can of course be applied to larger ranges as well and although it might be shorter to use OFFSET this version is non volatile whereas OFFSET or INDIRECT are not |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying formulas to other cells. Keeping references w/o $ sign. | Excel Discussion (Misc queries) | |||
Copying formulas to other cells. Keeping references w/o $ sign. | Excel Discussion (Misc queries) | |||
excel 2002 - copying formulas to another worksheet | Excel Discussion (Misc queries) | |||
Excel & Copying Formulas | Excel Worksheet Functions | |||
Copying formulas | Excel Worksheet Functions |