randomwalker
Board Regular
- Joined
- Feb 22, 2007
- Messages
- 169
Hi,
cell c1 = aaa, c2=aa, c3=aaa, c4=a, c5=aa, ..., c18=aa.
d1 = 20, d2=83, d3=60, ...
Now I want to find out the mean of some numbers in column D. Those numbers are higher than the 10% percentile and lower than the 90% percentile, meanwhile the level in the corresponding cells in column c is "aaa". my formula is {=AVERAGE(IF($C$1:$C$18="aaa",IF($D$1:$D$18>PERCENTILE($D$1:$D$18,10%),IF($D$1:$D$18<PERCENTILE($D$1:$D$18,90%),$D$1:$D$18))))}
but it doesn't work correctly. the formula above simply gives the average of those numbers in column d with "aaa" in column c, it overlooks the 10% percentile and 90% percentile function.
where am I wrong?
thanks for help!
cell c1 = aaa, c2=aa, c3=aaa, c4=a, c5=aa, ..., c18=aa.
d1 = 20, d2=83, d3=60, ...
Now I want to find out the mean of some numbers in column D. Those numbers are higher than the 10% percentile and lower than the 90% percentile, meanwhile the level in the corresponding cells in column c is "aaa". my formula is {=AVERAGE(IF($C$1:$C$18="aaa",IF($D$1:$D$18>PERCENTILE($D$1:$D$18,10%),IF($D$1:$D$18<PERCENTILE($D$1:$D$18,90%),$D$1:$D$18))))}
but it doesn't work correctly. the formula above simply gives the average of those numbers in column d with "aaa" in column c, it overlooks the 10% percentile and 90% percentile function.
where am I wrong?
thanks for help!