Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Question - Last Twelve Month Calculation
I am trying to create a formula that will automatically sum the last twelve months of data when new months are added (in columns) to the right of the most recent month. Also, don't want to use the "now" feature as information generally lags real time by a couple of months.
Months are columns and the item I want to sum is in the row below the column heading. Any help would be appreciated. Submitted via EggHeadCafe - Software Developer Portal of Choice A Dynamic Progress Bar for Your ASP Page http://www.eggheadcafe.com/tutorials...ss-bar-fo.aspx |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Question - Last Twelve Month Calculation
On Thu, 31 Dec 2009 11:20:11 -0800, joseph jordan wrote:
I am trying to create a formula that will automatically sum the last twelve months of data when new months are added (in columns) to the right of the most recent month. Also, don't want to use the "now" feature as information generally lags real time by a couple of months. Months are columns and the item I want to sum is in the row below the column heading. Any help would be appreciated. Submitted via EggHeadCafe - Software Developer Portal of Choice A Dynamic Progress Bar for Your ASP Page http://www.eggheadcafe.com/tutorials...ss-bar-fo.aspx Assuming that your data are on row 2 and that the cell next to the data you want to sum is blank, try this formula: =SUM(OFFSET(A2,,MIN(IF(ISBLANK(A2:IV2),COLUMN(A:IV )))-13,,12)) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Question - Last Twelve Month Calculation
If the data values you want to sum are in row 2, starting in column A, use the following array formula: =SUM(OFFSET(A2,0,MAX(--(A2:IV2<"")*(COLUMN(A2:IV2)))-MIN(12,COUNT(A2:IV2)),1,12)) This will find the right-most used cell in row 2, create a 12-column range ending at that cell, and SUM those cells. If there are fewer than 12 entries in row 2, all the entries are summed. This is an array formula, so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }. You do not type in the braces -- Excel puts them in automatically. The formula will not work properly if you do not enter it with CTRL SHIFT ENTER. For much more information about array formulas, see http://www.cpearson.com/Excel/ArrayFormulas.aspx. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Thu, 31 Dec 2009 11:20:11 -0800, joseph jordan wrote: I am trying to create a formula that will automatically sum the last twelve months of data when new months are added (in columns) to the right of the most recent month. Also, don't want to use the "now" feature as information generally lags real time by a couple of months. Months are columns and the item I want to sum is in the row below the column heading. Any help would be appreciated. Submitted via EggHeadCafe - Software Developer Portal of Choice A Dynamic Progress Bar for Your ASP Page http://www.eggheadcafe.com/tutorials/aspnet/0f8083d1-4588-413d-a9dd-e93432fd7e84/a-dynamic-progress-bar-fo.aspx |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question
Thanks for the help. Works great.
Lars-?ke Aspelin wrote: Assuming that your data are on row 2 and that the cell next to thedata you 31-Dec-09 Assuming that your data are on row 2 and that the cell next to the data you want to sum is blank, try this formula: =SUM(OFFSET(A2,,MIN(IF(ISBLANK(A2:IV2),COLUMN(A:IV )))-13,,12)) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-?ke Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice ASP.NET DropDown ListBox and XML Databinding http://www.eggheadcafe.com/tutorials...n-listbox.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Month Calculation | Excel Worksheet Functions | |||
Month end calculation | Excel Worksheet Functions | |||
Excel calculation question | Excel Discussion (Misc queries) | |||
month-to-month calculation | Excel Discussion (Misc queries) | |||
Excel auto calculation formula question. | Excel Worksheet Functions |