View Single Post
  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick Bernie Deitrick is offline
external usenet poster
 
Posts: 5,441
Default 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.


.



.


.