Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a Max Subtotal a Formula
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a Max Subtotal a Formula
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 On Oct 1, 11:59*pm, LindaD wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a Max Subtotal a Formula
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" wrote: 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 On Oct 1, 11:59 pm, LindaD wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a Max Subtotal a Formula
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:D$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 On Oct 2, 1:31*am, LindaD wrote: 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" wrote: 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 On Oct 1, 11:59 pm, LindaD wrote: 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?- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a Max Subtotal a Formula
Hi Pete:
This worked perfectly. Thanks! "Pete_UK" wrote: 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:D$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 On Oct 2, 1:31 am, LindaD wrote: 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" wrote: 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 On Oct 1, 11:59 pm, LindaD wrote: 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?- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a Max Subtotal a Formula
You're welcome, Linda - thanks for feeding back.
Pete On Oct 3, 12:36*am, LindaD wrote: Hi Pete: This worked perfectly. *Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal Formula | Excel Worksheet Functions | |||
Formula Help - If and Subtotal(???) | Excel Discussion (Misc queries) | |||
pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
Help with SUBTOTAL formula | Excel Worksheet Functions | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |