Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dragging a formula down a column
I need to get the same range of values as I drag a formula down. For example
in cell A1 I might have the formula =sum(D1:D3). When I drag the formula in cell A1 down to A2 I want the range to change to (D4:D7) not (D2:D5). I believe I need to use the offset function but I am confused by it. Please help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dragging a formula down a column
Hi!
Try this: =SUM(OFFSET(D$1,(ROWS($1:1)-1)*3,,3)) The results will be: A1 = SUM(D1:D3) A2 = SUM(D4:D6) A3 = SUM(D7:D9) etc Biff "Jason R" <Jason wrote in message ... I need to get the same range of values as I drag a formula down. For example in cell A1 I might have the formula =sum(D1:D3). When I drag the formula in cell A1 down to A2 I want the range to change to (D4:D7) not (D2:D5). I believe I need to use the offset function but I am confused by it. Please help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dragging a formula down a column
Thanks Biff for the reply. It worked. I was hoping u could give an
explanation of how it works so I can apply it to other formulas. "Biff" wrote: Hi! Try this: =SUM(OFFSET(D$1,(ROWS($1:1)-1)*3,,3)) The results will be: A1 = SUM(D1:D3) A2 = SUM(D4:D6) A3 = SUM(D7:D9) etc Biff "Jason R" <Jason wrote in message ... I need to get the same range of values as I drag a formula down. For example in cell A1 I might have the formula =sum(D1:D3). When I drag the formula in cell A1 down to A2 I want the range to change to (D4:D7) not (D2:D5). I believe I need to use the offset function but I am confused by it. Please help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dragging a formula down a column
Hi!
=SUM(OFFSET(D$1,(ROWS($1:1)-1)*3,,3)) We're using the Offset function to determine the range argument for the Sum function. OFFSET(D$1,(ROWS($1:1)-1)*3,,3) D1 is the starting point. So, we want to offset D1 by an amount of rows. The number of rows is calculated using: (ROWS($1:1)-1)*3 The first cell that this formula is entered into, the number of rows to offset D1 by is calculated to be zero: (ROWS($1:1)-1)*3 Evaluates to: (1-1)*3 = 0 As you drag copy the formula down a column the ROWS() argument will increment and calculate a different row offset. (ROWS($1:2)-1)*3 Evaluates to: (2-1)*3 = 3 That would then mean to offset D1 by 3 rows (D4) Biff "Jason R" wrote in message ... Thanks Biff for the reply. It worked. I was hoping u could give an explanation of how it works so I can apply it to other formulas. "Biff" wrote: Hi! Try this: =SUM(OFFSET(D$1,(ROWS($1:1)-1)*3,,3)) The results will be: A1 = SUM(D1:D3) A2 = SUM(D4:D6) A3 = SUM(D7:D9) etc Biff "Jason R" <Jason wrote in message ... I need to get the same range of values as I drag a formula down. For example in cell A1 I might have the formula =sum(D1:D3). When I drag the formula in cell A1 down to A2 I want the range to change to (D4:D7) not (D2:D5). I believe I need to use the offset function but I am confused by it. Please help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dragging a formula down a column
Thanks a bunch
"Biff" wrote: Hi! =SUM(OFFSET(D$1,(ROWS($1:1)-1)*3,,3)) We're using the Offset function to determine the range argument for the Sum function. OFFSET(D$1,(ROWS($1:1)-1)*3,,3) D1 is the starting point. So, we want to offset D1 by an amount of rows. The number of rows is calculated using: (ROWS($1:1)-1)*3 The first cell that this formula is entered into, the number of rows to offset D1 by is calculated to be zero: (ROWS($1:1)-1)*3 Evaluates to: (1-1)*3 = 0 As you drag copy the formula down a column the ROWS() argument will increment and calculate a different row offset. (ROWS($1:2)-1)*3 Evaluates to: (2-1)*3 = 3 That would then mean to offset D1 by 3 rows (D4) Biff "Jason R" wrote in message ... Thanks Biff for the reply. It worked. I was hoping u could give an explanation of how it works so I can apply it to other formulas. "Biff" wrote: Hi! Try this: =SUM(OFFSET(D$1,(ROWS($1:1)-1)*3,,3)) The results will be: A1 = SUM(D1:D3) A2 = SUM(D4:D6) A3 = SUM(D7:D9) etc Biff "Jason R" <Jason wrote in message ... I need to get the same range of values as I drag a formula down. For example in cell A1 I might have the formula =sum(D1:D3). When I drag the formula in cell A1 down to A2 I want the range to change to (D4:D7) not (D2:D5). I believe I need to use the offset function but I am confused by it. Please help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula help for counting,with a column of dates and a column of n | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
column to column conditional formatting won't work, need formula | Excel Worksheet Functions | |||
Value between 2 dates | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |