Tricky Formulas

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
0
3/31/2009 11:33:48 PM
excel 39879 articles. 2 followers. Follow

14 Replies
1573 Views

Similar Articles

[PageSpeed] 59

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,
"Harish" <mahadevan.swamy@gmail.com> wrote in message 
news:740e85a6-009d-46d3-9cd3-3fe8aa3022ea@e5g2000vbe.googlegroups.com...
> 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 

0
alan111 (581)
3/31/2009 11:59:56 PM
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.
0
4/1/2009 12:14:44 AM
On Tue, 31 Mar 2009 17:14:44 -0700 (PDT), Harish
<mahadevan.swamy@gmail.com> wrote:

>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.

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?
0
dranon (82)
4/1/2009 12:34:44 AM
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.

0
4/1/2009 1:08:17 AM
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.
"Harish" <mahadevan.swamy@gmail.com> wrote in message 
news:12283c93-fdc9-4192-9ffb-a9dc05100fb5@f19g2000vbf.googlegroups.com...
> 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.
> 

0
alan111 (581)
4/1/2009 1:23:48 AM
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.
0
4/1/2009 1:38:48 AM
It does work, drag it up and you will get what you want.
"Harish" <mahadevan.swamy@gmail.com> wrote in message 
news:ca002d48-3323-43a3-8c52-d162177989f8@n17g2000vba.googlegroups.com...
> 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. 

0
alan111 (581)
4/1/2009 1:54:33 AM
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?

-- 
Rick (MVP - Excel)


"Harish" <mahadevan.swamy@gmail.com> wrote in message 
news:12283c93-fdc9-4192-9ffb-a9dc05100fb5@f19g2000vbf.googlegroups.com...
> 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.
> 

0
4/1/2009 2:30:00 AM
You have circular ref: the formula in E4 cannot contain E4


"Alan" <alan111@ntlworld.com> wrote in message 
news:Ox7kEimsJHA.4592@TK2MSFTNGP06.phx.gbl...
>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.
> "Harish" <mahadevan.swamy@gmail.com> wrote in message 
> news:12283c93-fdc9-4192-9ffb-a9dc05100fb5@f19g2000vbf.googlegroups.com...
>> 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.
>>
> 


0
ericNOSPAM (46)
4/1/2009 2:35:10 AM
Never mind, I found the solution to my own problem. Thanks
0
4/1/2009 4:59:45 AM
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.
0
4/1/2009 5:07:00 AM
"Harish" <mahadevan.swamy@gmail.com> wrote in message 
news:740e85a6-009d-46d3-9cd3-3fe8aa3022ea@e5g2000vbe.googlegroups.com...
> 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

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. 


0
vMike
4/1/2009 3:01:39 PM
On Tue, 31 Mar 2009 21:59:45 -0700 (PDT), Harish
<mahadevan.swamy@gmail.com> wrote:

>Never mind, I found the solution to my own problem. Thanks

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.
0
dranon (82)
4/1/2009 10:48:37 PM
On Tue, 31 Mar 2009 18:08:17 -0700 (PDT), Harish
<mahadevan.swamy@gmail.com> wrote:

>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.

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.


0
dranon (82)
4/1/2009 11:12:01 PM
Reply:

Similar Artilces:

HELP WITH FORMULA #15
I have 3 columns. C -I=J These columns are continuous and I keep adding amounts to cells in column C and deducting amounts from I column and need to end up with a total in column J. Could someone please help me with a formula that will continually give me a total to column J as I enter information into column C and deducting from column I. -- Thank you in advance for your assistance HankL =SUM(J1:J100) Adjust range of cells to your need....... Vaya con Dios, Chuck, CABGx3 "Hank Laskin" wrote: > I have 3 columns. > C -I=J > These columns are continuous and I ...

Using a formula to fill a field
I am ery new to Access and was wondering, is there anyway to make a field fill automaticaly with a formula? Like in excel for example if you have a fromula on a cell and the arguments change the cell changes. I have a table with a field of invoice amounts and then a table with account info, i want to know if there is a way for the account table (ie: Balance field) to update automaticaly when you add an invoice with that accountID. Access is a relational database. An Access table may look like a spreadsheet, but it is really just a "bucket o' data". No, you can't ad...

