Dynamic range in average formula

**Cicada** · 05-09-2011, 09:21 AM

Hello all,

Please excuse the above title I was at a loss of what to call this one.

I have the following incomplete code...

Please Login or Register  to view this content.

i could finish it but have many more ranges to go and was wondering if there was a shortcut my guess is yes. As you can see in the above code (fairly banal i know) im pulling averages of data from sheet 2 and putting them into what will be a table on sheet 3. I could go through and do them individually but that would be quite painful. Does somone a little more technically minded know of any tricks to expedite the process ie shorten the code. would be handy if it was applicable to other formulas also
thanks for your help

Dan

**Richard Buttrey** · 05-09-2011, 09:50 AM

Hi Dan,

Does the following help

Please Login or Register  to view this content.

Regards

**Cicada** · 05-09-2011, 10:22 AM

Thankyou Richard works a treat, do you mind explaining some of that for me if you have some time so i can duplicate that for different problems in the future instead of bugging you guys. Would be much appreciated. thanks again

Regards
Dan

**Cicada** · 05-09-2011, 10:37 AM

Ah was wondering what that notation was, just found a few sites explaining R1C1 notation will this help in lifting the veil?

Dan

Ps in my original post said notation was automated by recording a macro.

**Cicada** · 05-09-2011, 11:09 AM

Hello

on closer look sorry last time had a brief look it is returning values but not from the ranges that i want, it is several cells out of whack. eg where i want the average from I53:I57 it is instead returning I51:I55 as i don't understand what has been written I can't make the appropriate adjustments.Can someone give me a quick heads up on the choice of numbers in the ranges and the role of * and + in those ranges. If anyone can help on this would be much appreciated

Regards
Dan

**Richard Buttrey** · 05-09-2011, 11:11 AM

Hi Dan,

The Input box prompts for the number of iterations. Your original example used four, but in the real world rather than prompt for a number you'd may be able to use a system variable which identifies the number of iterations from a list of values.

I left the first formula as the RC notation since I didn't seem to fit in with the rest of the example. I generally prefer to use the A1 notation, hence the rest of the code.

The two columns you're using (B & C) are hard coded but again for a generalised solution you may be able to generate those with variables which work out the columns to use.

The x variable is a loop counter and is set by the answer to the input box. In your example the various ranges that you're averaging are 9 rows apart, hence the hard coded reference to '9' in the Excel formula. So for instance in the first iteration where x = 1, the code

Please Login or Register  to view this content.

multiples 1 by 9 and adds 33 to set the start of the range to row 42, and the end of the range to 1 x 9 plus 37, i.e. 46. The column is hard codes as "I"

On the second iteration, x =2 and hence 2 x 9 + 33 = 51 which is the start of the second range.

etc...

**Richard Buttrey** · 05-09-2011, 11:55 AM

Hi Dan,

Sorry, the starting point of the ranges was wrong. Substitute the following two lines in the For..Next loop

Please Login or Register  to view this content.

Regards

**Cicada** · 05-09-2011, 01:32 PM

Legend, thanks Richard for your time.

Many thanks
Dan

**Cicada** · 05-09-2011, 02:08 PM

sorry to bug you again Richard why in the second iteration does x = 2 what is this a function of?

Regards
Dan

Disreard sorry was having a vague moment