Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formula / calculation problem (Excel 2000)

    I am new to calculations and I suspect I may be asking too much of Excel, could anybody please tell me if what I am doing is (a) possible and ([img]/forums/images/smilies/cool.gif[/img] if I am approaching it in the right way.

    I would like to publish a tariff in Excel for some staff members my fields would be : Destination - Minimum - -50kilos - +50 Kilos - +100 kilos - +300kilos and so on and then finally a cell for the weight of that shipment to be entered. Each destinations rates will be different and each set of rates will be different but I think I can over come that, howeverI try to write my formula it says something about a circular query.

    For simplicity I first tried (ignore whether the cell letters are right for now) =IF(J2<23,K2=J2*C2,""") eventually I will need it to say if the weight in J2 is <23 then K2 = B2 [which is the minimum].

    In a nutshell can I write a multiple calculation into one cell that can tell the difference between my weight bands, this formula is obviousely wrong otherwise I would not be asking but is this possible

    =IF(J2<23,K2=B2,""),IF(J2,>23<96,K2=J2*C2,""),IF(J 2>96<285,K2=J2*D2,"") I do hope this makes some sort of sense to somebody.


    TIA


    Steve

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: formula / calculation problem (Excel 2000)

    Studying up your formula, I assume you are entering it into K2.
    Try this editted version: =IF(J2<24,B2,IF(J2>23,J2*C2,IF(J2>96,J2*D2,"")))
    Your formula had some syntax errors that i cleared out!
    About Circular References; they occur if you create a formula say in A1, and you refer to cell A1 in the formula. Aformula cannot refer to the cell containing the formula. This creates a circular reference!

    Hope this helps!
    Regards,
    Rudi

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: formula / calculation problem (Excel 2000)

    You would put this in cell K2:
    =IF(J2<23,B2,IF(and(J2>23, j2<96),J2*C2,IF(and(J2>96,j2<285),J2*D2,"")))

    You circular reference is because you have D2 the cell reference in the cell. Note: If j2 = 23 or J2 = 96 or J2>=285 it will yield a null string (""), so you might want to adjust if that is not correct. (perhaps you want <=23 or >=23, etc)

    You will be limited to 7 IFs. so you might have to rethink. Using some type of Lookup can accomplish the same thing.
    John Walkenbach on his site gives some alternatives to these nested IFs

    Post back if you have further questions.
    Steve

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: formula / calculation problem (Excel 2000)

    Hi Steve,

    Check out the thread starting at <post#=403669>post 403669</post#>. It discusses various ways to address a related problem.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula / calculation problem (Excel 2000)

    Thanks everybody I will start working on that, also the reference to the post with the DLookup, I did download that and enabled macros but it did not seem to do anything.

    Cheers again


    Steve

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: formula / calculation problem (Excel 2000)

    Here is a reworked example if you need to evaluate between 2 specific ranges:
    =IF(J2<24,B2,IF(AND(J2>23,J2<96),J2*C2,IF(AND(J2>9 6,J2<285),J2*D2,"")))
    Enter it into cell K2
    Regards,
    Rudi

  7. #7
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula / calculation problem (Excel 2000)

    Thanks

    The answer from Steve 403774 seemed the easiest to use given my limited knowledge / ability and it works very well.

    Thanks a lot for all your answers


    Steve

  8. #8
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula / calculation problem (Excel 2000)

    Hi Steve, sorry its me again

    I have tried using your formula in one of my workbooks but I keep getting eroneous answers, would you mind awfully looking at my attached example to see why the calculations do not seem to behave as I believe they should.

    I also in the example tried another method from another poster (403781) by using the < and > operators, in row 6 of my example I tried the following (Excel would not let me save an incorrect formula therefore I have duplicated it here).

    =IF,I6<16,I6,B6,IF(AND(I6>15,I6<499,I6*C6,IF(AND(I 6>500,I6<999,I6*G6,IF(AND(I6>1000,I6<100000,I6*H6, ""))))))

    But it does not seem to want to know

    TIA

    Steve

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: formula / calculation problem (Excel 2000)

    Hi Stephen,
    It was just a matter of elimination.
    The formula started evaluating "IF(I2>23..... any other value incl. 101 was higher than 23, thus the formula stopped all other evaluations.
    Try this formula. I simply changed the values so it starts on the highest and evaluates down to smaller values.
    Copy this into cell J2:
    =IF(I2<24,B2,IF(I2>1000,I2*H2,IF(I2>500,I2*G2,IF(I 2>300,I2*F2,IF(I2>100,I2*E2,IF(I2>23,I2*C2,""))))) )
    Regards,
    Rudi

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: formula / calculation problem (Excel 2000)

    try this:
    =IF(I2<24,B2,IF(I2<100,I2*C2,IF(I2<300,I2*E2,IF(I2 <500,I2*F2,IF(I2<1000,I2*G2,I2*H2)))))

    Look at your eqn:
    =IF(I2<24,B2,IF(I2>23,I2*C2,IF(I2>100,I2*E2,IF(I2> 300,I2*F2,IF(I2>500,I2*G2,IF(I2>1000,I2*H2,""))))) )
    The IFs are solved left to right. If I2=102,
    It is not less than 24, so it goes to next one
    It >23 so it results in I2*C2. It never checks to see that if it is >100 or >300 , the IF checks that 101>23 so it does the "True part"

    Steve

  11. #11
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula / calculation problem (Excel 2000)

    Thanks everyone it is working fine now

    Steve

  12. #12
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula / calculation problem (Excel 2000)

    Sorry to be a pain but it has just dawned on me that with 100's of different entries all possibly with different minimums and different -50 rates it will take me years to keep writing the formulas for each one, trying to be bright I tried

    =IF(B2/I2<B2,B2,IF(B2/I2>B2,C2*I2,IF(I2>50,D2*I2 etc

    What I am trying to get itto do is to divide the minimum by the -50 rate, if that is less than the weight entered in I2 then J2 should show the minimum (B2), if the minimum divided by the weight is greater then j2 should show the weight X the value of C"

    hope that makes sense

    cheers


    Steve

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: formula / calculation problem (Excel 2000)

    I am not sure what you are after. Could you elaborate with some exmple data?

    =IF(B2/I2<B2,B2,IF(B2/I2>B2,C2*I2,IF(I2>50,D2*I2 etc

    You are not looking at the conditions again
    If I2>50 then B2/I2<B2 is true, so this condition (D2*I2) will never be calc'd, it will give B2

    Steve

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula / calculation problem (Excel 2000)

    Do you mean that you first want to test that the minimum (col [img]/forums/images/smilies/cool.gif[/img] divided by the -50 rate (col C) is at least the amount in the Weight column (col I)? Is that where the 24 comes from in your original question?

    And are you saying that you don't want to have to determine the minimum divided by the -50 rate for every shipment so that you don't have to calculate that and write it into each formula in every row? Is that what you're concerned will take forever to do?

    If so, just change the reference in the formula in row 2 from '24' to 'B2/C2'.

    Otherwise, I don't think I understand what you're wanting to do.

  15. #15
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula / calculation problem (Excel 2000)

    Hi Steve

    Thanks for your ongoing help. Please see the attached example and the following code which I have tried to do without any success

    =IF(B59<B59/I59,B59,IF(B59>B59<50,C59*I59,B59*C59,IF(I59>1000, H59*I59,IF(I59>500,G59*I59,IF(I59>300,F59*I59,IF(I 59>300,F59*I59,IF(I59>100,E59*E59,IF(I59>50,D59*I5 9,""))))))))

    If you look at the attachment you will see that each destination (A) has a minimum ([img]/forums/images/smilies/cool.gif[/img] with varying -50 kilo rates therefore if a customer had a shipment of 23 kilos going to a destination where the Minimum ([img]/forums/images/smilies/cool.gif[/img] is

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •