Good morning,

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

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?







"SunshineStateBroker via OfficeKB.com" wrote:

Good morning,

On a worksheet, i do two averages. The first one is displayed in cell V19 and
the other in W19
.
For the first average displayed in V19 i identify a string of values in the K
column. The beginning row number is entered in R19 and the ending row number
is entered in R22.
Example: R19 = 25 and R22 = 50, i would take the average of cells K25 thru
K50 and display the result in cell V19.

For the second average displayed in W19, again the beginning row number is
defined in R19 but the ending row number is now displayed in cell Y19. As
with the first calculation, i would need to find the average of that range as
well.
Example: R19 = 25 and Y19 = 35, i would take the average of cells K25 thru
K35 and display the result in cell W19.

Having each macro trigger individually after the result is entered in R22 and
Y19 respectively would be amazing.

Any assistance with this would be most appreaciated as it would be a HUGE
timesaver for me.
Thank you.

--
Message posted via http://www.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 Molloy wrote:
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,

[quoted text clipped - 20 lines]
timesaver for me.
Thank you.

--
Message posted via http://www.officekb.com

Works like a charm!

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


Patrick Molloy wrote:
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,

[quoted text clipped - 20 lines]
timesaver for me.
Thank you.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200910/1