Help required with percentages

[Nubian]

Hello.

I have attached an .xls with the formula I require help with.

Tried to explain it as best I can within the spreadsheet. Please let me know if you need further info.

Thanks

[JBeaucaire]

In G5, then copied down:

=IF(E5>=PERCENTILE($E$5:$F$17,0.88),"H", IF(E5>=PERCENTILE($E$5:$F$17,0.6),"M+", IF(E5>=PERCENTILE($E$5:$F$17,0.15),"M", "L")))

In H5, then copied down:

=IF(E5>=PERCENTILE($E$5:$F$17,0.75),"H", IF(E5>=PERCENTILE($E$5:$F$17,0.5),"M+", IF(E5>=PERCENTILE($E$5:$F$17,0.25),"M", "L")))


I can't fathom any functions that equal your values in row 33.

[Nubian]

Thanks for your reply & help.

I was worried that I had not made myself clear. Is it possible to have your formulas index tables A&B, as the percentiles will change and don't want to have to update the formula each time accordingly.

Table C - I was hoping that its possible to update row 33 based on D21:D24
ie (sum in I32) D21 = 2 therefore return a value greater than the 11th highest score in column E and so on

Column I formula to explain further if J5 = 9 return E13 value

Many thanks for your help. If you want further examples or an updated example .xls let me know

I hope I have explained clearly

[shg]

@JB: Apparently not ...

[JBeaucaire]

@SHG: ??


In G5:
=IF($E5>=PERCENTILE($E$5:$F$17,SUM($C$22:$C$24)),"H", IF($E5>=PERCENTILE($E$5:$F$17,SUM($C$23:$C$24)),"M+", IF($E5>=PERCENTILE($E$5:$F$17,$C$24),"M", "L")))

In H5:
=IF($E5>=PERCENTILE($E$5:$F$17,SUM($C$30:$C$32)),"H", IF($E5>=PERCENTILE($E$5:$F$17,SUM($C$31:$C$32)),"M+", IF($E5>=PERCENTILE($E$5:$F$17,$C$32),"M", "L")))

In I5:
=INDEX($E$5:$E$17, J5)

Those 3 copied down.

I33: =PERCENTILE($E$5:$E$17, SUM($C$22:$C$24))
J33: =PERCENTILE($E$5:$E$17, SUM($C$22:$C$23))
K33: =PERCENTILE($E$5:$E$17, SUM($C$22))

[Nubian]

Absolute genius, thank you very much!

Saves me a lot of playing around. tytyty

[shg]

@JB: Redeemed!

[JBeaucaire]

(disco.... moon walk)