Using a Max Subtotal a Formula

[LindaD]

I have a large worksheet that I've defined a Max subtotal on end date. The
grouping is on a field that concatenates the account name & product name.
Each Account/product can have multiple records with different end dates. I
need to find the max end date for each group (used subtotal to do this) and
then calculate the number of months between the end date of each row and the
max end date for the group. I have created formulas to do this, but
unfortunately have not found an easy way to copy the formula to each row as
the Max subtotal always has to be adjusted for each group. Since there are
24000 rows in my spreadsheet, I'm looking for an easier way to do this than
to copy and adjust the formulas on every row. Any ideas?

 

[Pete_UK]

So, is the data sorted by your account/product field?

Can you post an example of your data, so I can see which columns you
are talking about?

Pete

 

[LindaD]

Below is a sample. The formula for column E is =round(($d$8-d2)/30,0).
Col A B C
D E
Account Product Account+Product End Date # Months
2 ABC Corp Product 1 ABC Corp--Product 1 7/31/2008 5
3 ABC Corp Product 1 ABC Corp--Product 1 7/31/2008 5
4 ABC Corp Product 1 ABC Corp--Product 1 9/30/2008 3
5 ABC Corp Product 1 ABC Corp--Product 1 9/30/2008 3
6 ABC Corp Product 1 ABC Corp--Product 1 12/31/2008 0
7 ABC Corp Product 1 ABC Corp--Product 1 12/31/2008 0
8 ABC Corp--Product 1 Max 12/31/2008
9 ABC Corp Product 2 ABC Corp--Product 2 8/31/2008 7
10ABC Corp Product 2 ABC Corp--Product 2 9/30/2008 6
11ABC Corp Product 2 ABC Corp--Product 2 12/31/2008 3
12ABC Corp Product 2 ABC Corp--Product 2 3/31/2009 0
13ABC Corp Product 2 ABC Corp--Product 2 3/31/2008 12
14 ABC Corp--Product 2 Max 3/31/2009
15 Grand Max 3/31/2009

 

[Pete_UK]

Okay, for this formula you will not need those subtotals, so to get
rid of them you can click anywhere within the data and then click on
Data | Subtotals | Remove All.

Then you can put this array* formula in E2:

=ROUND((MAX(IF(C$2:C$24000=C2,D$2$24000))-D2)/30,0)

I've assumed you have 24000 rows of data (but may be less if you get
rid of the subtotal rows). Then copy this down as required.

* An array formula needs to be committed using Ctrl-Shift-Enter (CSE)
rather than the normal Enter. If you do this correctly then Excel will
wrap curly braces { } around the formula when viewed in the formula
bar - do not type these yourself. If you subsequently edit/amend the
formula you must use CSE again.

Hope this helps.

Pete

 

[LindaD]

Hi Pete:

This worked perfectly. Thanks!

Okay, for this formula you will not need those subtotals, so to get
rid of them you can click anywhere within the data and then click on
Data | Subtotals | Remove All.

Then you can put this array* formula in E2:

=ROUND((MAX(IF(C$2:C$24000=C2,D$2$24000))-D2)/30,0)

I've assumed you have 24000 rows of data (but may be less if you get
rid of the subtotal rows). Then copy this down as required.

* An array formula needs to be committed using Ctrl-Shift-Enter (CSE)
rather than the normal Enter. If you do this correctly then Excel will
wrap curly braces { } around the formula when viewed in the formula
bar - do not type these yourself. If you subsequently edit/amend the
formula you must use CSE again.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Linda - thanks for feeding back.

Pete