Offset function help

[Guest]

I am trying to replace the range in an average calculation using the offset
function but its not giving me the result the same as using Excels standard
average function.

Say I want to;

=AVERAGE(I6:I15)

Therefore;
I want to dynamically adjust the rane using I6 as the reference point.
I figure that using I6 I need to adjust the rows by 9 (downwards) the cols
by 0. The overall height = 10 and the width 1.

So Iv'e tried;

=AVERAGE(OFFSET(I6:I6,9,0,10,1))

But it gives me a different answer and I cannot work out what range this is
actually an average of.

What should I do?

Bruce

 

[Bob Phillips]

You only need =AVERAGE(OFFSET(I6,9,0,10,1)), but irrewspective it works fine
for me as you describe it.

Can you give an example from I6 on that doesn't work.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Hi Bob,

Just tried =AVERAGE(OFFSET(I6,9,0,10,1))and it gives the same answer as
=AVERAGE(OFFSET(I6:I6,9,0,10,1)).

Also worked out this gives the average of I15:I24 ( I want the Average of
I6:I15). I actual want to only displace the I15 part of this range, not the
first I6.

Reason is the formula is on the RHS of a MSQuery. If I refresh it and the
result is NULL then the formula’s range changes from I6:I15 to I6:I6.

Another idea to I just displace the second I15 from I6 (similar to what you
suggest, except as 1x1 dimension) and concatenate the first I6?

Bruce

 

[Guest]

The first argument after the address indicates how many rows from the address
you want the offset range to START. In your example you want the range to
start with the referenced cell, so use:

=AVERAGE(OFFSET(I6,0,0,10,1))

 

[Guest]

Thanks Duke,
got it now

Bruce

The first argument after the address indicates how many rows from the address
you want the offset range to START. In your example you want the range to
start with the referenced cell, so use:

=AVERAGE(OFFSET(I6,0,0,10,1))