I have a spreadsheet with many rows (1000s) and columns. I am trying to
determine how long it took for a percentage of franchises to open. Lets say
I have:

Column A: Company Name
Column B: Date when product was ready (ie Company A would have the same date
on each row )
Column C: Franchise Name
Column D: Date franchise opened.
Column E: Col D - Col B

If there were a 1000 rows, 500 of which were for company A. I would like to
find the amount of time it took for 50% (the first 50 sites for this example)
of the franchises to open. I am thinking it has to order the dates in column
D, then find the franchise that was 50th and then take column E as the
answer.

I get that it is an array, but get stuck after that.

Ideas.

Pal,

=INDEX(E:E,SUMPRODUCT((D1:D20000=(LARGE(IF(A1:A200 00="Company
A",D1:D20000),COUNTIF(A1:A20000,"Company A")/2)))*(A1:A20000="Company
A")*ROW(A1:A20000)))

Array entered using Ctrl-Shift-Enter. "Company A" can also be a cell
reference, in case you want to make a table - use advanced filtering to
extract the unique list from your column of company names.

HTH,
Bernie
MS Excel MVP






"PAL" wrote in message
...
I have a spreadsheet with many rows (1000s) and columns. I am trying to
determine how long it took for a percentage of franchises to open. Lets
say
I have:

Column A: Company Name
Column B: Date when product was ready (ie Company A would have the same
date
on each row )
Column C: Franchise Name
Column D: Date franchise opened.
Column E: Col D - Col B

If there were a 1000 rows, 500 of which were for company A. I would like
to
find the amount of time it took for 50% (the first 50 sites for this
example)
of the franchises to open. I am thinking it has to order the dates in
column
D, then find the franchise that was 50th and then take column E as the
answer.

I get that it is an array, but get stuck after that.

Ideas.

Also, I should have noted that I have assumed that the dates are unique in
column D for any one company - i.e., they did not open multiple franchises
on the same day.

Bernie

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Pal,

=INDEX(E:E,SUMPRODUCT((D1:D20000=(LARGE(IF(A1:A200 00="Company
A",D1:D20000),COUNTIF(A1:A20000,"Company A")/2)))*(A1:A20000="Company
A")*ROW(A1:A20000)))

Array entered using Ctrl-Shift-Enter. "Company A" can also be a cell
reference, in case you want to make a table - use advanced filtering to
extract the unique list from your column of company names.

HTH,
Bernie
MS Excel MVP






"PAL" wrote in message
...
I have a spreadsheet with many rows (1000s) and columns. I am trying to
determine how long it took for a percentage of franchises to open. Lets
say
I have:

Column A: Company Name
Column B: Date when product was ready (ie Company A would have the same
date
on each row )
Column C: Franchise Name
Column D: Date franchise opened.
Column E: Col D - Col B

If there were a 1000 rows, 500 of which were for company A. I would like
to
find the amount of time it took for 50% (the first 50 sites for this
example)
of the franchises to open. I am thinking it has to order the dates in
column
D, then find the franchise that was 50th and then take column E as the
answer.

I get that it is an array, but get stuck after that.

Ideas.

This is great. Thank you. A few points to fine tune....

I changed "large" to "small" in order for it to start from the smallest
(10th, 25th percentile....).

Also,

1) multiple franchises can be opened on the same day.
2) it is possible that product ready, col B, may be blank. Anyway to force
a blank instead of #"Val"
3) if the number of franchise is "1" we get the "#NUM!". Anway to force a
blank.
4) I also noticed if the number of franchises is "2" or "3". That while it
calucates a 50th or 80th percentile it gives the "#NUM!" for the 25th
percentile.

Any thoughts to clean this up is appreciated, but regardless thanks much.

"Bernie Deitrick" wrote:

Also, I should have noted that I have assumed that the dates are unique in
column D for any one company - i.e., they did not open multiple franchises
on the same day.

Bernie

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Pal,

=INDEX(E:E,SUMPRODUCT((D1:D20000=(LARGE(IF(A1:A200 00="Company
A",D1:D20000),COUNTIF(A1:A20000,"Company A")/2)))*(A1:A20000="Company
A")*ROW(A1:A20000)))

Array entered using Ctrl-Shift-Enter. "Company A" can also be a cell
reference, in case you want to make a table - use advanced filtering to
extract the unique list from your column of company names.

HTH,
Bernie
MS Excel MVP






"PAL" wrote in message
...
I have a spreadsheet with many rows (1000s) and columns. I am trying to
determine how long it took for a percentage of franchises to open. Lets
say
I have:

Column A: Company Name
Column B: Date when product was ready (ie Company A would have the same
date
on each row )
Column C: Franchise Name
Column D: Date franchise opened.
Column E: Col D - Col B

If there were a 1000 rows, 500 of which were for company A. I would like
to
find the amount of time it took for 50% (the first 50 sites for this
example)
of the franchises to open. I am thinking it has to order the dates in
column
D, then find the franchise that was 50th and then take column E as the
answer.

I get that it is an array, but get stuck after that.

Ideas.


.

See my comments in-line....


This is great. Thank you.


You're welcome.

A few points to fine tune....

I changed "large" to "small" in order for it to start from the smallest
(10th, 25th percentile....).

Also,

1) multiple franchises can be opened on the same day.

On second thought, this really shouldn't matter, since the value in column E
should be the same.


2) it is possible that product ready, col B, may be blank. Anyway to
force
a blank instead of #"Val"

We weren't using column B andywhere... perhaps wrap the formula in

=IF(B2=""","",LongFormula)

3) if the number of franchise is "1" we get the "#NUM!". Anway to force a
blank.

=IF(COUNTIF(A1:A20000,"Company A")=1,"",IF(B2=""","",LongFormula))
or
=IF(COUNTIF(A1:A20000,"Company A")=1,E2,IF(B2=""","",LongFormula))


4) I also noticed if the number of franchises is "2" or "3". That while it
calucates a 50th or 80th percentile it gives the "#NUM!" for the 25th
percentile.

Use the same technique, along the lines of

=IF(COUNTIF(A1:A20000,"Company A")<3,"Something other than 25th
percentile",Rest of the formula here)

HTH,
Bernie
MS Excel MVP

Lots going on in this one. If I focus in on #2 only, I get the blank I am
looking for, but when it is not blank it returns a "false".

=IF(COUNTIF(A1:A20000,P2)=1,"",IF(B2=""",""",INDEX (H:H,SUMPRODUCT(($F$2:$F$1045=(SMALL(IF($A$2:$A$10 45=P2,$F$2:$F$1045),COUNTIF($A$2:$A$1045,P2)/4)))*($A$2:$A$1045=P2)*ROW($A$2:$A$1045)))))

"Bernie Deitrick" wrote:

See my comments in-line....


This is great. Thank you.


You're welcome.

A few points to fine tune....

I changed "large" to "small" in order for it to start from the smallest
(10th, 25th percentile....).

Also,

1) multiple franchises can be opened on the same day.

On second thought, this really shouldn't matter, since the value in column E
should be the same.


2) it is possible that product ready, col B, may be blank. Anyway to
force
a blank instead of #"Val"

We weren't using column B andywhere... perhaps wrap the formula in

=IF(B2=""","",LongFormula)

3) if the number of franchise is "1" we get the "#NUM!". Anway to force a
blank.

=IF(COUNTIF(A1:A20000,"Company A")=1,"",IF(B2=""","",LongFormula))
or
=IF(COUNTIF(A1:A20000,"Company A")=1,E2,IF(B2=""","",LongFormula))


4) I also noticed if the number of franchises is "2" or "3". That while it
calucates a 50th or 80th percentile it gives the "#NUM!" for the 25th
percentile.

Use the same technique, along the lines of

=IF(COUNTIF(A1:A20000,"Company A")<3,"Something other than 25th
percentile",Rest of the formula here)

HTH,
Bernie
MS Excel MVP


.