Using variables to determine a range for an average.

Originally Posted by tigertiger

I think: don't need VBA, using only formula

at cell C2 (sheet1), enter the formula
=IF(ROW()-1<Sheet2!$B$1+Sheet2!$B$2,"",AVERAGE(INDIRECT("B"&(ROW()-Sheet2!$B$1+1)&":B"&ROW())))
and fill-copy for C3:C.... (... end of column C corresponding with column B)

Actually, it seems like it is working backwards. Let's use the values on Sheet2, B1=5 and B2=10.

The formula in Sheet1, Cell C16 should be =Average(B2:B6) but it is actually this =Average(B7:B15)

Can you fix that? I am too much of a newbie to see how to change it.

Thanks!

Originally Posted by blastronaut

Actually, it seems like it is working backwards. Let's use the values on Sheet2, B1=5 and B2=10.

The formula in Sheet1, Cell C16 should be =Average(B2:B6) but it is actually this =Average(B7:B15)

Can you fix that? I am too much of a newbie to see how to change it.

Thanks!

no It ACTUALLY is =Average(B12:B16)

because B1 is range to average, B2 is ... as you said at above post

what is about C17,C18,.....

Originally Posted by tigertiger

If you like that, you can use the following formula for cell C2:
=IF(ROW()-1<Sheet2!$B$1+Sheet2!$B$2,"",AVERAGE(INDIRECT("B"&(ROW()-Sheet2!$B$1+1-Sheet2!$B$2)&":B"&(ROW()-Sheet2!$B$2))))

and then fill-Copy for C3,C4,......

IF YOU want to know the range in D2 you paste the formula, and then file D3,D4,..... =>> it will show the range to caculate AVERAGE
=IF(ROW()-1<Sheet2!$B$1+Sheet2!$B$2,""," Average of B"&(ROW()-Sheet2!$B$1+1-Sheet2!$B$2)&":B"&(ROW()-Sheet2!$B$2) )

is that true: ?

Perfect! Thank you so much!