# Dynamic range reference for functions like AVERAGE

#### jawnail

##### New Member
Hello All,

OS: W7 SP1
Excel 2010

#VALUE!
Excel 2010
Cell Formulas
RangeFormula
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)

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Take a look at INDIRECT

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
QRSTUVWXY
1locationshapeRTCondCntRungCntMeanSDTrim1
21075012743126.8996750
31072623743126.8996726
41086934743126.8996869
51054145743126.8996
61082956743126.8996829
71174717696.333374.33259747
81161128696.333374.33259
91173139696.333374.33259731
1020595110676149.401595
1120710211676149.401710
1220529312676149.401529
1320870413676149.401
1421537114630.6131.5344537
Sheet1
Excel 2010
Cell Formulas
RangeFormula
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)

Replies
6
Views
140
Replies
1
Views
127
Replies
11
Views
463
Replies
1
Views
132
Replies
3
Views
135

1,202,966
Messages
6,052,839
Members
444,603
Latest member
dustinjmangum

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back