Offset Question

  • Thread starter Thread starter BobA
  • Start date Start date
B

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
 
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
Click to expand...

Hi Bob,

See Excel help and examples:

http://goo.gl/FrpYnd



===
Regards,
Norman
 
Hi Bob,

Am Tue, 4 Feb 2014 06:30:43 -0800 (PST) schrieb BobA:
=AVERAGE(OFFSET(C$8,,,COUNT(C$8:C$5000)-1))
Click to expand...

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.
 
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
Click to expand...

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

Thanks.
 
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.
 
Back
Top