help using OFFSET within IF

[Special K]

I am calculating a conditional MEDIAN of column C based on the value of column A. The following works:

{=MEDIAN(IF(A2:A60=4770,C2:C60))}

But when I generalize the formula to include all rows except the first using OFFSET, I get a #VALUE! error:

{=MEDIAN(IF(OFFSET(A2,0,0,ROWS(A:A)-ROW(A2)+1)=4770,OFFSET(C2,0,0,ROWS(C:C)-ROW(C2)+1)))}

Am I not using OFFSET correctly? The OFFSET formulas don't return errors when I input them individually into cells.

 

[awoohaw]

I am not a fan of OFFSET because of it's volatility, but that probably is not an issue with your formulas. But, Using INDEX to build references may work for you:

Mr excel questions 68.xlsm
	A	B	C	D	E	F
1
2	8107		13891		11953	11953
3	6804		6420
4	4770		5793
5	19524		577
6	5074		5996
7	11508		17770
8	4770		4106
9	12639		2776
10	16656		1378
11	9543		4618
12	4770		16855
13	4770		15788
14	17498		16823
15	14460		1476
16	4770		18923
17	17530		3190
18	19035		9742
19	7342		17483
20	15417		17617
21	4770		11953
22	18851		1829
23	12268		12003
24	4770		4404
25	4770		7514
26	4770		7747
27	5424		17390
28	4770		1300
29	5974		19475
30	4770		13477
31	4770		13616
32	4770		15539
33	8482		18371
34	10384		17067
35	17862		18703
36	15534		7841
37	11179		13438
38	16179		5121
39	19127		17868
40	7704		2004
41	608		6849
42	19960		14335
43	1229		6921
44	18253		11347
45	4806		14046
46	15912		12999
47	5740		12563
48	5584		19587
49	4032		9287
50	6883		8481
51	10974		3609
52	18098		1946
53	19226		16522
54	10066		3241
55	16812		4437
56	12551		1018
57	587		19085
58	4976		19653
59	6999		19302
60	8307		5836
61
Sheet2

Cell Formulas
Range	Formula
E2	E2	=MEDIAN(IF(A2:A60=4770,C2:C60))
F2	F2	=MEDIAN(IF(INDEX(A:A,2,1):INDEX(A:A,ROWS(A:A),1)=4770, INDEX(C:C,2,1):INDEX(C:C,ROWS(C:C),1)))

 

[Tetra201]

... Am I not using OFFSET correctly?..

You are using OFFSET correctly; however, it does not like having the ROW() function in the formula.

You can try using a work-around -- for example, CSE

=MEDIAN(IF(OFFSET(A2,0,0,X1)=4770,OFFSET(C2,0,0,X1)))

where cell X1 contains =ROWS(A:A)-ROW(A2)+1,

Or just switch to the INDEX-based solution offered by awoohaw.