restate: 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!

 

[konew1]

Your formula works for me using xl10. I can't see any reason why it doesn't work for you

Excel Workbook
	C	D	E
2	aaa	2
3	w	3
4	e	4
5	r	5
6	aaa	6
7	q	2
8	w	3
9	aaa	3
10	aaa	5
11	aaa	12
12	s	7
13
14			4.666667
15
16			2
17			7
Sheet3

 

[Cindy Ellis]

Confirmed using Excel 2007 as well. Are you sure that there are values for aaa that are outside the 10th to 90th percentile boundaries?

 

[randomwalker]

Hi konew1

what I want to get is the average of the numbers between 10% of those "aaa" numbers and 90% of those "aaa" numbers. Use your data set as an example, the 10% percentile of those "aaa" number = 2.4, the 90% percentile of those "aaa" numbers = 9.6. That's to say I want to get the average of the numbers larger than 2.4 but lower than 9.6. In your dataset, those numbers are 3,6,5 and the average = 4.6667.

but if change the "aaa" numbers to 12, 24, 3, 5, 18, you'll find the result is 8.50 if use the formula and the result is 11.667 if you calculate manually.
the 10% of the "aaa" numbers = 3.8; the 90% of the "aaa" numbers = 21.6, therefore the numbers that I am looking for are 12, 5, 18 and the mean of these numbers is 11.667.

seems there is something wrong in the formula, any idea?

thanks

 

[Cindy Ellis]

So you want to use the percentile just based on the numbers coded aaa? Definitely more challenging...working on it now.

 

[Aladin Akyurek]

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!

Hi konew1

what I want to get is the average of the numbers between 10% of those "aaa" numbers and 90% of those "aaa" numbers. Use your data set as an example, the 10% percentile of those "aaa" number = 2.4, the 90% percentile of those "aaa" numbers = 9.6. That's to say I want to get the average of the numbers larger than 2.4 but lower than 9.6. In your dataset, those numbers are 3,6,5 and the average = 4.6667.

but if change the "aaa" numbers to 12, 24, 3, 5, 18, you'll find the result is 8.50 if use the formula and the result is 11.667 if you calculate manually.
the 10% of the "aaa" numbers = 3.8; the 90% of the "aaa" numbers = 21.6, therefore the numbers that I am looking for are 12, 5, 18 and the mean of these numbers is 11.667.

seems there is something wrong in the formula, any idea?

thanks

So you want to use the percentile just based on the numbers coded aaa? Definitely more challenging...working on it now.

Control+shift+enter, not just enter:

=AVERAGE(IF($C$2:$C$12="aaa",
  IF($D$2:$D$12>PERCENTILE(IF($C$2:$C$12="aaa",$D$2:$D$12),10%),
  IF($D$2:$D$12 < PERCENTILE(IF($C$2:$C$12="aaa",$D$2:$D$12),90%),
  $D$2:$D$12))))

</percentile($D$1:$d$18,90%),$d$1:$d$18))))}.

 

[randomwalker]

Thank you very much, Aladin Akyurek. the formula given by you works!

 

[Aladin Akyurek]

Thank you very much, Aladin Akyurek. the formula given by you works!

You are welcome.