| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A formula that autofills by trend, not linear
I have groups of months on one sheet, and a yearly summary sheet besides.
I need the first yearly formula to sum the range Months!A1:12. I need the second year to sum the range Months!A13:A24. As I autofill this formula down, I need the references to jump by 12, but Excel is only counting up by 1. How do I set Excel straight on this one? Thank you for your benevolence. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A formula that autofills by trend, not linear
Assume you enter the first formula in cell A1.
=SUM(OFFSET(Months!A$1,(ROWS(A$1:A1)-1)*12,,12)) Copy down as needed. A1 will be the sum of Months!A1:A12 A2 will be the sum of Months!A13:A24 A3 will be the sum of Months!A25:A36 A4 will be the sum of Months!A37:A48 etc etc -- Biff Microsoft Excel MVP "Arlen" wrote in message ... I have groups of months on one sheet, and a yearly summary sheet besides. I need the first yearly formula to sum the range Months!A1:12. I need the second year to sum the range Months!A13:A24. As I autofill this formula down, I need the references to jump by 12, but Excel is only counting up by 1. How do I set Excel straight on this one? Thank you for your benevolence. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A formula that autofills by trend, not linear
Thanks, Biff!
Perfect "T. Valko" wrote: Assume you enter the first formula in cell A1. =SUM(OFFSET(Months!A$1,(ROWS(A$1:A1)-1)*12,,12)) Copy down as needed. A1 will be the sum of Months!A1:A12 A2 will be the sum of Months!A13:A24 A3 will be the sum of Months!A25:A36 A4 will be the sum of Months!A37:A48 etc etc -- Biff Microsoft Excel MVP "Arlen" wrote in message ... I have groups of months on one sheet, and a yearly summary sheet besides. I need the first yearly formula to sum the range Months!A1:12. I need the second year to sum the range Months!A13:A24. As I autofill this formula down, I need the references to jump by 12, but Excel is only counting up by 1. How do I set Excel straight on this one? Thank you for your benevolence. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A formula that autofills by trend, not linear
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Arlen" wrote in message ... Thanks, Biff! Perfect "T. Valko" wrote: Assume you enter the first formula in cell A1. =SUM(OFFSET(Months!A$1,(ROWS(A$1:A1)-1)*12,,12)) Copy down as needed. A1 will be the sum of Months!A1:A12 A2 will be the sum of Months!A13:A24 A3 will be the sum of Months!A25:A36 A4 will be the sum of Months!A37:A48 etc etc -- Biff Microsoft Excel MVP "Arlen" wrote in message ... I have groups of months on one sheet, and a yearly summary sheet besides. I need the first yearly formula to sum the range Months!A1:12. I need the second year to sum the range Months!A13:A24. As I autofill this formula down, I need the references to jump by 12, but Excel is only counting up by 1. How do I set Excel straight on this one? Thank you for your benevolence. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A formula that autofills by trend rather than linear | Excel Discussion (Misc queries) | |||
| Linear trend function: 5 year increment | Excel Discussion (Misc queries) | |||
| Extracting Constant from Non-linear Trend line | Charts and Charting in Excel | |||
| Linear Regression using the TREND function | Excel Worksheet Functions | |||
| How do I anchor one end of a linear trend line? | Charts and Charting in Excel | |||
Linear Mode
