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="C at")),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.

--
RyGuy

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

"ryguy7272" wrote:

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="C at")),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.

--
RyGuy

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€¦

--
RyGuy


"bj" wrote:

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

"ryguy7272" wrote:

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="C at")),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.

--
RyGuy

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" wrote:

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€¦

--
RyGuy


"bj" wrote:

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

"ryguy7272" wrote:

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="C at")),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.

--
RyGuy

You hit the nail on the head, or is it head on the nail. Now, theres 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 its late now and after a long day Im
just too wiped out to think anymore.

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

--
RyGuy


"bj" wrote:

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" wrote:

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€¦

--
RyGuy


"bj" wrote:

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

"ryguy7272" wrote:

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="C at")),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.

--
RyGuy