Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Fill Problems
I have a formula that I want to implement for a series of data points. For
the first cell(A1) the formula would be "IF(E5=0, NA(), E6/E5)" The next cell in the series (A2) would be "IF(E8=0, NA(), E9/E8)". So each time the formula is iterated it increases each cell value by three. So, continuing the example the next cell (A3) would contain "IF(E11=0, NA(), E12/E11)". However, when I try the click-fill method it iterates by 1. I also cannot have excel do this using the Edit -Fill- Series method either. How may I iterate formulas by values different than 1? excelBRISKbaby |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Fill Problems
try in A1
if(indirect("E"&(2+row()*3)=0,NA(),indirect("E"&(3 +row()*3)/indirect("E"&(2+row()*3)) "excelBRISKbaby" wrote: I have a formula that I want to implement for a series of data points. For the first cell(A1) the formula would be "IF(E5=0, NA(), E6/E5)" The next cell in the series (A2) would be "IF(E8=0, NA(), E9/E8)". So each time the formula is iterated it increases each cell value by three. So, continuing the example the next cell (A3) would contain "IF(E11=0, NA(), E12/E11)". However, when I try the click-fill method it iterates by 1. I also cannot have excel do this using the Edit -Fill- Series method either. How may I iterate formulas by values different than 1? excelBRISKbaby |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Fill Problems
Cell references always increment by 1 so you have to approach this
differently. This works I think: -Enter your first formula in A1 -Select A1:A3 and drag the fill handle until you have all the formulas you want -With the range still selected do a {F5}, Special, Blanks, OK -Press Ctrl-Minus and select Shift Cells Up. -- Jim "excelBRISKbaby" wrote in message ... |I have a formula that I want to implement for a series of data points. For | the first cell(A1) the formula would be "IF(E5=0, NA(), E6/E5)" The next cell | in the series (A2) would be "IF(E8=0, NA(), E9/E8)". So each time the formula | is iterated it increases each cell value by three. So, continuing the example | the next cell (A3) would contain "IF(E11=0, NA(), E12/E11)". | | However, when I try the click-fill method it iterates by 1. I also cannot | have excel do this using the Edit -Fill- Series method either. | | How may I iterate formulas by values different than 1? | | excelBRISKbaby |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Fill Problems
BJ,
The formula works for a single cell but I'm not sure it fills correctly. Also, the indirect function that you are using can't be used in my setup according to Excel Help. Here's what follows in the Excel Help. "If you always want the formula to refer to the same cell regardless of whether the row above the cell is deleted or the cell is moved, use the INDIRECT worksheet function. For example, if you always want to refer to cell A10, use the following syntax: =INDIRECT("A10") " My program needs to be able to insert new rows to accept new data. Thank you very much for your help, it is appreciated! excelBRISKbaby "bj" wrote: try in A1 if(indirect("E"&(2+row()*3)=0,NA(),indirect("E"&(3 +row()*3)/indirect("E"&(2+row()*3)) "excelBRISKbaby" wrote: I have a formula that I want to implement for a series of data points. For the first cell(A1) the formula would be "IF(E5=0, NA(), E6/E5)" The next cell in the series (A2) would be "IF(E8=0, NA(), E9/E8)". So each time the formula is iterated it increases each cell value by three. So, continuing the example the next cell (A3) would contain "IF(E11=0, NA(), E12/E11)". However, when I try the click-fill method it iterates by 1. I also cannot have excel do this using the Edit -Fill- Series method either. How may I iterate formulas by values different than 1? excelBRISKbaby |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Fill Problems
Jim,
This method works! Thank you very much. I'll just need to leave enough room so that when new rows are entered I can use this method without over running into other data. Thank you again for your out-of-the-box thinking! excelBRISKbaby "Jim Rech" wrote: Cell references always increment by 1 so you have to approach this differently. This works I think: -Enter your first formula in A1 -Select A1:A3 and drag the fill handle until you have all the formulas you want -With the range still selected do a {F5}, Special, Blanks, OK -Press Ctrl-Minus and select Shift Cells Up. -- Jim "excelBRISKbaby" wrote in message ... |I have a formula that I want to implement for a series of data points. For | the first cell(A1) the formula would be "IF(E5=0, NA(), E6/E5)" The next cell | in the series (A2) would be "IF(E8=0, NA(), E9/E8)". So each time the formula | is iterated it increases each cell value by three. So, continuing the example | the next cell (A3) would contain "IF(E11=0, NA(), E12/E11)". | | However, when I try the click-fill method it iterates by 1. I also cannot | have excel do this using the Edit -Fill- Series method either. | | How may I iterate formulas by values different than 1? | | excelBRISKbaby |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Fill Problems
Great Stuff Jim
-- Regards Sri "Jim Rech" wrote: Cell references always increment by 1 so you have to approach this differently. This works I think: -Enter your first formula in A1 -Select A1:A3 and drag the fill handle until you have all the formulas you want -With the range still selected do a {F5}, Special, Blanks, OK -Press Ctrl-Minus and select Shift Cells Up. -- Jim "excelBRISKbaby" wrote in message ... |I have a formula that I want to implement for a series of data points. For | the first cell(A1) the formula would be "IF(E5=0, NA(), E6/E5)" The next cell | in the series (A2) would be "IF(E8=0, NA(), E9/E8)". So each time the formula | is iterated it increases each cell value by three. So, continuing the example | the next cell (A3) would contain "IF(E11=0, NA(), E12/E11)". | | However, when I try the click-fill method it iterates by 1. I also cannot | have excel do this using the Edit -Fill- Series method either. | | How may I iterate formulas by values different than 1? | | excelBRISKbaby |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Fill Problems
The formula will increment by a factor of three for each row you move down
just paste into as many rows as you need. Since it references row() which will change as you insert or delete rows, it will allow you to insert or delete rows. as you insert rows which would affect the active cells in column A all you have to do is to copy and paste into the new empty cells in column A "excelBRISKbaby" wrote: BJ, The formula works for a single cell but I'm not sure it fills correctly. Also, the indirect function that you are using can't be used in my setup according to Excel Help. Here's what follows in the Excel Help. "If you always want the formula to refer to the same cell regardless of whether the row above the cell is deleted or the cell is moved, use the INDIRECT worksheet function. For example, if you always want to refer to cell A10, use the following syntax: =INDIRECT("A10") " My program needs to be able to insert new rows to accept new data. Thank you very much for your help, it is appreciated! excelBRISKbaby "bj" wrote: try in A1 if(indirect("E"&(2+row()*3)=0,NA(),indirect("E"&(3 +row()*3)/indirect("E"&(2+row()*3)) "excelBRISKbaby" wrote: I have a formula that I want to implement for a series of data points. For the first cell(A1) the formula would be "IF(E5=0, NA(), E6/E5)" The next cell in the series (A2) would be "IF(E8=0, NA(), E9/E8)". So each time the formula is iterated it increases each cell value by three. So, continuing the example the next cell (A3) would contain "IF(E11=0, NA(), E12/E11)". However, when I try the click-fill method it iterates by 1. I also cannot have excel do this using the Edit -Fill- Series method either. How may I iterate formulas by values different than 1? excelBRISKbaby |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula problems | Excel Discussion (Misc queries) | |||
Formula problems | Excel Worksheet Functions | |||
Problems with auto-fill of dates | Excel Discussion (Misc queries) | |||
Formula Problems | Excel Worksheet Functions | |||
autoshape fill problems in Excel 2003 | Excel Discussion (Misc queries) |