Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
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)+(E 8*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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
Harish wrote...
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$2 5 .... 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*E 4 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 / |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
Is MULT a function? I didnt get anything with ur solution
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
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)+(E 8*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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
Harish wrote...
Is MULT a function? I didnt get anything with ur solution It's MMULT, 2 Ms. I tested it, and it works. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
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)+(E 23*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!! On Mar 31, 10:04*pm, Harish wrote: 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)+(E 8*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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
Wow....Thanks Harlan...This is amazing....Thanks for your help
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|