Sumproduct and Percentile

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ryguy7272 - 11 Jun 2007 21:15 GMT

I am trying to combine . Does anyone know if this can be done? I got a
result with the following:
=PERCENTILE(SUMPRODUCT((C1:C10="Green")+(E1:E10="Cat")),0.9)

=SUMPRODUCT(--(C1:C10="Green"<PERCENTILE(C1:C10,0.5)),--(E1:E10="Cat"<PERCENTILE(E1:E10,0.95)))

=PERCENTILE(SUMPRODUCT((C1:C10=12)+(E1:E10=75)),0.9)

Initially I thought, ok, maybe I can't calculate a percentile on text, but I
still thought Excel would calcualte the percentile of the matching values.
Maybe it requires Percentile & Index & Match? Anyway, I tried a few things
and I'm not getting the results I expected... Maybe this can't be done...
Can anyone confirm, comment, etc.

Signature

RyGuy

Reply to this Message

bj - 11 Jun 2007 21:24 GMT

What is you are trying to do?
I can not tell from your equations

> I am trying to combine . Does anyone know if this can be done? I got a
> result with the following:
[quoted text clipped - 9 lines]
> and I'm not getting the results I expected... Maybe this can't be done...
> Can anyone confirm, comment, etc.

Reply to this Message

ryguy7272 - 12 Jun 2007 20:55 GMT

Thanks for responding bj. Basically, I was trying to calculate only the
Percentiles of the values that matched the criteria of the Sumproduct. Does
it make sense? Maybe this will not work…

Signature

RyGuy

> What is you are trying to do?
> I can not tell from your equations
[quoted text clipped - 12 lines]
> > and I'm not getting the results I expected... Maybe this can't be done...
> > Can anyone confirm, comment, etc.

Reply to this Message

bj - 12 Jun 2007 22:33 GMT

it may be a semantic issue
the percentile function calculates a value which would correlate to a
percentage of the data set. the data set must be totally numeric
for example percentile({1,2,3,4},0.3) would be 1.9
I think You want what I would call just the percent value
if there are 20 items and 4 meet a requirement the the percent which meet
the requirement of the total is 20 %.
for example if you want the percent of when both column A = Green and column
C = Cat for the first 20 rows you could use
=sumproduct(--(A1:A20="Green"),--(C1:C20="Cat"))/20
and format as %
if you want the percent of when column A = Green or column C = Cat for the
first 20 rows you could use
=sumproduct(--or(A1:A20="Green",C1:C20="Cat"))/20

If you are trying to get the percentage, it is different from percentile

> Thanks for responding bj. Basically, I was trying to calculate only the
> Percentiles of the values that matched the criteria of the Sumproduct. Does
[quoted text clipped - 16 lines]
> > > and I'm not getting the results I expected... Maybe this can't be done...
> > > Can anyone confirm, comment, etc.

Reply to this Message

ryguy7272 - 13 Jun 2007 05:24 GMT

You hit the nail on the head, or is it head on the nail. Now, there’s some
semantic issues for ya... Thanks so much bj! That was pretty much what I
was after!!

I took your idea to the next level and ended up using the following:
=SUMPRODUCT(--(A1:A20="Green")--(B1:B20="Cat"))/(SUMPRODUCT(--(A1:B20<>""))+COUNTBLANK(A1:B20))

I used 2 Green(s) and 3 Cat(s) for a total of 5 elements in the array from
A1:B20. The above-listed function returned .125, which is exactly what 5/40
yields.

Just for information purposes, I used SUMPRODUCT(--(A1:B20<>"") to count the
non-blank spaces and COUNTBLANK(A1:B20) to count the blanks. There may be a
more eloquent way of doing this, but it’s late now and after a long day I’m
just too wiped out to think anymore.

Thanks again bj; I wonder what people did before there were discussion
groups such as these…

Signature

RyGuy

> it may be a semantic issue
> the percentile function calculates a value which would correlate to a
[quoted text clipped - 33 lines]
> > > > and I'm not getting the results I expected... Maybe this can't be done...
> > > > Can anyone confirm, comment, etc.

Reply to this Message