Formula Fill Problems

[Guest]

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

 

[Guest]

try in A1
if(indirect("E"&(2+row()*3)=0,NA(),indirect("E"&(3+row()*3)/indirect("E"&(2+row()*3))

 

[Jim Rech]

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
|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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Great Stuff Jim

 

[Guest]

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