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!
|