Offset Question

[BobA]

Claus provided me with this formula which works great. However, after doing a little research there is one part of the formula I don't understand. Specifically the three commas after (OFFSET(c$8,,,

=AVERAGE(OFFSET(C$8,,,COUNT(C$8:C$5000)-1))

Using two commas and four commas works, but changes the value slightly. One or five commas returns an error.

What are their function?

Thanks

 

[Norman Jones]

Claus provided me with this formula which works great. However, after
doing a little research there is one part of the formula I don't understand.
Specifically the three commas after (OFFSET(c$8,,,

=AVERAGE(OFFSET(C$8,,,COUNT(C$8:C$5000)-1))

Using two commas and four commas works, but changes the value slightly. One
or five commas returns an error.

What are their function?

Thanks

Hi Bob,

See Excel help and examples:

http://goo.gl/FrpYnd



===
Regards,
Norman

 

[Claus Busch]

Hi Bob,

Am Tue, 4 Feb 2014 06:30:43 -0800 (PST) schrieb BobA:

=AVERAGE(OFFSET(C$8,,,COUNT(C$8:C$5000)-1))

there are 2 arguments into the OFFSET formula you don't need or the
value is 0. The first argument is the offset from start cell in rows and
the second one the offset from start cell in columns. So you don't need
to write the 0 but you have to write the commas.
Try instead:
=AVERAGE(OFFSET(C$8,0,0,COUNT(C$8:C$5000)-1))


Regards
Claus B.

 

[BobA]

Hi Bob,



Am Tue, 4 Feb 2014 06:30:43 -0800 (PST) schrieb BobA:






there are 2 arguments into the OFFSET formula you don't need or the

value is 0. The first argument is the offset from start cell in rows and

the second one the offset from start cell in columns. So you don't need

to write the 0 but you have to write the commas.

Try instead:

=AVERAGE(OFFSET(C$8,0,0,COUNT(C$8:C$5000)-1))





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

OK, I think I get it. The zeros are superfluous, but the commas are not.

Thanks.

 

[BobA]

OK, I think I figured something else out. In this formula:

=AVERAGE(OFFSET(C$8,0,0,COUNT(C$8:C$5000)-1))

I couldn't figure out how this was all working. What was happening here? But after a little tinkering I think I've got it.

You need reference, rows, columns, height and width.

The first 0 is reference rows and the second zero is reference columns and the "COUNT(c$8:C$5000)-1" is reference height. (This was the part that was throwing me.) Reference witdth is 1, so it's not necessary.

It took a while, but I hope this is right.

Thanks to all.

 

[Claus Busch]

Hi Bob,

Am Tue, 4 Feb 2014 08:01:49 -0800 (PST) schrieb BobA:

It took a while, but I hope this is right.

yes, you got it


Regards
Claus B.