Dynamic range reference for functions like AVERAGE

[jawnail]

Hello All,

OS: W7 SP1
Excel 2010

#VALUE!

Excel 2010

Cell Formulas
Range	Formula
D2	=AVERAGE(C2:C6)
D3	=D2
D4	=D3
D5	=D4
D6	=D5
D7	=AVERAGE(C7:C9)
D8	=D7
D9	=D8
D10	=AVERAGE(C10:C13)
D11	=D10
D12	=D11
D13	=D12
D14	=AVERAGE(C14:C18)
D15	=D14
D16	=D15
D17	=D16
D18	=D17
D19	=AVERAGE(C19:C24)
D20	=D19
D21	=D20
D22	=D21
D23	=D22
D24	=D23
D25	=AVERAGE(C25:C29)
D26	=D25
D27	=D26
D28	=D27
D29	=D28
E2	=STDEV(C2:C6)
E3	=E2
E4	=E3
E5	=E4
E6	=E5
E7	=STDEV(C7:C9)
E8	=E7
E9	=E8
E10	=STDEV(C10:C13)
E11	=E10
E12	=E11
E13	=E12
E14	=STDEV(C14:C18)
E15	=E14
E16	=E15
E17	=E16
E18	=E17
E19	=STDEV(C19:C24)
E20	=E19
E21	=E20
E22	=E21
E23	=E22
E24	=E23
E25	=STDEV(C25:C29)
E26	=E25
E27	=E26
E28	=E27
E29	=E28
F2	=IF(OR(C2<(D2-(H$1*E2)),C2>(D2+(H$1*E2))),"",C2)
F3	=IF(OR(C3<(D3-(H$1*E3)),C3>(D3+(H$1*E3))),"",C3)
F4	=IF(OR(C4<(D4-(H$1*E4)),C4>(D4+(H$1*E4))),"",C4)
F5	=IF(OR(C5<(D5-(H$1*E5)),C5>(D5+(H$1*E5))),"",C5)
F6	=IF(OR(C6<(D6-(H$1*E6)),C6>(D6+(H$1*E6))),"",C6)
F7	=IF(OR(C7<(D7-(H$1*E7)),C7>(D7+(H$1*E7))),"",C7)
F8	=IF(OR(C8<(D8-(H$1*E8)),C8>(D8+(H$1*E8))),"",C8)
F9	=IF(OR(C9<(D9-(H$1*E9)),C9>(D9+(H$1*E9))),"",C9)
F10	=IF(OR(C10<(D10-(H$1*E10)),C10>(D10+(H$1*E10))),"",C10)
F11	=IF(OR(C11<(D11-(H$1*E11)),C11>(D11+(H$1*E11))),"",C11)
F12	=IF(OR(C12<(D12-(H$1*E12)),C12>(D12+(H$1*E12))),"",C12)
F13	=IF(OR(C13<(D13-(H$1*E13)),C13>(D13+(H$1*E13))),"",C13)
F14	=IF(OR(C14<(D14-(H$1*E14)),C14>(D14+(H$1*E14))),"",C14)
F15	=IF(OR(C15<(D15-(H$1*E15)),C15>(D15+(H$1*E15))),"",C15)
F16	=IF(OR(C16<(D16-(H$1*E16)),C16>(D16+(H$1*E16))),"",C16)
F17	=IF(OR(C17<(D17-(H$1*E17)),C17>(D17+(H$1*E17))),"",C17)
F18	=IF(OR(C18<(D18-(H$1*E18)),C18>(D18+(H$1*E18))),"",C18)
F19	=IF(OR(C19<(D19-(H$1*E19)),C19>(D19+(H$1*E19))),"",C19)
F20	=IF(OR(C20<(D20-(H$1*E20)),C20>(D20+(H$1*E20))),"",C20)
F21	=IF(OR(C21<(D21-(H$1*E21)),C21>(D21+(H$1*E21))),"",C21)
F22	=IF(OR(C22<(D22-(H$1*E22)),C22>(D22+(H$1*E22))),"",C22)
F23	=IF(OR(C23<(D23-(H$1*E23)),C23>(D23+(H$1*E23))),"",C23)
F24	=IF(OR(C24<(D24-(H$1*E24)),C24>(D24+(H$1*E24))),"",C24)
F25	=IF(OR(C25<(D25-(H$1*E25)),C25>(D25+(H$1*E25))),"",C25)
F26	=IF(OR(C26<(D26-(H$1*E26)),C26>(D26+(H$1*E26))),"",C26)
F27	=IF(OR(C27<(D27-(H$1*E27)),C27>(D27+(H$1*E27))),"",C27)
F28	=IF(OR(C28<(D28-(H$1*E28)),C28>(D28+(H$1*E28))),"",C28)
F29	=IF(OR(C29<(D29-(H$1*E29)),C29>(D29+(H$1*E29))),"",C29)
G2	=IF(B2=B1,0,1)
G3	=IF(B3=B2,0,1)
G4	=IF(B4=B3,0,1)
G5	=IF(B5=B4,0,1)
G6	=IF(B6=B5,0,1)
G7	=IF(B7=B6,0,1)
G8	=IF(B8=B7,0,1)
G9	=IF(B9=B8,0,1)
G10	=IF(B10=B9,0,1)
G11	=IF(B11=B10,0,1)
G12	=IF(B12=B11,0,1)
G13	=IF(B13=B12,0,1)
G14	=IF(B14=B13,0,1)
G15	=IF(B15=B14,0,1)
G16	=IF(B16=B15,0,1)
G17	=IF(B17=B16,0,1)
G18	=IF(B18=B17,0,1)
G19	=IF(B19=B18,0,1)
G20	=IF(B20=B19,0,1)
G21	=IF(B21=B20,0,1)
G22	=IF(B22=B21,0,1)
G23	=IF(B23=B22,0,1)
G24	=IF(B24=B23,0,1)
G25	=IF(B25=B24,0,1)
G26	=IF(B26=B25,0,1)
G27	=IF(B27=B26,0,1)
G28	=IF(B28=B27,0,1)
G29	=IF(B29=B28,0,1)
G30	=IF(B30=B29,0,1)

 

