Multiple If conditions, AND formula help needed

[jilaba]

Kindly refer attachment SalesTGT.xls

I have 3 cells B2, B5, D5 with some values. I like to get results in cell F4 based on following conditions.

conditions
1. If B2>B5 & D5="NEW", F4 = 0, If B2>B5 & D5="OLD", F4 = B2-B5
2. If B2<B5 & D5="NEW", F4 = B5-B2, If B2<B5 & D5="OLD", F4 = 0

I wish to get both conditions solved in one formula.

I tried nested if method and unable to get desired results.
someone pls help me.

[WideBoyDixon]

Is that the correct attachment? B5 is blank, D5 is blank, B2 has the value 20000. Also, will D5 *always* be "NEW" or "OLD" - no other values? Also what happens if B2=B5?

WBD

[JohnTopley]

Posted the wrong file (??) as data does not match your requirements.

[jilaba]

Is that the correct attachment? B5 is blank, D5 is blank, B2 has the value 20000. Also, will D5 *always* be "NEW" or "OLD" - no other values? Also what happens if B2=B5?

WBD

Thanks for your time. I corrected and uploaded again.
D5 will either be NEW or OLD only. B2 will never be equal to B5. Both D5 & B2 will be having value restricted entries.

[jilaba]

Posted the wrong file (??) as data does not match your requirements.

sorry, I posted right file now. Thanks.

[WideBoyDixon]

I'm going to have to assume you mean B1, not B2:

Please Login or Register  to view this content.

WBD

[jilaba]

I'm going to have ...

WBD

You are right, ITs B1 only. Sorry for that. It solved my first condition thing only. Thanks

[JohnTopley]

Try

=IF(D5="NEW",MAX(0,B5-B1),MAX(0,B1-B5))

[Bo_Ry]

Maybe try

=MAX(0,(B1-B5)*(2*(D5="old")-1))

or
=MAX(0,IF(D5="Old",B1-B5,B5-B1))

Work.jpg

[jilaba]

Try ...

Thanks for your time., But not workng.

[jilaba]

I'm going to have to;;;;;; WBD

It solved my condition 1. For condition 2, i do not know to club it with condition 1's formula. Pls help me. Thanks

[jilaba]

Maybe try

Thanks, but not working.

[Glenn Kennedy]

Try this...

=IF(AND(B2>B5,D5="Old"),B2-B5,IF(AND(B2<B5,D5="Old"),0,IF(AND(B2>B5,D5="New"),0,IF(AND(B2<B5,D5="New"),B5-B2,0))))

[JohnTopley]

What is not working ????

B1=20000 B5=14000, D5="NEW" , F4=0: D5=OLD"", F5=6000 (B1> B5)

b1=12000, b5=14000, D5="NEW", f4=2000, d5="OLD", F4=0 (B1 < B5)

[Glenn Kennedy]

JT... I think part of the story is missing. We'll see....

[jilaba]

Try this...

Thanks a lot, it solved my thing.

[jilaba]

Maybe try ...

Extremly sorry, It was so simple and solved. In fact - i overlooked it, as i was looking for a complex lengthy formula. Great.

[jilaba]

Thanks all of you for your help, it was solved.

[jilaba]

What is not working ????

B1=20000 B5=14000, D5="NEW" , F4=0: D5=OLD"", F5=6000 (B1> B5)

b1=12000, b5=14000, D5="NEW", f4=2000, d5="OLD", F4=0 (B1 < B5)

Thank you. It solved, sorry.