| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
(Max - Min) for a dynamic range within a table
I have a table that has 8457 rows, within the table there are 12
models, and within each model there are 5 phases for development. What I'm trying to do is place a formula into a table that will summarize the data I'm looking at by extracting the beginning date and ending date of the phase - take the difference of the two to provide the duration of that phase. Some example data below: Boat Phase Activity Desc Start Finish 205 Phase 1 Port Bow Lid Plug 1/1/2006 1/31/2006 205 Phase 1 Sun Lounge Mill 1 1/15/2006 2/12/2006 205 Phase 2 Sun Lounge Foam 2 2/16/2006 2/18/2006 205 Phase 2 STB Bow Lid Plug 2/11/2006 5/5/2006 205 Phase 2 Anchor Lid Plug 3/14/2006 4/12/2006 205 Phase 3 Sport Deck / Buck 5/1/2006 8/3/2006 205 Phase 3 Motor Apply Steel 8/7/2006 8/8/2006 205 Phase 3 Sun Lounge Mill 2 8/14/2006 8/18/2006 391 Phase 1 Sport Deck Buck 9/14/2006 1/13/2007 391 Phase 1 Helm Panels Design 9/28/2006 1/25/2007 391 Phase 2 Electrical Tech 1/29/2007 1/31/2007 391 Phase 2 W/S Install on plug 1/24/2007 4/17/2007 391 Phase 2 Liner Paint 1 2/24/2007 3/25/2007 391 Phase 3 Hull Mold Install 4/13/2007 7/16/2007 391 Phase 3 Sport Deck Mill 2 7/20/2007 7/21/2007 391 Phase 3 Sport Deck Foam 7/27/2007 7/31/2007 So then in a summary table below would be said formula: Boat Phase 1 Phase 2 Phase 3 205 42 83 109 391 133 83 109 So cell B2 would represent the MAXIMUM for phase 1 of the 205 (2/12/2006) minus the MINIMUM for phase 1 for the 205 (1/1/2006) which is 42 days. Etc Etc Etc for the remaining cells. Any help would be greatly appreciated. -Steve |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
(Max - Min) for a dynamic range within a table
In cell F2, put this, format for Number, and copy down......
=E2-D2 Vaya con Dios, Chuck, CABGx3 " wrote: I have a table that has 8457 rows, within the table there are 12 models, and within each model there are 5 phases for development. What I'm trying to do is place a formula into a table that will summarize the data I'm looking at by extracting the beginning date and ending date of the phase - take the difference of the two to provide the duration of that phase. Some example data below: Boat Phase Activity Desc Start Finish 205 Phase 1 Port Bow Lid Plug 1/1/2006 1/31/2006 205 Phase 1 Sun Lounge Mill 1 1/15/2006 2/12/2006 205 Phase 2 Sun Lounge Foam 2 2/16/2006 2/18/2006 205 Phase 2 STB Bow Lid Plug 2/11/2006 5/5/2006 205 Phase 2 Anchor Lid Plug 3/14/2006 4/12/2006 205 Phase 3 Sport Deck / Buck 5/1/2006 8/3/2006 205 Phase 3 Motor Apply Steel 8/7/2006 8/8/2006 205 Phase 3 Sun Lounge Mill 2 8/14/2006 8/18/2006 391 Phase 1 Sport Deck Buck 9/14/2006 1/13/2007 391 Phase 1 Helm Panels Design 9/28/2006 1/25/2007 391 Phase 2 Electrical Tech 1/29/2007 1/31/2007 391 Phase 2 W/S Install on plug 1/24/2007 4/17/2007 391 Phase 2 Liner Paint 1 2/24/2007 3/25/2007 391 Phase 3 Hull Mold Install 4/13/2007 7/16/2007 391 Phase 3 Sport Deck Mill 2 7/20/2007 7/21/2007 391 Phase 3 Sport Deck Foam 7/27/2007 7/31/2007 So then in a summary table below would be said formula: Boat Phase 1 Phase 2 Phase 3 205 42 83 109 391 133 83 109 So cell B2 would represent the MAXIMUM for phase 1 of the 205 (2/12/2006) minus the MINIMUM for phase 1 for the 205 (1/1/2006) which is 42 days. Etc Etc Etc for the remaining cells. Any help would be greatly appreciated. -Steve |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
(Max - Min) for a dynamic range within a table
I think you want the earliest start date and the latest finish date
for a particular boat and phase, so it would help if you were to define two named ranges - one for Boat and the other for Phase. The easiest way is to highlight the heading and data for these two columns and Insert | Name | Create, and click Top Row only. Then define two other named ranges for the start and finish dates in exactly the same way. Then in B2 of your other sheet you can add this array* formula: =MAX(IF((Boat=$A2)*(Phase=B$1),Finish,0)) - MIN(IF((Boat=$A2)*(Phase=B $1),Start,50000)) * As this is an array formula, then once you have typed it in (or subsequently amend it), you must use CTRL-SHIFT-ENTER (CSE) to commit it rather than the usual ENTER. If you do this correctly, then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Format the cell as Number, then copy it across two columns for the other phases. Then copy these three formula down for as many entries as you have in column A. Hope this helps. Pete On Nov 5, 7:48 pm, " wrote: I have a table that has 8457 rows, within the table there are 12 models, and within each model there are 5 phases for development. What I'm trying to do is place a formula into a table that will summarize the data I'm looking at by extracting the beginning date and ending date of the phase - take the difference of the two to provide the duration of that phase. Some example data below: Boat Phase Activity Desc Start Finish 205 Phase 1 Port Bow Lid Plug 1/1/2006 1/31/2006 205 Phase 1 Sun Lounge Mill 1 1/15/2006 2/12/2006 205 Phase 2 Sun Lounge Foam 2 2/16/2006 2/18/2006 205 Phase 2 STB Bow Lid Plug 2/11/2006 5/5/2006 205 Phase 2 Anchor Lid Plug 3/14/2006 4/12/2006 205 Phase 3 Sport Deck / Buck 5/1/2006 8/3/2006 205 Phase 3 Motor Apply Steel 8/7/2006 8/8/2006 205 Phase 3 Sun Lounge Mill 2 8/14/2006 8/18/2006 391 Phase 1 Sport Deck Buck 9/14/2006 1/13/2007 391 Phase 1 Helm Panels Design 9/28/2006 1/25/2007 391 Phase 2 Electrical Tech 1/29/2007 1/31/2007 391 Phase 2 W/S Install on plug 1/24/2007 4/17/2007 391 Phase 2 Liner Paint 1 2/24/2007 3/25/2007 391 Phase 3 Hull Mold Install 4/13/2007 7/16/2007 391 Phase 3 Sport Deck Mill 2 7/20/2007 7/21/2007 391 Phase 3 Sport Deck Foam 7/27/2007 7/31/2007 So then in a summary table below would be said formula: Boat Phase 1 Phase 2 Phase 3 205 42 83 109 391 133 83 109 So cell B2 would represent the MAXIMUM for phase 1 of the 205 (2/12/2006) minus the MINIMUM for phase 1 for the 205 (1/1/2006) which is 42 days. Etc Etc Etc for the remaining cells. Any help would be greatly appreciated. -Steve |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
(Max - Min) for a dynamic range within a table
Perfect Thanks!
On Nov 5, 12:57 pm, Pete_UK wrote: I think you want the earliest start date and the latest finish date for a particular boat and phase, so it would help if you were to define two named ranges - one for Boat and the other for Phase. The easiest way is to highlight the heading and data for these two columns and Insert | Name | Create, and click Top Row only. Then define two other named ranges for the start and finish dates in exactly the same way. Then in B2 of your other sheet you can add this array* formula: =MAX(IF((Boat=$A2)*(Phase=B$1),Finish,0)) - MIN(IF((Boat=$A2)*(Phase=B $1),Start,50000)) * As this is an array formula, then once you have typed it in (or subsequently amend it), you must use CTRL-SHIFT-ENTER (CSE) to commit it rather than the usual ENTER. If you do this correctly, then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Format the cell as Number, then copy it across two columns for the other phases. Then copy these three formula down for as many entries as you have in column A. Hope this helps. Pete On Nov 5, 7:48 pm, " wrote: I have a table that has 8457 rows, within the table there are 12 models, and within each model there are 5 phases for development. What I'm trying to do is place a formula into a table that will summarize the data I'm looking at by extracting the beginning date and ending date of the phase - take the difference of the two to provide the duration of that phase. Some example data below: Boat Phase Activity Desc Start Finish 205 Phase 1 Port Bow Lid Plug 1/1/2006 1/31/2006 205 Phase 1 Sun Lounge Mill 1 1/15/2006 2/12/2006 205 Phase 2 Sun Lounge Foam 2 2/16/2006 2/18/2006 205 Phase 2 STB Bow Lid Plug 2/11/2006 5/5/2006 205 Phase 2 Anchor Lid Plug 3/14/2006 4/12/2006 205 Phase 3 Sport Deck / Buck 5/1/2006 8/3/2006 205 Phase 3 Motor Apply Steel 8/7/2006 8/8/2006 205 Phase 3 Sun Lounge Mill 2 8/14/2006 8/18/2006 391 Phase 1 Sport Deck Buck 9/14/2006 1/13/2007 391 Phase 1 Helm Panels Design 9/28/2006 1/25/2007 391 Phase 2 Electrical Tech 1/29/2007 1/31/2007 391 Phase 2 W/S Install on plug 1/24/2007 4/17/2007 391 Phase 2 Liner Paint 1 2/24/2007 3/25/2007 391 Phase 3 Hull Mold Install 4/13/2007 7/16/2007 391 Phase 3 Sport Deck Mill 2 7/20/2007 7/21/2007 391 Phase 3 Sport Deck Foam 7/27/2007 7/31/2007 So then in a summary table below would be said formula: Boat Phase 1 Phase 2 Phase 3 205 42 83 109 391 133 83 109 So cell B2 would represent the MAXIMUM for phase 1 of the 205 (2/12/2006) minus the MINIMUM for phase 1 for the 205 (1/1/2006) which is 42 days. Etc Etc Etc for the remaining cells. Any help would be greatly appreciated. -Steve- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
(Max - Min) for a dynamic range within a table
You're welcome, Steven - thanks for feeding back.
Pete On Nov 6, 1:09 am, " wrote: Perfect Thanks! On Nov 5, 12:57 pm, Pete_UK wrote: I think you want the earliest start date and the latest finish date for a particular boat and phase, so it would help if you were to define two named ranges - one for Boat and the other for Phase. The easiest way is to highlight the heading and data for these two columns and Insert | Name | Create, and click Top Row only. Then define two other named ranges for the start and finish dates in exactly the same way. Then in B2 of your other sheet you can add this array* formula: =MAX(IF((Boat=$A2)*(Phase=B$1),Finish,0)) - MIN(IF((Boat=$A2)*(Phase=B $1),Start,50000)) * As this is an array formula, then once you have typed it in (or subsequently amend it), you must use CTRL-SHIFT-ENTER (CSE) to commit it rather than the usual ENTER. If you do this correctly, then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Format the cell as Number, then copy it across two columns for the other phases. Then copy these three formula down for as many entries as you have in column A. Hope this helps. Pete On Nov 5, 7:48 pm, " wrote: I have a table that has 8457 rows, within the table there are 12 models, and within each model there are 5 phases for development. What I'm trying to do is place a formula into a table that will summarize the data I'm looking at by extracting the beginning date and ending date of the phase - take the difference of the two to provide the duration of that phase. Some example data below: Boat Phase Activity Desc Start Finish 205 Phase 1 Port Bow Lid Plug 1/1/2006 1/31/2006 205 Phase 1 Sun Lounge Mill 1 1/15/2006 2/12/2006 205 Phase 2 Sun Lounge Foam 2 2/16/2006 2/18/2006 205 Phase 2 STB Bow Lid Plug 2/11/2006 5/5/2006 205 Phase 2 Anchor Lid Plug 3/14/2006 4/12/2006 205 Phase 3 Sport Deck / Buck 5/1/2006 8/3/2006 205 Phase 3 Motor Apply Steel 8/7/2006 8/8/2006 205 Phase 3 Sun Lounge Mill 2 8/14/2006 8/18/2006 391 Phase 1 Sport Deck Buck 9/14/2006 1/13/2007 391 Phase 1 Helm Panels Design 9/28/2006 1/25/2007 391 Phase 2 Electrical Tech 1/29/2007 1/31/2007 391 Phase 2 W/S Install on plug 1/24/2007 4/17/2007 391 Phase 2 Liner Paint 1 2/24/2007 3/25/2007 391 Phase 3 Hull Mold Install 4/13/2007 7/16/2007 391 Phase 3 Sport Deck Mill 2 7/20/2007 7/21/2007 391 Phase 3 Sport Deck Foam 7/27/2007 7/31/2007 So then in a summary table below would be said formula: Boat Phase 1 Phase 2 Phase 3 205 42 83 109 391 133 83 109 So cell B2 would represent the MAXIMUM for phase 1 of the 205 (2/12/2006) minus the MINIMUM for phase 1 for the 205 (1/1/2006) which is 42 days. Etc Etc Etc for the remaining cells. Any help would be greatly appreciated. -Steve- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic range in Pivot table | Excel Discussion (Misc queries) | |||
| Using dynamic range to create pivot table | Excel Discussion (Misc queries) | |||
| Populate a table with a dynamic range | Excel Worksheet Functions | |||
| Dynamic Range in a Pivot Table | Excel Discussion (Misc queries) | |||
| Crate group of date, with Dynamic Range in pivot table not working | Excel Discussion (Misc queries) | |||
Linear Mode