Formula needed 01-22-10
Hi I need a formula that will look in one column range for a certain value and for another value in another column range and when they are both found in the same row, will count the number of occurances. Help!!!! Krissy wrote: > Hi > I need a formula that will look in one column range for a certain value and > for another value in another column range and when they are both found in the > same row, will count the number of occurances. Help!!!! http://www.contextures.com/xlFunctions01.html#SumProduct Omit the "values to be summed". =SUMPRODUCT(--(A2...

VBA to select formula cells OK -but want certain cells with ColorI
Using XL 2003 & 97 The following subroutine works. That said, how can I fine formula cells with a "!" in the cell. (In short, probably a cell formula referring to another sheet) With the following code I would like all formula cells set to colorindex 6 (yellow) but all formula cells with "!" set to a color index of 3 (red) I tried to use an If statement but to no avail. Sub SelectFormulaColor() Selection.SpecialCells(xlCellTypeFormulas, 23).Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With ActiveWindow.Dis...

Numeric content in one cell ( implicit formula ) and the result in another one
Hi, I made some search before, but too much information at the same time. So I post this question: In cell D2 , I have the following content : 10002/(14971213 - 37375) /1000000) In cell E2, I would like to have the result value of data in cell D2 : 669,754 I would not work with "left...len... search... right.." as the format ( then formula ) in cell D2 may change. Is there a function giving the computed result of a cell and put the result in another one? Best regards Pierre In cell E2, will the formula "=D2" do it for you? Or is that too simplistic and I don'...

what does it mean -- in formulas
what does this -- sign do in formulas -- Nawaz5 ----------------------------------------------------------------------- Nawaz50's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3393 View this thread: http://www.excelforum.com/showthread.php?threadid=53714 See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Nawaz50" <Nawaz50.26yzbm_1146219908.114@excelforum-nospam.com> wrote in message news:Nawaz50.26yzbm_1146219908.114@excelforum-nosp...

Formula For Monthly Date
Hello, I have a worksheet that has products listed that are on sale at different times of the year. My question is what formula to use to get all Jan products,codes,prices etc on to new worksheet, Feb on another worksheet etc. Thanks Mare Use Autofilter. See: http://www.contextures.com/xlautofilter01.html -- Gary's Student "Mare" wrote: > Hello, > I have a worksheet that has products listed that are on sale at different > times of the year. My question is what formula to use to get all Jan > products,codes,prices etc on to new worksheet, Feb on another works...

formula question #7
Is there a formula that can change the color of cells if revenues drop three months in a row and if so what should the formula be? Hi, Assuming that your data is in row 1, columns A,B,C, with C being the latest data, Try using conditional formatting in cell C1 with the "Formula Is" =IF(AND(C1<B1,B1<A1),1,0) HTH >-----Original Message----- >Is there a formula that can change the color of cells if >revenues drop three months in a row and if so what should >the formula be? >. > Formula only needs =AND(D1<C1,C1<B1,B1<A1) you don't need t...

"IF" Formulae
I'm trying to create a formula causing one of three answers. The object being any one of 0, 1 or >1. I need 0 an >1 to answer "years" and 1 to answer "year". My efforts below. =IF(G8>1,"years","year")*IF(G8<1,"years") My problem is that I can't override the first false conclusion. Any suggestions? Seems to me that you really only have two options (1 or Not 1). Try this: =if(G8=1,"year","years") HTH, Elkar "Libby" wrote: > I'm trying to create a formula causing one of three ...

day of the week and date formula
Hello I am looking to write a formula that gives me the difference in hours between a logged date and time and a closed date and time eg -27/11/2009 09:23:26 and 30/11/2009 10:34:20. Once I have this I need to calculate the working hours used to resolve the issue. If the duration of the time includes a weekend, a saturday would equate to 4 hours working and a sunday would be 0 hours working time, a week day equates to 11.5 hours working. Any suggestions?? Thanks -- Sarah Hi, we need to know what hours during any day are considered working - ie, do all hours...

