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

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

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

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