# IF functions

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

Not open for further replies.

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

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

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

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