Tricky Formulas

  • Thread starter Thread starter Harish
  • Start date Start date
H

Harish

Hi,

I have to perform a tricky calculation using excel. In one cell I have
entered the following formula:

=(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, I can;t copy this formula down to the next cell but I am
expecting this formula in the next cell down:

=(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)

As you can see, the numbers in the E column has to decrease going from
25 to whatever number i started off with. Can anybody suggest any
ideas as when I copy down this formula, the numbers in the E column
will decrease? Thanks
 
Not really sure what you mean. If you drag this formula down to the next
cell it should give the result you have given, assuming that +(E2*I$3) is a
typo and should be +(E26*I$3).
If you want the cell references in column E to decrease you need to drag it
up, dragging it down will increase them.
Regards,
Alan,
 
If i drag it down to next cell E3, i'll get this:

=(E3*I$2)+(E26*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)

which is not what I want. I am sure there is some function to decrease
the cell numbers but i don't know.
 
If i drag it down to next cell E3, i'll get this:

=(E3*I$2)+(E26*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)

which is not what I want. I am sure there is some function to decrease
the cell numbers but i don't know.
Click to expand...

Maybe if you gave more than one example, we could tell whether, as
Alan surmised, you made a typo or, as I suspect, you are just not
describing what you want to have happen very clearly.

As Alan says, copying the formula down does, indeed, do what you SAY
you want ("function to decrease the cell numbers"). But you seem to
want something else.

What?
 
ok another example i can give you is, the formula in E4 would be like
this:

=(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)

the formula in E3 would be 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)+(E4*I$25)

the formula in E2 would be like this:

=(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)

and so on.....I have to do this for 30 cells and I am too lazy to
write lengthy formulas like this. If there is any easier way to copy
down the formulas with the conditions that I have described above, it
would make my life a lot easier.

I don't know if the OFFSET method would help to do what I am talking
about. Also VB might be used to solve this problem, but i m not good
at coding.
 
I think I see what you mean. Place the formula in say row 50 to give plenty
of room and then drag it upward. This will do what you want.
It's just the way Excel works, drag it down and the cell references
increase, drag it up and they decrease. I daresay there is a way to reverse
this using code, bit is it worth it?
Regards,
Alan.
 
The method that you told me is not working for me. I think I have to
use VB for this problem. Using a loop in that code will help me give
what I want. Thanks for your suggestions.
 
The formulas you showed cannot be in the cells you say they are in,
otherwise they would generate a circular reference error (each formula
starts with a reference to the cell you say the formula is in). Can you
clarify this for us?
 
I realized my mistake now. What i mean to say was the formulas were in
F2, F3, and F4 not E2, E3 and E4. Sorry for the confusion.
 
Harish said:
Hi,

I have to perform a tricky calculation using excel. In one cell I have
entered the following formula:

=(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, I can;t copy this formula down to the next cell but I am
expecting this formula in the next cell down:

=(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)

As you can see, the numbers in the E column has to decrease going from
25 to whatever number i started off with. Can anybody suggest any
ideas as when I copy down this formula, the numbers in the E column
will decrease? Thanks
Click to expand...

You might try copy then cut. You copy diagonally to the cell down and to
the right and then cut back to the cell below. You may then next to fix
some of it up but I think you will get close with the copy and cut.
 
Never mind, I found the solution to my own problem. Thanks
Click to expand...

It is generally considered proper newsgroup behaviour to show the
solution that you found. Since others have invested time and energy
into YOUR problem, this is the least you can do.
 
ok another example i can give you is, the formula in E4 would be like
this:

=(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)

the formula in E3 would be 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)+(E4*I$25)

the formula in E2 would be like this:

=(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)

and so on.....I have to do this for 30 cells and I am too lazy to
write lengthy formulas like this. If there is any easier way to copy
down the formulas with the conditions that I have described above, it
would make my life a lot easier.

I don't know if the OFFSET method would help to do what I am talking
about. Also VB might be used to solve this problem, but i m not good
at coding.
Click to expand...

Offset will do it, but not terribly easily. All you have to do is
deal with the endpoints (when the numbers wrap around from 25 back to
2. Truth be told, if all you have to do is do this for 30 cells
(which, I might add MAKES NO SENSE AT ALL, since you only gave a list
of 24 potential cells to do it for - so, is your list really longer,
or are you just messing with everybody?), the least effort will be to
do copy/paste and then edit what doesn't work.
 
Back
Top