Returning the average of two ranges with a macro

  • Thread starter SunshineStateBroker via OfficeKB.com
  • Start date
S

SunshineStateBroker via OfficeKB.com

Good morning,

On a worksheet, i do two averages. The first one is displayed in cell V19 and
the other in W19
 
P

Patrick Molloy

your first formula is

=AVERAGE(OFFSET(K1,R19-1,0,R22-R19+1,1))

the second formula is just a matter of changing the address R22 to Y19

OFFset from K1 will find the first address. so if R19 was 25, offsetting
from K1 by 25 rows is K26, hence we deduct 1 to get
OFFSET(K1,R19-1,0
lext, we resize by the number of rows. ie the difference between th first
row given br R19 (25) and the last row in R22 (50)...but the difference is
the fences, we need the posts, hence rows 25 to 50 INCLUSIVE is 26 rows
....thats why we add 1 to the 'height' of the offset range

I hope this is clear?
 
S

SunshineStateBroker via OfficeKB.com

Oh cool, so i don't even need to put this in the macro section, just slap
that formula in teh excel fields?



Patrick said:
your first formula is

=AVERAGE(OFFSET(K1,R19-1,0,R22-R19+1,1))

the second formula is just a matter of changing the address R22 to Y19

OFFset from K1 will find the first address. so if R19 was 25, offsetting
from K1 by 25 rows is K26, hence we deduct 1 to get
OFFSET(K1,R19-1,0
lext, we resize by the number of rows. ie the difference between th first
row given br R19 (25) and the last row in R22 (50)...but the difference is
the fences, we need the posts, hence rows 25 to 50 INCLUSIVE is 26 rows
...thats why we add 1 to the 'height' of the offset range

I hope this is clear?
Good morning,
Click to expand...
[quoted text clipped - 20 lines]
timesaver for me.
Thank you.
Click to expand...
Click to expand...
 
S

SunshineStateBroker via OfficeKB.com

Works like a charm!

Thank you both for the solution and the reasoning behind it.


Patrick said:
your first formula is

=AVERAGE(OFFSET(K1,R19-1,0,R22-R19+1,1))

the second formula is just a matter of changing the address R22 to Y19

OFFset from K1 will find the first address. so if R19 was 25, offsetting
from K1 by 25 rows is K26, hence we deduct 1 to get
OFFSET(K1,R19-1,0
lext, we resize by the number of rows. ie the difference between th first
row given br R19 (25) and the last row in R22 (50)...but the difference is
the fences, we need the posts, hence rows 25 to 50 INCLUSIVE is 26 rows
...thats why we add 1 to the 'height' of the offset range

I hope this is clear?
Good morning,
Click to expand...
[quoted text clipped - 20 lines]
timesaver for me.
Thank you.
Click to expand...
Click to expand...
 
Top