average formula for dynamic range

**rossg** · 03-01-2014, 05:29 AM

hi need to know the notation for this

Please Login or Register  to view this content.

where A1 is really RC[-1] and the average range changes in length.

**:) Sixthsense :)** · 03-01-2014, 06:29 AM

May be this....

Please Login or Register  to view this content.

**rossg** · 03-01-2014, 06:37 AM

no sorry dont think it'll work.

formula = "previous column same row" - AVERAGE($A$2:'could be any length')

code looks more like this

Please Login or Register  to view this content.

**Richard Buttrey** · 03-01-2014, 06:42 AM

Hi

First create a dynamic range name say MyRange for your column A values. e.g

Range('Sheet1'!$A$1,0,0,Counta($A:$A),1)

Now the VBA is

Please Login or Register  to view this content.

**rossg** · 03-01-2014, 07:15 AM

thanks got it working

**Richard Buttrey** · 03-01-2014, 07:41 AM

Great. Glad to have been able to help.

Re: average formula for dynamic range