What formula to use?
I have a list of teams in column B. In a tab called Division I have 6 division names with the teams listed in their respective division. I want column C to enter the appropriate division name for the division the team is in. For example, I want column C to be labelled as AL EAST when column B has BOS in it. How do I do this? Thank you. =INDEX(Division!A:A,MATCH(B1,Division!B:B,0)) etc. -- HTH RP (remove nothere from the email address if mailing direct) "Jambruins" <Jambruins@discussions.microsoft.com> wrote in message news:3100394C-BF57-470D-A218-BCA9C85D8D54@mi...

Formula help #12
Hi All, I'm trying to create a formula but can't work it out. We've decided that were going to class each month from the 20th to the 21st, rather than the 1st of the 1st. So 21/05/05 to 20/06/05 (dd/mm/yy) will all be classed as May, and from 21/06/05 it will be classed as June. I have a list of dates and would like a formula in the column next to it that will work out what month were classing the date in and put the month in an MMM format .i.e May So (dd/mm/yy) (mm) 21/05/05 May 22/05/05 May 20/06/05 May 21/06/05 June 30/06/05 June 21/07/05 Aug I should be able t...

Inserting space in between formulas
I put in a formula to combine a string of words with numbers and would like a space or dash to go in between. How do I do this? =A1 & " " & B1 or =A1 & " - " & B1 Where, A1 = First part of text (or numbers) B1 = Second part of text (or numbers) Substitute A1 / B1 with the relevant cells in your spreadsheet "Mary" wrote: > I put in a formula to combine a string of words with numbers and would like a > space or dash to go in between. How do I do this? Thanks, that worked great! Now I want to delete the column that references that...

Text and Formula in the Same Cell
Good Morning, Everyone - Here's what I'm trying to do: I have a cell that has text - "Vendo Name" - and I also want that cell to contain a formula (counta). I this possible? Thankx, C -- theboatdud ----------------------------------------------------------------------- theboatdude's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1024 View this thread: http://www.excelforum.com/showthread.php?threadid=27525 Here's an example: ="Vendor Name: "&COUNTA(B:B) HTH Jason Atlanta, GA >-----Original Message----- > >Goo...

Logical formulas
I think I got this in the wrong Discussion group. I put it General Discussion before but I think it belongs here. Original Message below: I must admit. I am horrible at logical functions. I am trying to compose a formula that will return a value of "1" if number is greater than 1, less than 4, greater than 949, and less than 991. Here is what I have now but it is not working: =IF(AND(G7>0,G7<4,G7>949,G7<991),1,0) Any help would be awesome! -- -CRM Try this: =IF(OR(AND(G7>0,G7<4),AND(G7>949,G7<991)),1,0) Does that help? ------------------------...

Help with copying formula
Hi I run Excel 2K I have typed the following formulas in three consecutive cells. =SUM('DRO BY SHIFT'!Z10:Z12)+BP10 =SUM('DRO BY SHIFT'!Z13:Z15)+BP11 =SUM('DRO BY SHIFT'!Z16:Z18)+BP12 You will note that the Z colum ranges take in 3 cells each time. The problem is that when I drag or copy these formulas down the column I dont get the reltive ranges that I need. That is the next one down should read =SUM('DRO BY SHIFT'!Z19:Z21)+BP13 However I get =SUM('DRO BY SHIFT'!Z17:Z19)+BP13 How can I write the formula so that when I drag it down it maintai...