[VoG]

Take a look at INDIRECT

 

[jawnail]

Thanks VoG! INDIRECT works great! This will be a real time and effort saver. I've included how I used it for anyone else needing an example.

I created two counters, CondCnt and RungCnt, to keep track of how many rows in a given condition and how many total so far. In the Mean column I then 'feel' my way down the shape column until the value changes then I use INDIRECT nested in AVERAGE to reference the current row number from RungCnt as the end of the range and the current row number minus the value in CondCnt - 1.

Excel Workbook
	Q	R	S	T	U	V	W	X	Y
1	location	shape	RT	CondCnt	RungCnt	Mean	SD	Trim	1
2	1	0	750	1	2	743	126.8996	750
3	1	0	726	2	3	743	126.8996	726
4	1	0	869	3	4	743	126.8996	869
5	1	0	541	4	5	743	126.8996
6	1	0	829	5	6	743	126.8996	829
7	1	1	747	1	7	696.3333	74.33259	747
8	1	1	611	2	8	696.3333	74.33259
9	1	1	731	3	9	696.3333	74.33259	731
10	2	0	595	1	10	676	149.401	595
11	2	0	710	2	11	676	149.401	710
12	2	0	529	3	12	676	149.401	529
13	2	0	870	4	13	676	149.401
14	2	1	537	1	14	630.6	131.5344	537
Sheet1

Excel 2010

