Formula for "Median IF" and "Trimmean IF&quot

[ncody]

I have an excel worksheet (Results) that has a rating (in column F), and core deposits/total assets ratio in (column N). I am trying to create a summary worksheet that will provide the following:

1 rating median core deposits/total assets ratio
2 rating median core deposits/total assets ratio
3 rating merdian core deposits/total assets ratio
4 rating median core deposits/total assets ratio
5 rating median core deposits/total assets ratio

I tried the following formula for the 1 rated for median and it didn't work:

=MEDIAN(IF(Results!F2:F2000=1,L2:L2000))

I tried the following formula for the 1 rated for trimmean and it also didn't work:

=Trimmean(IF(Results!F2:F2000,L2:L2000),.1)

Any suggestions for how to write the formula so that it works?

 

[Aladin Akyurek]

Try to provide 5 rows from the F-range and 5-row from L-range along with the expected results.

 

[ncody]

Here are 30 rows from the data. Column B is the rating and Column C are the ratios I want to use to calculate median and trimmean.

2Problem Formula Report_9120051428394839731.xls
	A	B	C	D
1	Sequence #	COMM`L LIQ RT (0P)	CORE DEPS / TOT ASSETS (2003Q4)
2	1	1	57.11
3	2	1	23.86
4	3	1	57.30
5	4	1	77.22
6	5	1	70.96
7	6	1	80.51
8	7	2	54.32
9	8	2	61.82
10	9	2	88.42
11	10	1	83.49
12	11	2	54.68
13	12	2	72.07
14	13	2	81.70
15	14	2	81.75
16	15	2	72.76
17	16	1	63.15
18	17	2	60.27
19	18	2	83.27
20	19	1	72.84
21	20	1	82.69
22	21	2	69.80
23	22	1	82.03
24	23	1	67.11
25	24	1	42.40
26	25	1	76.80
27	26	1	78.56
28	27	1	75.06
29	28	1	63.93
30	29	1	52.21
Results

I would expect median for the 1-rated to be 71.90. I would expect trimmean for the 1-rated using .1 to be 67.07.

I would expect median for the 2-rated to be 72.07. I would expect trimmean for the 2-rated using .1 to be 70.99.

 

[Aladin Akyurek]

Here are 30 rows from the data. Column B is the rating and Column C are the ratios I want to use to calculate median and trimmean.

...

I would expect median for the 1-rated to be 71.90. I would expect trimmean for the 1-rated using .1 to be 67.07.

I would expect median for the 2-rated to be 72.07. I would expect trimmean for the 2-rated using .1 to be 70.99.

Book7
	A	B	C	D	E	F	G	H	I	J
1	Sequence #	COMM`L LIQ RT (0P)	CORE DEPS / TOT ASSETS (2003Q4)					median	trimmean
2	1	1	57.11				1	71.9	67.06833
3	2	1	23.86				2	72.07	70.98727
4	3	1	57.3
5	4	1	77.22
6	5	1	70.96
7	6	1	80.51
8	7	2	54.32
9	8	2	61.82
10	9	2	88.42
Sheet1

H2:

=MEDIAN(IF($B$2:$B$30=G2,$C$2:$C$30))

which is confirmed with control+shift+enter then copied down.

I2:

=TRIMMEAN(IF($B$2:$B$30=G2,$C$2:$C$30),0.1)

which is confirmed with control+shift+enter then copied down.

 

[ncody]

Thanks so much - it worked. I guess pointing to a cell for the rating values works whereas entering the rating values doesn't work.