sub function within formula

[ben sutherland]

Within a formula I want to replace sub functions with cell references to these functions e.g. in cell C1 I have the formula

1. =IF(A1>0,B1+1,B1-1)

Copying sub functions B1+1 & B2-1 to cells F1 & F2 respectively, the above formula reads

2. =IF(A1>0,F1,F2)

The problem I’m having is as follows:

In a single row both formula return the same result. However if I copy the 2nd formula to the 2nd, 3rd row etc. formula 1 calculates:

Row 2 = IF(A2>0,B2+1,B2-1)
Row ‘n’ = IF(An>0,Bn+1,Bn-1)

Formula 2 however will calculate

Row 2 =IF(A2>0, B1+1,B1-1)
Row ’n’ =IF(An>0, B1+1,B1-1)


Relative cell referencing doesn’t apply to the 2nd formula because I’m using other cell references. Is there a way around this problem or do I need to take a different approach?

Would greatly appreciate any advice.

Thanks

 

[Damon Ostrander]

Hi Ben,

Here is a workaround. Simply use a loop like this in the Immediate pane of the Visual Basic Editor:

for row=2 to 100: cells(row,"C")= "= IF(A2>0,B2+1,B2-1)" :next row

where "C" is the column you want to place the formula in. This will place the formula in rows 2 to 100 without autofilling. The colons enable the three statements be done in one line so that the code can be run in the Immediate pane, which only allows one-liners.

Damon