1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

IF functions

Discussion in 'Business Applications' started by LTHAM, Oct 10, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. LTHAM

    LTHAM Thread Starter

    Joined:
    Oct 10, 2007
    Messages:
    2
    I need help with the following:

    If cell A1 is < L1, then the answer is cell A1, but

    If cell A1 is > K1 but < K2, then the answer is 100% of L1 plus 90% of (cell A1-L1), but

    If cell A1 is > K2 but < K3, then the answer is 100% of L1 plus 90% of L2 plus 84% of (Cell A1-L1-L2), but

    If cell A1 is > K3, then the answer is 100% of L1 plus 90% of L2 plus 84% of L3 plus 79% of (Cell A1-L1-L2-L3)


    Cell A1 is the variable

    K1=2400 L1=2400
    K2=4000 L2=1600
    k3=7143 L3=3143

    How do I do this? I'm supposing I use nested IF functions, but I cannot get it to work. I need this late tonight if at ALL possible. Thanks so much!

    Lisa
     
  2. lika2know

    lika2know

    Joined:
    Oct 10, 2007
    Messages:
    33
    This is a perfect candidate for nested queries, if a confusing one. To get it right, I recommend you use an outline sort of way to lay out your logic, then it comes together much easier. I recommend you actually start your logic design by setting it up this way:
    logic: a1 < l1
    true: a1
    false: logic: a1>k1
    true: logic: a1<k2
    true: (l1+(.9*(a1-l1))
    false: logic: a1>k3
    true: (l1+(.9*l2)+(.84*(a1-l1-l2))
    false: (l1+(.9*l2)+(.84*l3)+(.79(a1-l1-l2-l3))

    Important notes:
    (1) for the case you provide, k1 and l1 are the same and the others have known relationships. If you don't need a generalized tool, you could simplify the calculation, and
    (2) you don't account for equal to conditions in your current logic -- so if I had something that was exactly 2400, what would you do with it?

    This shows that you have accounted for all three states that you need for all the if statements...(except that you have to figure out where to make things >= or <= or you will have skipped several potential cases. (A good testing approach is to test for specific known values like 2400, 4000, 1600, etc. and make sure they are classified correctly; also test for negative values and very large values, just in case.)

    hope this helps.
    lika2know
     
  3. LTHAM

    LTHAM Thread Starter

    Joined:
    Oct 10, 2007
    Messages:
    2
    Here's the answer I found, if anyone is interested... (lika2know...i am adjusting for the =, thank you for that)

    =IF(A1<L1,A1,IF(AND(A1>K1,A1<K2),L1+0.9*(A1-L1),IF(AND(A1>K2,A1<K3),L1+0.9*L2+0.84*(A1-L1-L2),IF(A1>K3,L1+0.9*L2+0.84*L3+0.79*(A1-L1-L2-L3)))))
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi LTHAM, welcome to the board!

    Kudos on finding your answer, and even more kudos for posting it! Don't forget you can mark your thread as Solved by going to Thread Tools | Mark Solved | Perform Action.

    :)
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - functions
  1. Gevans0666
    Replies:
    5
    Views:
    270
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/636321

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice