Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
incrementing
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
incrementing
=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. "paul/bones" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
incrementing
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. "JLatham" wrote: =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. "paul/bones" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
incrementing | Excel Discussion (Misc queries) | |||
Variable Incrementing | Excel Discussion (Misc queries) | |||
Incrementing numbers | Excel Worksheet Functions | |||
Incrementing by 1 when doc is opened | Excel Worksheet Functions | |||
Getting the last value of an incrementing sheet | Excel Worksheet Functions |