What is procedure to exit formula auditing mode?
Please advise. Thanks, Doug To remove the arrows ? <Alt> <T> <U> <A> Not really a shortcut though. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "W8" <W8@discussions.microsoft.com> wrote in message news:99B64142-8037-43F4-BDB1-AF7170EF54EC@microsoft.com... Please advise. Thanks, Doug Type Ctrl+` (Grave Accent) to toggle formula auditing mode on/off or go to Tools>Formula Auditing>Formula Auditing Mode. HTH |:&g...

array formula Excel 97 (Poker)
Cells A1:A7 each contain a random integer from 1 to 13, with possibl repetitions. I have been trying to create a single formula that will test, for cel A1, for the presence of each of the next lowest four numbers in th range A!:A7 So if A1 contains 9, I would like the formula to return 1 if and onl if 8,7,6,and 5 all occur in the range somewhere, and return otherwise. This is tantamount to testing whether seven cards contain a five car straight headed by the card in A1. I just can't get the right combination of array or sumproduct to d it. I just know ther must be one........... Bil...

Conditional Formatting VBA with formula to find string
Hi, Please can I get some help.... I need to create a conditional format VBA in Excel 2007 (because I have many conditions to include) and I don't know how to do it... The Action Required: If Product A appears in any text string in range($C$95:$C$300) then colour that cell RED, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell BLUE, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell GREEN, and so on through 41 products... Data - I have a list of about 41 Product names in range Z...

copying formulas #3
This is probably a simple question for most of you, but I have an exce file that contains approx. 400 sheets, with a summary sheet. How do copy a simple formula to read Cell B1, but the next sheet needs to rea Cell B2 and so on. Example: Sheet #1 is the summary, then sheets 2-40 have a title taken from the summary, so if I do a typical copy formula it keeps reading Cell B1. Hope my question is clear!!! Thanks -- Ladkin ----------------------------------------------------------------------- Ladkins's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3252 View t...

Help Creating Excel Formula
Hello All, I'm having a hard time creating a formula to collect data for work. Here is what I'm looking to do: To explain I will use cells A1, A2, A3 & A4. To start cells A1 & A4 are empty and cells A2 & A3 have values. Now when a value is added to cell A1 sum cells A2 & A3 to cell A4. When the value in cell A1 increses add cell A3 to the amount currently in cell A4 and so on (see below). A1 A2 A3 A4 7200 600 A1 A2 A3 A4 1 7200 600 7800 A1 A2 A3 A4 2 7200 600 8400 A1 A2 A3 A4 3 7200 600 9000 I have been racking my head trying to figure...

Even Distribution Formula
Say I have 120 bowling players, their id_number in col_a, they all have different skill level, indicated by his avg_score in col_b I need to allot them into n teams (say 15), of equivalent strength on the TEAM level so no team ends up with mostly high-scorers and vic-versa. I'm not sure the title is right. How do you call this ? And does Excel 2003 has a formula foir this ? thanks! ID AVG_SCORE TEAM_NUMBER -------- ---------------- -------------------- 1 287 2 103 3 139 4 100 5 243 6 207 ...

formula to add a number to a long string **
I'm not quite sure how to best describe this: I need a formula that will take the example below and return the "final result" Col. A = 24.43.234.555 and Col B= 23 making Final Result = 24.43.234.578 Is there a way to do this? This seems to work: =LEFT(A1,LEN(A1)-3)&(RIGHT(TEXT(A1,"0"),3))+23 HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "MMangen" wrote: > I'm not quite sure how to best describe this: > > I need a formula that will take the e...

Something to help me keep track of formulas
Is there an Add-In or some third party software that can help me manage my formulas. They are getting very long and I can not figure out what goes where. I am looking for something free if possible. THanks. Here is an example of my formula that's too long. =IF(A12="Total ",SUM($I$11:$I11),IF(A12="","",IF(ISBLANK('PASTE ABCone'!F12),IF(((VLOOKUP(A12,'PASTE ABCone'!$A$11:$I$300,9,FALSE))*(VLOOKUP(A12,'%'!$A$9:$E$505,5,FALSE)))=0,"",ROUND((VLOOKUP(A12,'PASTE ABCone'!$A$11:$I$300,9,FALSE))*(VLOOKUP(A12,'%'...

Multiple "SUM IF" functions in one formula??
I know it can be done, Excel can do anything but I'm not sure if I can even explain it. For example, I want column D2 to display a qty IF a customer number equals a specific value AND a part number equals a specific value. Basically Column A is a list of part numbers, and Row 1 is a list of customers. I need to pull how many parts each customer has ordered. Hi see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "Dax" <dax.tipton@smc.com> schrieb im Newsbeitrag news:593e3ca7.0408301337.7b8f6a35@posting.google.com... > I ...