How long for the first group
PAL,
It was that there are multiple rows returned by the sumproduct. You can
array enter:
=IF(Work!$B$2:$B$23="","",IF(COUNTIF(Work!$A$2:$A$ 23,A4)<=5,"",INDEX(Work!D:D,SUMPRODUCT(MAX((Work!$ C$2:$C$23=(SMALL(IF(Work!$A$2:$A$23=A4,Work!$C$2:$ C$23),COUNTIF(Work!$A$2:$A$23,A4)/2)))*(Work!$A$2:$A$23=A4)*ROW(Work!$A$2:$A$23))))) )
The sumproduct in this will return the last entry that meets the criteria
(the MAX that I added), but since you are looking at identical numbers, it
doesn't really matter.
I will send you the fixed workbook example.
HTH,
Bernie
MS Exel MVP
"PAL" wrote in message
...
Sent
"Bernie Deitrick" wrote:
PAL,
deitbe at consumer dot org
Bernie
"PAL" wrote in message
...
Yes, I can send. Trying to remove the noise and the company name from
properties. Where to post? Thanks. My guess is your assumption is
correct
"Bernie Deitrick" wrote:
PAL,
I'm not sure why you are getting a zero - perhaps you have duplicates,
and the SUMPRODUCT is
actually summing for two or more rather than just finding one (that is
one drawback to SUMPRODUCT).
Can you email me a book or sheet that shows the problem?
HTH,
Bernie
MS Excel MVP
"PAL" wrote in message
...
Same thing with the shortened data set....
249,336, 370,341,336,341,461,341,360,552,579,579
If I pull the numbers out and do straight percentile I get 350, 578.
ie =PERCENTILE(range,0.50) ETC...which is what I would expect.
"PAL" wrote:
Sorry for the delay. Got pulled away, but was probably good to get
away.
Not sure what I did last time as it was the wrong data. I need
back
track
to your previous suggestion and recreate.
Within the big spreadsheet, for 50th, I get 0. For 90th, I get
552.
"Bernie Deitrick" wrote:
What results would you expect? With those numbers I get 244 as
the
50%ile and 455 as the
90%ile.
But if I change
COUNTIF(Work!$A$2:$A$2000,A33)/1.11
to
ROUND(COUNTIF(Work!$A$2:$A$2000,A33)/1.11,0)
then I get 482 as the 90%ile.
HTH,
Bernie
MS Excel MVP
"PAL" wrote in message
...
Near as I can tell its a stats problem. I have done it two
ways,
removed all
the data with the exception of the data generating these
numbers
or I kept it
all in. The 90th was working originally.
The numbers in the data set are
152,239,239,244,244,244,263,273,364,455,482,482
I can get the 50th and 90th percentile if I delete 3 of the
numbers - 482,
244, 244. Obviously the dups are messing it up. If I remove 2
x
244 it
works. Obviously, I can't control the data so it seems like I
have a
problem.
Not sure where to go from here.
.
.
.
|