Sumproduct and Percentile

[ryguy7272]

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.

 

[bj]

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

 

[ryguy7272]

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…

 

[bj]

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

 

[ryguy7272]

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…