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*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, 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)

Any ideas?

 

[Harlan Grove]

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

[reformatted and deleting unnecessary parentheses]

=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

....

If you were entering this formula in cell X99, you could use the
following array formula instead.

=SUM(MMULT(--(MOD(ROW(E$2:E$25)+TRANSPOSE(ROW(E$2:E$25))-2*MIN(ROW(E
$2:E$25)),
ROWS(E$2:E$25))=ROWS(X$99:X99)-1),E$2:E$25)*$I$2:$I$25)

When you copy it down into X100, the X100 formula will return the same
result as

[reformatted and deleting unnecessary parentheses]

=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

Simplified matrix multiplication example.

/ 0 1 0 0 0 0 \ / a \ / b \
| 1 0 0 0 0 0 | | b | | a |
| 0 0 0 0 0 1 | | c | | f |
| 0 0 0 0 1 0 | * | d | = | e |
| 0 0 0 1 0 0 | | e | | d |
\ 0 0 1 0 0 0 / \ f / \ c /

 

[Harish]

Is MULT a function? I didnt get anything with ur solution

 

[Harish]

I see that in your solution, you have an = sign in the middle of the
formula which i think is wrong. Do you understand where I am getting
with this.

To explain it to you again, I have to write this formula in 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)

In cell E3, I have to write this formula:

=(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)+(E4*I$25)

In cell E4, I have to write this formula:

=(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)+(E4*I$25)

I have to do this for 30 cells and it is a real pain to write lengthy
formula again and again. If you can understand the pattern from the
above, you should be able to suggest me some quick way out of this
pain. Thanks for your help

 

[Harlan Grove]

Is MULT a function? I didnt get anything with ur solution

It's MMULT, 2 Ms. I tested it, and it works.

 

[Harish]

I am mistaken in my last post about E4: I meant to write this formula
in cell E4

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

Sorry for the confusion!!

 

[Harish]

Wow....Thanks Harlan...This is amazing....Thanks for your help