Conditional Percentiles

[DemaBM]

I am working with garments (shirts, pants, etc) and I need to calculate certain percentiles on the demand data for each size of the garments.

My setup is as follows:

Column A = Size
Column B = Demand

The percentile formula is straightforward: PERCENTILE (array, k)
where k=percentile (0.95, etc)

However, I only want the percentile of the Demand values for size 2, 4, 6, 8, etc. Ideally, it would work like the SUMIF function:

PERCENTILEIF(Range, Criteria , PercRange)

I really need this help because Access is a monster to even calculate a simple percentile!

Thanks!

 

[PA HS Teacher]

Try the following

=Percentile(If(SizeRange=2,DemandRange,""),k)

confirmed with control + shift + enter

 

[DemaBM]

Thanks + Followup Question

When you say "Confirm With", is it possible to drag the formula?

The formula worked for the first entry in the table, but when I dragged it, I got all zeros. If I need to go in and followup with Ctrl-Shift-Enter, that is fine, but if there is an easier way to do this for all the cells at once, that would be great!

Thanks again!

 

[PA HS Teacher]

Make sure you are using absolute reference, and not relative.

use: $A$1:$A$10

not: A1:A10

Then it should still work.

(and yes you can drag the formula)