Cell Formulas
Range	Formula
V2	=IF(R2<>R3,AVERAGE(INDIRECT("S"&U2-T1&":S"&U2)),V3)
V3	=IF(R3<>R4,AVERAGE(INDIRECT("S"&U3-T2&":S"&U3)),V4)
V4	=IF(R4<>R5,AVERAGE(INDIRECT("S"&U4-T3&":S"&U4)),V5)
V5	=IF(R5<>R6,AVERAGE(INDIRECT("S"&U5-T4&":S"&U5)),V6)
V6	=IF(R6<>R7,AVERAGE(INDIRECT("S"&U6-T5&":S"&U6)),V7)
V7	=IF(R7<>R8,AVERAGE(INDIRECT("S"&U7-T6&":S"&U7)),V8)
V8	=IF(R8<>R9,AVERAGE(INDIRECT("S"&U8-T7&":S"&U8)),V9)
V9	=IF(R9<>R10,AVERAGE(INDIRECT("S"&U9-T8&":S"&U9)),V10)
V10	=IF(R10<>R11,AVERAGE(INDIRECT("S"&U10-T9&":S"&U10)),V11)
V11	=IF(R11<>R12,AVERAGE(INDIRECT("S"&U11-T10&":S"&U11)),V12)
V12	=IF(R12<>R13,AVERAGE(INDIRECT("S"&U12-T11&":S"&U12)),V13)
V13	=IF(R13<>R14,AVERAGE(INDIRECT("S"&U13-T12&":S"&U13)),V14)
V14	=IF(R14<>R15,AVERAGE(INDIRECT("S"&U14-T13&":S"&U14)),V15)
W2	=IF(R2<>R3,STDEV(INDIRECT("S"&U2-T1&":S"&U2)),W3)
W3	=IF(R3<>R4,STDEV(INDIRECT("S"&U3-T2&":S"&U3)),W4)
W4	=IF(R4<>R5,STDEV(INDIRECT("S"&U4-T3&":S"&U4)),W5)
W5	=IF(R5<>R6,STDEV(INDIRECT("S"&U5-T4&":S"&U5)),W6)
W6	=IF(R6<>R7,STDEV(INDIRECT("S"&U6-T5&":S"&U6)),W7)
W7	=IF(R7<>R8,STDEV(INDIRECT("S"&U7-T6&":S"&U7)),W8)
W8	=IF(R8<>R9,STDEV(INDIRECT("S"&U8-T7&":S"&U8)),W9)
W9	=IF(R9<>R10,STDEV(INDIRECT("S"&U9-T8&":S"&U9)),W10)
W10	=IF(R10<>R11,STDEV(INDIRECT("S"&U10-T9&":S"&U10)),W11)
W11	=IF(R11<>R12,STDEV(INDIRECT("S"&U11-T10&":S"&U11)),W12)
W12	=IF(R12<>R13,STDEV(INDIRECT("S"&U12-T11&":S"&U12)),W13)
W13	=IF(R13<>R14,STDEV(INDIRECT("S"&U13-T12&":S"&U13)),W14)
W14	=IF(R14<>R15,STDEV(INDIRECT("S"&U14-T13&":S"&U14)),W15)
X2	=IF(OR(S2<(V2-(Y$1*W2)),S2>(V2+(Y$1*W2))),"",S2)
X3	=IF(OR(S3<(V3-(Y$1*W3)),S3>(V3+(Y$1*W3))),"",S3)
X4	=IF(OR(S4<(V4-(Y$1*W4)),S4>(V4+(Y$1*W4))),"",S4)
X5	=IF(OR(S5<(V5-(Y$1*W5)),S5>(V5+(Y$1*W5))),"",S5)
X6	=IF(OR(S6<(V6-(Y$1*W6)),S6>(V6+(Y$1*W6))),"",S6)
X7	=IF(OR(S7<(V7-(Y$1*W7)),S7>(V7+(Y$1*W7))),"",S7)
X8	=IF(OR(S8<(V8-(Y$1*W8)),S8>(V8+(Y$1*W8))),"",S8)
X9	=IF(OR(S9<(V9-(Y$1*W9)),S9>(V9+(Y$1*W9))),"",S9)
X10	=IF(OR(S10<(V10-(Y$1*W10)),S10>(V10+(Y$1*W10))),"",S10)
X11	=IF(OR(S11<(V11-(Y$1*W11)),S11>(V11+(Y$1*W11))),"",S11)
X12	=IF(OR(S12<(V12-(Y$1*W12)),S12>(V12+(Y$1*W12))),"",S12)
X13	=IF(OR(S13<(V13-(Y$1*W13)),S13>(V13+(Y$1*W13))),"",S13)
X14	=IF(OR(S14<(V14-(Y$1*W14)),S14>(V14+(Y$1*W14))),"",S14)
T2	=IF(R2<>R1,1,T1+1)
T3	=IF(R3<>R2,1,T2+1)
T4	=IF(R4<>R3,1,T3+1)
T5	=IF(R5<>R4,1,T4+1)
T6	=IF(R6<>R5,1,T5+1)
T7	=IF(R7<>R6,1,T6+1)
T8	=IF(R8<>R7,1,T7+1)
T9	=IF(R9<>R8,1,T8+1)
T10	=IF(R10<>R9,1,T9+1)
T11	=IF(R11<>R10,1,T10+1)
T12	=IF(R12<>R11,1,T11+1)
T13	=IF(R13<>R12,1,T12+1)
T14	=IF(R14<>R13,1,T13+1)