(Max - Min) for a dynamic range within a table

[steven.britton]

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

 

[CLR]

In cell F2, put this, format for Number, and copy down......

=E2-D2

Vaya con Dios,
Chuck, CABGx3

 

[Pete_UK]

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

 

[steven.britton]

Perfect Thanks!

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











- Show quoted text -

 

[Pete_UK]

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

Pete