Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dragging Down a Formula
Now my actually case is more complicated than this, but I think if someone
explains this to me, I'll be fine. say i have a formula in cell b1 that is =a1*2 now in b2 i would have =a6*2 and in b3 i would have =a11*2 and continue to increase the cell value by five. Can i do this be just dragging down the corner rather than entering them manually? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dragging Down a Formula
In Cell B1, enter a formula like
=INDEX($A$1:$A$1000,(ROW(B1)-ROW($B$1))*5+1,1)*2 Note that the B1 and $B$1 are strictly needed - they should be the address of the first cell that this is entered in, so that you don't have to limit where you put the formula, and so that you can insert rows above and still have the formula work. This is the part that steps through a range by 5 rows everytime the formula is copied down by one row INDEX($A$1:$A$1000,(ROW(B1)-ROW($B$1))*5+1,1) That becomes INDEX($A$1:$A$1000,(1-1)*5+1,1) INDEX($A$1:$A$1000,1,1) or just A1 Copied down, you get INDEX($A$1:$A$1000,(ROW(B2)-ROW($B$1))*5+1,1) That becomes INDEX($A$1:$A$1000,(2-1)*5+1,1) INDEX($A$1:$A$1000,1*5+1,1) INDEX($A$1:$A$1000,6,1) or A6 etc. HTH, Bernie MS Excel MVP "JBoyer" wrote in message ... Now my actually case is more complicated than this, but I think if someone explains this to me, I'll be fine. say i have a formula in cell b1 that is =a1*2 now in b2 i would have =a6*2 and in b3 i would have =a11*2 and continue to increase the cell value by five. Can i do this be just dragging down the corner rather than entering them manually? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dragging Down a Formula
"Note that the B1 and $B$1 are strictly needed "
What I meant was that they AREN'T strictly needed.... Boy, my fingers just don't obey my mind sometime.... Bernie "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... In Cell B1, enter a formula like =INDEX($A$1:$A$1000,(ROW(B1)-ROW($B$1))*5+1,1)*2 Note that the B1 and $B$1 are strictly needed - they should be the address of the first cell that this is entered in, so that you don't have to limit where you put the formula, and so that you can insert rows above and still have the formula work. This is the part that steps through a range by 5 rows everytime the formula is copied down by one row INDEX($A$1:$A$1000,(ROW(B1)-ROW($B$1))*5+1,1) That becomes INDEX($A$1:$A$1000,(1-1)*5+1,1) INDEX($A$1:$A$1000,1,1) or just A1 Copied down, you get INDEX($A$1:$A$1000,(ROW(B2)-ROW($B$1))*5+1,1) That becomes INDEX($A$1:$A$1000,(2-1)*5+1,1) INDEX($A$1:$A$1000,1*5+1,1) INDEX($A$1:$A$1000,6,1) or A6 etc. HTH, Bernie MS Excel MVP "JBoyer" wrote in message ... Now my actually case is more complicated than this, but I think if someone explains this to me, I'll be fine. say i have a formula in cell b1 that is =a1*2 now in b2 i would have =a6*2 and in b3 i would have =a11*2 and continue to increase the cell value by five. Can i do this be just dragging down the corner rather than entering them manually? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dragging Down a Formula
I should have been more clear with my example, here is the formula I'm using
=IF(ISBLANK(F3:L3),"",SUM(IF(F3:L3<0,F3:L3,FALSE)) ). This formula is in cell Z2. Now in cell Z3 I want =IF(ISBLANK(F8:L8),"",SUM(IF(F8:L8<0,F8:L8,FALSE)) ). "Bernie Deitrick" wrote: In Cell B1, enter a formula like =INDEX($A$1:$A$1000,(ROW(B1)-ROW($B$1))*5+1,1)*2 Note that the B1 and $B$1 are strictly needed - they should be the address of the first cell that this is entered in, so that you don't have to limit where you put the formula, and so that you can insert rows above and still have the formula work. This is the part that steps through a range by 5 rows everytime the formula is copied down by one row INDEX($A$1:$A$1000,(ROW(B1)-ROW($B$1))*5+1,1) That becomes INDEX($A$1:$A$1000,(1-1)*5+1,1) INDEX($A$1:$A$1000,1,1) or just A1 Copied down, you get INDEX($A$1:$A$1000,(ROW(B2)-ROW($B$1))*5+1,1) That becomes INDEX($A$1:$A$1000,(2-1)*5+1,1) INDEX($A$1:$A$1000,1*5+1,1) INDEX($A$1:$A$1000,6,1) or A6 etc. HTH, Bernie MS Excel MVP "JBoyer" wrote in message ... Now my actually case is more complicated than this, but I think if someone explains this to me, I'll be fine. say i have a formula in cell b1 that is =a1*2 now in b2 i would have =a6*2 and in b3 i would have =a11*2 and continue to increase the cell value by five. Can i do this be just dragging down the corner rather than entering them manually? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dragging Down a Formula
J,
In Z2, enter the formula =IF(OFFSET($F$3,(ROW()-ROW())*5,0)="","",SUMIF(OFFSET($F$3:$L$3,(ROW()-ROW($Z$2))*5,0),"<0")) and copy down. HTH, Bernie MS Excel MVP "JBoyer" wrote in message ... I should have been more clear with my example, here is the formula I'm using =IF(ISBLANK(F3:L3),"",SUM(IF(F3:L3<0,F3:L3,FALSE)) ). This formula is in cell Z2. Now in cell Z3 I want =IF(ISBLANK(F8:L8),"",SUM(IF(F8:L8<0,F8:L8,FALSE)) ). "Bernie Deitrick" wrote: In Cell B1, enter a formula like =INDEX($A$1:$A$1000,(ROW(B1)-ROW($B$1))*5+1,1)*2 Note that the B1 and $B$1 are strictly needed - they should be the address of the first cell that this is entered in, so that you don't have to limit where you put the formula, and so that you can insert rows above and still have the formula work. This is the part that steps through a range by 5 rows everytime the formula is copied down by one row INDEX($A$1:$A$1000,(ROW(B1)-ROW($B$1))*5+1,1) That becomes INDEX($A$1:$A$1000,(1-1)*5+1,1) INDEX($A$1:$A$1000,1,1) or just A1 Copied down, you get INDEX($A$1:$A$1000,(ROW(B2)-ROW($B$1))*5+1,1) That becomes INDEX($A$1:$A$1000,(2-1)*5+1,1) INDEX($A$1:$A$1000,1*5+1,1) INDEX($A$1:$A$1000,6,1) or A6 etc. HTH, Bernie MS Excel MVP "JBoyer" wrote in message ... Now my actually case is more complicated than this, but I think if someone explains this to me, I'll be fine. say i have a formula in cell b1 that is =a1*2 now in b2 i would have =a6*2 and in b3 i would have =a11*2 and continue to increase the cell value by five. Can i do this be just dragging down the corner rather than entering them manually? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dragging formula | Excel Discussion (Misc queries) | |||
Dragging a formula correctly? | Excel Worksheet Functions | |||
dragging a formula down a column | Excel Worksheet Functions | |||
when dragging formula how do i avoid #DIV/0! | Excel Discussion (Misc queries) | |||
dragging a formula | Excel Discussion (Misc queries) |