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