find out the average number

[randomwalker]

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!

 

[BeingYogi]

is this correct


{=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))))}

 

[VoG]

Duplicate http://www.mrexcel.com/forum/excel-questions/705037-restate-find-out-average-number.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: Forum Rules).