Here is a scenario:

If:
Column A is list of AUTO MAKERS
Column B is list of DEALERSHIPS
Column C is list of dates when DEALERSHIP OPENED
Column D is list of dates when DEALERSHIP READY TO SELL CARS
Column E is the the difference of Column D and Column C (D-C) (Cycle Time)

There are a thousand rows and multiple column in this data set.

I would like to find out how long does it take the 25th percentile of any
DEALERSHIP to get READY TO SELL CARS.

Please share your thoughts.

Rum

Hi,

For column E I used

=DATEDIF(C2,D2,"d")

to calculate the difference in days then for the first quartile the ARRAY
formula

=AVERAGE(IF(E2:E19<"",IF(E2:E19<=QUARTILE(E2:E19, 1),E2:E19)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"Rum" wrote:

Here is a scenario:

If:
Column A is list of AUTO MAKERS
Column B is list of DEALERSHIPS
Column C is list of dates when DEALERSHIP OPENED
Column D is list of dates when DEALERSHIP READY TO SELL CARS
Column E is the the difference of Column D and Column C (D-C) (Cycle Time)

There are a thousand rows and multiple column in this data set.

I would like to find out how long does it take the 25th percentile of any
DEALERSHIP to get READY TO SELL CARS.

Please share your thoughts.

Rum

What is the advantage in using =DATEDIF(C2,D2,"d") rather than just =D2-C2,
Mike?
--
David Biddulph


"Mike H" wrote in message
...
Hi,

For column E I used

=DATEDIF(C2,D2,"d")

to calculate the difference in days then for the first quartile the ARRAY
formula

=AVERAGE(IF(E2:E19<"",IF(E2:E19<=QUARTILE(E2:E19, 1),E2:E19)))

This is an array formula which must be entered by pressing
CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly
brackets
'around the formula {}. You can't type these yourself. If you edit the
formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"Rum" wrote:

Here is a scenario:

If:
Column A is list of AUTO MAKERS
Column B is list of DEALERSHIPS
Column C is list of dates when DEALERSHIP OPENED
Column D is list of dates when DEALERSHIP READY TO SELL CARS
Column E is the the difference of Column D and Column C (D-C) (Cycle
Time)

There are a thousand rows and multiple column in this data set.

I would like to find out how long does it take the 25th percentile of any
DEALERSHIP to get READY TO SELL CARS.

Please share your thoughts.

Rum

David,

On reflection probably nothing, in fact on even more reflection I now prefer
D2-C2. Thanks for that.

Mike

"David Biddulph" wrote:

What is the advantage in using =DATEDIF(C2,D2,"d") rather than just =D2-C2,
Mike?
--
David Biddulph


"Mike H" wrote in message
...
Hi,

For column E I used

=DATEDIF(C2,D2,"d")

to calculate the difference in days then for the first quartile the ARRAY
formula

=AVERAGE(IF(E2:E19<"",IF(E2:E19<=QUARTILE(E2:E19, 1),E2:E19)))

This is an array formula which must be entered by pressing
CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly
brackets
'around the formula {}. You can't type these yourself. If you edit the
formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"Rum" wrote:

Here is a scenario:

If:
Column A is list of AUTO MAKERS
Column B is list of DEALERSHIPS
Column C is list of dates when DEALERSHIP OPENED
Column D is list of dates when DEALERSHIP READY TO SELL CARS
Column E is the the difference of Column D and Column C (D-C) (Cycle
Time)

There are a thousand rows and multiple column in this data set.

I would like to find out how long does it take the 25th percentile of any
DEALERSHIP to get READY TO SELL CARS.

Please share your thoughts.

Rum


.