Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula needed for Sum(A1:C1) then Sum(D1:F1) in next cell
I am converting a monthly revenue table to a quarterly revenue table. I would
like to add A1:C1 and put it the result in a new cell and then autofill the cell to the right of it so that it automatically adds D1:F1 for the next value A B C D E F 1 Jan Feb March April May June 2 10 12 12 13 14 15 Qtr1 Qtr 2 Qtr 3 sum A2:C2 Sum D2:F2 Etc... using autofill currently using autofill the first qtr is correct "=Sum(A2:C2) but the next cell gets filled with "=sum(B2:D2) instead of adding the next 3 months. I appreciate anyone's help! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula needed for Sum(A1:C1) then Sum(D1:F1) in next cell
One way:
Assume the first formula is entered in A4: =SUM(OFFSET($A1,,(COLUMNS($A4:A4)-1)*3,,3)) However, if this is for just a single year which only has 4 quarters, what's wrong with using 4 simple sum formulas: =SUM(A1:C1) =SUM(D1:F1) =SUM(G1:I1) =SUM(J1:L1) Using those 4 formulas is better than using the formula I suggested. -- Biff Microsoft Excel MVP "Steve" wrote in message ... I am converting a monthly revenue table to a quarterly revenue table. I would like to add A1:C1 and put it the result in a new cell and then autofill the cell to the right of it so that it automatically adds D1:F1 for the next value A B C D E F 1 Jan Feb March April May June 2 10 12 12 13 14 15 Qtr1 Qtr 2 Qtr 3 sum A2:C2 Sum D2:F2 Etc... using autofill currently using autofill the first qtr is correct "=Sum(A2:C2) but the next cell gets filled with "=sum(B2:D2) instead of adding the next 3 months. I appreciate anyone's help! |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula needed for Sum(A1:C1) then Sum(D1:F1) in next cell
Hi T. Valko,
The formula worked so thank you! :) I now understand this sum offset function with the exception of this column statement: (COLUMNS($A4:A4)-1)*3 Can you explain how this works? Specific questions about how this works: What is the purpose for the column array getting wider as I autofill the cells to the right with this formula? (Next cell has $A4:B4 then the next cell has $A4:C4, etc...) What is the purpose of "-1" in this expression What is the purpose of "*3" in this expression I know that overall this is expression identifies the 3 columns where the values are to be summed, but I don't understand the logic. In order for me to correctly write and use this formula in the future, I need to be able to understand this column expression. PS I used one year as an example but I have a lot more years per request and then I have often have to do a CY quarterly version and a FY quarterly version of the monthly tables, so I thought there had to be an easier way worth learning. :) "T. Valko" wrote: One way: Assume the first formula is entered in A4: =SUM(OFFSET($A1,,(COLUMNS($A4:A4)-1)*3,,3)) However, if this is for just a single year which only has 4 quarters, what's wrong with using 4 simple sum formulas: =SUM(A1:C1) =SUM(D1:F1) =SUM(G1:I1) =SUM(J1:L1) Using those 4 formulas is better than using the formula I suggested. -- Biff Microsoft Excel MVP "Steve" wrote in message ... I am converting a monthly revenue table to a quarterly revenue table. I would like to add A1:C1 and put it the result in a new cell and then autofill the cell to the right of it so that it automatically adds D1:F1 for the next value A B C D E F 1 Jan Feb March April May June 2 10 12 12 13 14 15 Qtr1 Qtr 2 Qtr 3 sum A2:C2 Sum D2:F2 Etc... using autofill currently using autofill the first qtr is correct "=Sum(A2:C2) but the next cell gets filled with "=sum(B2:D2) instead of adding the next 3 months. I appreciate anyone's help! |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula needed for Sum(A1:C1) then Sum(D1:F1) in next cell
Here's how this works...
=SUM(OFFSET($A1,,(COLUMNS($A4:A4)-1)*3,,3)) You want to sum groups of 3 cells starting from cell A1. We use the OFFSET function to pass the range of these 3 cells to the SUM function. These are the arguments that OFFSET takes: OFFSET(reference,rows,cols,height,width) $A1 is the reference or "anchor" cell. That's where we're starting from. Since the data we're interested in is all on the same row we don't need to offset A1 by any rows so that argument is empty and defaults to 0. That means offset A1 by 0 rows. We're interested in cells in groups of 3 so we need to offset A1 by 3 columns with each cell that we copy the formula to. That's what the cols argument (COLUMNS($A4:A4)-1)*3 is doing. The groups of cells we're interested in are A1:C1, D1:F1, G1:I1, J1:L1, etc. So, we use (COLUMNS($A4:A4)-1)*3 to increment the column offset by 3 for each cell that the formula is copied to. With the formula entered in the first cell of A4, COLUMNS($A4:A4) evaluates to 1. The columns function simply counts the number of columns referenced in its argument. As we copy the formula across the range reference will incremnt like this: COLUMNS($A4:A4) = 1 COLUMNS($A4:B4) = 2 COLUMNS($A4:C4) = 3 COLUMNS($A4:D4) = 4 We use the multiplier of 3 to increment the offset by groups of 3. So COLUMNS($A4:A4)*3 = 3 which means offset A1 by 3 columns and that would put us at cell D1. However, we want to start at cell A1 not cell D1 so we use -1 to adjust for this. Like this: (COLUMNS($A4:A4)-1) = 0 * 3 = 0 (COLUMNS($A4:B4)-1) = 1 * 3 = 3 (COLUMNS($A4:C4)-1) = 2 * 3 = 6 (COLUMNS($A4:D4)-1) = 3 * 3 = 9 So, this means offset A1 by 0 columns, 3 columns, 6 columns, 9 columns, etc. Now, we need to tell OFFSET how high and wide the range we're interested in is. Since the data is on the same row we leave the height argument empty and this defaults to 1. We want to sum every 3 cells so the width argument is 3. So, in plain English: A4 = offset A1 by 0 columns and sum A1:C1 B4 = offset A1 by 3 columns and sum D1:F1 C4 = offset A1 by 6 columns and sum G1:I1 D4 = offset A1 by 9 columns and sum J1:L1 -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi T. Valko, The formula worked so thank you! :) I now understand this sum offset function with the exception of this column statement: (COLUMNS($A4:A4)-1)*3 Can you explain how this works? Specific questions about how this works: What is the purpose for the column array getting wider as I autofill the cells to the right with this formula? (Next cell has $A4:B4 then the next cell has $A4:C4, etc...) What is the purpose of "-1" in this expression What is the purpose of "*3" in this expression I know that overall this is expression identifies the 3 columns where the values are to be summed, but I don't understand the logic. In order for me to correctly write and use this formula in the future, I need to be able to understand this column expression. PS I used one year as an example but I have a lot more years per request and then I have often have to do a CY quarterly version and a FY quarterly version of the monthly tables, so I thought there had to be an easier way worth learning. :) "T. Valko" wrote: One way: Assume the first formula is entered in A4: =SUM(OFFSET($A1,,(COLUMNS($A4:A4)-1)*3,,3)) However, if this is for just a single year which only has 4 quarters, what's wrong with using 4 simple sum formulas: =SUM(A1:C1) =SUM(D1:F1) =SUM(G1:I1) =SUM(J1:L1) Using those 4 formulas is better than using the formula I suggested. -- Biff Microsoft Excel MVP "Steve" wrote in message ... I am converting a monthly revenue table to a quarterly revenue table. I would like to add A1:C1 and put it the result in a new cell and then autofill the cell to the right of it so that it automatically adds D1:F1 for the next value A B C D E F 1 Jan Feb March April May June 2 10 12 12 13 14 15 Qtr1 Qtr 2 Qtr 3 sum A2:C2 Sum D2:F2 Etc... using autofill currently using autofill the first qtr is correct "=Sum(A2:C2) but the next cell gets filled with "=sum(B2:D2) instead of adding the next 3 months. I appreciate anyone's help! |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula needed for Sum(A1:C1) then Sum(D1:F1) in next cell
This was an absolutely perfect explanation of how it works. Thank you for
going "above and beyond" to explain the solution in a clear and concise manner. Steve "T. Valko" wrote: Here's how this works... =SUM(OFFSET($A1,,(COLUMNS($A4:A4)-1)*3,,3)) You want to sum groups of 3 cells starting from cell A1. We use the OFFSET function to pass the range of these 3 cells to the SUM function. These are the arguments that OFFSET takes: OFFSET(reference,rows,cols,height,width) $A1 is the reference or "anchor" cell. That's where we're starting from. Since the data we're interested in is all on the same row we don't need to offset A1 by any rows so that argument is empty and defaults to 0. That means offset A1 by 0 rows. We're interested in cells in groups of 3 so we need to offset A1 by 3 columns with each cell that we copy the formula to. That's what the cols argument (COLUMNS($A4:A4)-1)*3 is doing. The groups of cells we're interested in are A1:C1, D1:F1, G1:I1, J1:L1, etc. So, we use (COLUMNS($A4:A4)-1)*3 to increment the column offset by 3 for each cell that the formula is copied to. With the formula entered in the first cell of A4, COLUMNS($A4:A4) evaluates to 1. The columns function simply counts the number of columns referenced in its argument. As we copy the formula across the range reference will incremnt like this: COLUMNS($A4:A4) = 1 COLUMNS($A4:B4) = 2 COLUMNS($A4:C4) = 3 COLUMNS($A4:D4) = 4 We use the multiplier of 3 to increment the offset by groups of 3. So COLUMNS($A4:A4)*3 = 3 which means offset A1 by 3 columns and that would put us at cell D1. However, we want to start at cell A1 not cell D1 so we use -1 to adjust for this. Like this: (COLUMNS($A4:A4)-1) = 0 * 3 = 0 (COLUMNS($A4:B4)-1) = 1 * 3 = 3 (COLUMNS($A4:C4)-1) = 2 * 3 = 6 (COLUMNS($A4:D4)-1) = 3 * 3 = 9 So, this means offset A1 by 0 columns, 3 columns, 6 columns, 9 columns, etc. Now, we need to tell OFFSET how high and wide the range we're interested in is. Since the data is on the same row we leave the height argument empty and this defaults to 1. We want to sum every 3 cells so the width argument is 3. So, in plain English: A4 = offset A1 by 0 columns and sum A1:C1 B4 = offset A1 by 3 columns and sum D1:F1 C4 = offset A1 by 6 columns and sum G1:I1 D4 = offset A1 by 9 columns and sum J1:L1 -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi T. Valko, The formula worked so thank you! :) I now understand this sum offset function with the exception of this column statement: (COLUMNS($A4:A4)-1)*3 Can you explain how this works? Specific questions about how this works: What is the purpose for the column array getting wider as I autofill the cells to the right with this formula? (Next cell has $A4:B4 then the next cell has $A4:C4, etc...) What is the purpose of "-1" in this expression What is the purpose of "*3" in this expression I know that overall this is expression identifies the 3 columns where the values are to be summed, but I don't understand the logic. In order for me to correctly write and use this formula in the future, I need to be able to understand this column expression. PS I used one year as an example but I have a lot more years per request and then I have often have to do a CY quarterly version and a FY quarterly version of the monthly tables, so I thought there had to be an easier way worth learning. :) "T. Valko" wrote: One way: Assume the first formula is entered in A4: =SUM(OFFSET($A1,,(COLUMNS($A4:A4)-1)*3,,3)) However, if this is for just a single year which only has 4 quarters, what's wrong with using 4 simple sum formulas: =SUM(A1:C1) =SUM(D1:F1) =SUM(G1:I1) =SUM(J1:L1) Using those 4 formulas is better than using the formula I suggested. -- Biff Microsoft Excel MVP "Steve" wrote in message ... I am converting a monthly revenue table to a quarterly revenue table. I would like to add A1:C1 and put it the result in a new cell and then autofill the cell to the right of it so that it automatically adds D1:F1 for the next value A B C D E F 1 Jan Feb March April May June 2 10 12 12 13 14 15 Qtr1 Qtr 2 Qtr 3 sum A2:C2 Sum D2:F2 Etc... using autofill currently using autofill the first qtr is correct "=Sum(A2:C2) but the next cell gets filled with "=sum(B2:D2) instead of adding the next 3 months. I appreciate anyone's help! |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula needed for Sum(A1:C1) then Sum(D1:F1) in next cell
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Steve" wrote in message ... This was an absolutely perfect explanation of how it works. Thank you for going "above and beyond" to explain the solution in a clear and concise manner. Steve "T. Valko" wrote: Here's how this works... =SUM(OFFSET($A1,,(COLUMNS($A4:A4)-1)*3,,3)) You want to sum groups of 3 cells starting from cell A1. We use the OFFSET function to pass the range of these 3 cells to the SUM function. These are the arguments that OFFSET takes: OFFSET(reference,rows,cols,height,width) $A1 is the reference or "anchor" cell. That's where we're starting from. Since the data we're interested in is all on the same row we don't need to offset A1 by any rows so that argument is empty and defaults to 0. That means offset A1 by 0 rows. We're interested in cells in groups of 3 so we need to offset A1 by 3 columns with each cell that we copy the formula to. That's what the cols argument (COLUMNS($A4:A4)-1)*3 is doing. The groups of cells we're interested in are A1:C1, D1:F1, G1:I1, J1:L1, etc. So, we use (COLUMNS($A4:A4)-1)*3 to increment the column offset by 3 for each cell that the formula is copied to. With the formula entered in the first cell of A4, COLUMNS($A4:A4) evaluates to 1. The columns function simply counts the number of columns referenced in its argument. As we copy the formula across the range reference will incremnt like this: COLUMNS($A4:A4) = 1 COLUMNS($A4:B4) = 2 COLUMNS($A4:C4) = 3 COLUMNS($A4:D4) = 4 We use the multiplier of 3 to increment the offset by groups of 3. So COLUMNS($A4:A4)*3 = 3 which means offset A1 by 3 columns and that would put us at cell D1. However, we want to start at cell A1 not cell D1 so we use -1 to adjust for this. Like this: (COLUMNS($A4:A4)-1) = 0 * 3 = 0 (COLUMNS($A4:B4)-1) = 1 * 3 = 3 (COLUMNS($A4:C4)-1) = 2 * 3 = 6 (COLUMNS($A4:D4)-1) = 3 * 3 = 9 So, this means offset A1 by 0 columns, 3 columns, 6 columns, 9 columns, etc. Now, we need to tell OFFSET how high and wide the range we're interested in is. Since the data is on the same row we leave the height argument empty and this defaults to 1. We want to sum every 3 cells so the width argument is 3. So, in plain English: A4 = offset A1 by 0 columns and sum A1:C1 B4 = offset A1 by 3 columns and sum D1:F1 C4 = offset A1 by 6 columns and sum G1:I1 D4 = offset A1 by 9 columns and sum J1:L1 -- Biff Microsoft Excel MVP "Steve" wrote in message ... Hi T. Valko, The formula worked so thank you! :) I now understand this sum offset function with the exception of this column statement: (COLUMNS($A4:A4)-1)*3 Can you explain how this works? Specific questions about how this works: What is the purpose for the column array getting wider as I autofill the cells to the right with this formula? (Next cell has $A4:B4 then the next cell has $A4:C4, etc...) What is the purpose of "-1" in this expression What is the purpose of "*3" in this expression I know that overall this is expression identifies the 3 columns where the values are to be summed, but I don't understand the logic. In order for me to correctly write and use this formula in the future, I need to be able to understand this column expression. PS I used one year as an example but I have a lot more years per request and then I have often have to do a CY quarterly version and a FY quarterly version of the monthly tables, so I thought there had to be an easier way worth learning. :) "T. Valko" wrote: One way: Assume the first formula is entered in A4: =SUM(OFFSET($A1,,(COLUMNS($A4:A4)-1)*3,,3)) However, if this is for just a single year which only has 4 quarters, what's wrong with using 4 simple sum formulas: =SUM(A1:C1) =SUM(D1:F1) =SUM(G1:I1) =SUM(J1:L1) Using those 4 formulas is better than using the formula I suggested. -- Biff Microsoft Excel MVP "Steve" wrote in message ... I am converting a monthly revenue table to a quarterly revenue table. I would like to add A1:C1 and put it the result in a new cell and then autofill the cell to the right of it so that it automatically adds D1:F1 for the next value A B C D E F 1 Jan Feb March April May June 2 10 12 12 13 14 15 Qtr1 Qtr 2 Qtr 3 sum A2:C2 Sum D2:F2 Etc... using autofill currently using autofill the first qtr is correct "=Sum(A2:C2) but the next cell gets filled with "=sum(B2:D2) instead of adding the next 3 months. I appreciate anyone's help! |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula needed for Sum(A1:C1) then Sum(D1:F1) in next cell
Put first formula =SUM(A2:C2) in whatever cell you wanted, lets assume A5.
Put next formula =SUM(D2:F2) in cell 3 cells to the right, eg D5. Now select A5:F5. Now grab the fill handle and fill right as far as necessary (lets assume IV5). When done simply select A5:IV5, do Edit / Go To / Special / Blank cells, then do edit / delete / shift cells to left. Job done Regards Ken......................... "Steve" wrote in message ... I am converting a monthly revenue table to a quarterly revenue table. I would like to add A1:C1 and put it the result in a new cell and then autofill the cell to the right of it so that it automatically adds D1:F1 for the next value A B C D E F 1 Jan Feb March April May June 2 10 12 12 13 14 15 Qtr1 Qtr 2 Qtr 3 sum A2:C2 Sum D2:F2 Etc... using autofill currently using autofill the first qtr is correct "=Sum(A2:C2) but the next cell gets filled with "=sum(B2:D2) instead of adding the next 3 months. I appreciate anyone's help! |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula needed for Sum(A1:C1) then Sum(D1:F1) in next cell
Hi Ken,
This suggestion also worked. The use of the "edit command" for deleting blank cells is something I have needed for many other situations so thank you. Steve "Ken Wright" wrote: Put first formula =SUM(A2:C2) in whatever cell you wanted, lets assume A5. Put next formula =SUM(D2:F2) in cell 3 cells to the right, eg D5. Now select A5:F5. Now grab the fill handle and fill right as far as necessary (lets assume IV5). When done simply select A5:IV5, do Edit / Go To / Special / Blank cells, then do edit / delete / shift cells to left. Job done Regards Ken......................... "Steve" wrote in message ... I am converting a monthly revenue table to a quarterly revenue table. I would like to add A1:C1 and put it the result in a new cell and then autofill the cell to the right of it so that it automatically adds D1:F1 for the next value A B C D E F 1 Jan Feb March April May June 2 10 12 12 13 14 15 Qtr1 Qtr 2 Qtr 3 sum A2:C2 Sum D2:F2 Etc... using autofill currently using autofill the first qtr is correct "=Sum(A2:C2) but the next cell gets filled with "=sum(B2:D2) instead of adding the next 3 months. I appreciate anyone's help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Some Help Needed on getting info out of a cell from a formula | New Users to Excel | |||
If Then Formula Help Needed | Excel Worksheet Functions | |||
putting a string from one cell in the formula of another -- indirect needed? | Excel Worksheet Functions | |||
Formula Needed! | Excel Discussion (Misc queries) | |||
Look Up and Cell Reference - Formula Help Needed | Excel Worksheet Functions |