incrementing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paul/bones - 12 Sep 2007 13:12 GMT

I have 2 sheets and on the first one I have used a function to calculate the
average of each successive group of 10 values. Then I want to take all of
these values without the gaps that are present between calculated averages
and have them placed in the second sheet. My reference to sheet 1 is

=testfunc!B1

This value will be the first in the column of average values. I wanted to
know if there was a way for me to get the value of testfuncB11 into the
second cell in my column of averages in sheet 2 as this would be the cell my
next calculated average is in?

Reply to this Message

JLatham - 12 Sep 2007 13:24 GMT

=OFFSET(testfunc!$B$1,(ROW()-1)*10,0)

You may have to fiddle with the ROW()-1 portion. I put
=testfunc!B1 into row 1 on my setup, then the formula above into row 2.
So for me ROW()-1 evaluates to 1 to give a row offset of 10, taking me to
B11 on testfunc sheet.

If I continued on down my sheet, then when the formula is in row 3, ROW()-1
evaluates to 2 and 2*10 = 20, so it returns value from B21 on testfunc sheet.

Hope that helps.

> I have 2 sheets and on the first one I have used a function to calculate the
> average of each successive group of 10 values. Then I want to take all of
[quoted text clipped - 7 lines]
> second cell in my column of averages in sheet 2 as this would be the cell my
> next calculated average is in?

Reply to this Message

paul/bones - 12 Sep 2007 13:54 GMT

It does work I just had to do some messing with the ROW()-1 portion like you
mentioned. Thanks for the help I appreciate it.

> =OFFSET(testfunc!$B$1,(ROW()-1)*10,0)
>
[quoted text clipped - 19 lines]
> > second cell in my column of averages in sheet 2 as this would be the cell my
> > next calculated average is in?

Reply to this Message