Tricky Formula

[Harish]

Hello,
I have a formula in one cell and this is the tricky part: When I copy
the formulas to other rows in the same column, I want the numbers to
decrease instead of increase. For example, I have the following
formula in one cell E2

=(E2*I$2)+(E25*I$3)+(E24*I$4)+(E23*I$5)+(E22*I$6)+(E21*I$7)+(E20*I
$8)+
(E19*I$9)+(E18*I$10)+(E17*I$11)+(E16*I$12)+(E15*I$13)+(E14*I$14)+
(E13*I
$15)+(E12*I$16)+(E11*I$17)+(E10*I$18)+(E9*I$19)+(E8*I$20)+(E7*I$21)+
(E6*I$22)+(E5*I$23)+(E4*I$24)+(E3*I$25)

Now when I copy this formula down to E3, I want the next cell's
formula to
look like this
=(E3*I$2)+(E2*I$3)+(E25*I$4)+(E24*I$5)+(E23*I$6)+(E22*I$7)+(E21*I$8)+
(E20*I$9)+(E19*I$10)+(E18*I$11)+(E17*I$12)+(E16*I$13)+(E15*I$14)+
(E14*I
$15)+(E13*I$16)+(E12*I$17)+(E11*I$18)+(E10*I$19)+(E9*I$20)+(E8*I$21)+
(E7*I$22)+(E6*I$23)+(E5*I$24)+(I$25*E4)

and the same goes for E4. The formula should go backwards till it
reaches 1 and then come back to 25 and decrease to where it was
started off.
Any ideas?

 

[joel]

I dont think you mean that the formula will go into cell E2 because that will
cause circular calculation. Usuallywhen I get problems like thsi a write a
macro to insert the formulas into a worksheet.

the other method is to use indirect with.

Instead of this (E2*I$2)
use (Indirect("E"&Row(E2))*I$2)

You can use need to make Row(E2) part of the formula more complicated to
work properly by using a MOD() to handle the wrap around when you get to the
last row and some subtraction becasuew you are decrreasing rath than
increasing. More like this

(Indirect("E"&(25-mod(Row(E2),24))*I$2)

 

[Harish]

Yea I was thinking of using VB but since I dont have a good VB
experience, someone suggested a matrix type formula which solved my
problem. Thanks for your help