If B1>b2 But <b3,b1????

[JEE]

I want excel to return a value to a cell in which there are 3 posible choices so I don't think an IF statement will work. Here is the background.

A customer's frieght rate is say 8% of the total sale but the customer has minimum freight of say $400, and a maximum rate of say $900.

I have established a hidden worksheet that has all of the customers freight cost and other data that varies from customer to customer, so I have 3 cells with the MIN MAX and frieght rate percentage in them. I also have a cell (call it B1) with the result of a formula - =total sale*fgt rate...

cells for discussion purposes are
B1(total sale*Fgt rate)
B2(MIN say 400)
B3(MAX say 900)

SO how do I....

IF B1<B2,B2
IF B2<B1,B2
IF B1>B2 BUT <B3,B1


Okay I am a man that has taught myself excel and not a real bright bulb so I'm not sure I explained this right. Can anyone help??

John

[Maistrye]

SO how do I....

IF B1<B2,B2
IF B2<B1,B2
IF B1>B2 BUT <B3,B1


Okay I am a man that has taught myself excel and not a real bright bulb so I'm not sure I explained this right. Can anyone help??

John

You're probably going to have to clarify slightly.

IF B1<B2, B2 -> This covers all situations when B1 < B2.
IF B2<B1, B2 -> This covers all situations when B1 > B2.

So basically, that covers everything except B1 = B2 (which you haven't mentioned and will need to decide on) and means that for all situations you want the value in B2.

Your third statement is:
IF B1>B2 BUT <B3,B1 -> This is basically B1 is between B2 and B3, correct?

Because your first two cover all situations except for equality, this last statement is probably the one you need to consider first.

Based on that, I'd suggest the following statement: (The reason I've just put B2 for the second value for the IF statement is because your first 2 statements (the way you have put them) cover all remaining circumstances.

= IF(AND(B2<B1,B1<B3),B1,B2)

If you meant that B1 had to be between and could equal B2 and B3:

= IF(AND(B2<=B1, B1<=B3),B1,B2)

If you've not expressed your IF statements the way you meant to, you'll probably have to re-state them.

Scott

[Maistrye]

Sorry, I should have looked at your data. Then I would have understood what you meant.

You want:

B1<B2 -> B2
B1>B3 -> B3
B1 Between B2 and B3 -> B1

Use:

=IF(B1<=B2, B2, IF(B1>=B3, B3, B1))

Scott

[JEE]

worked great except that formula is at the bottom of an "order form" so until the customer enters something in the order form this till returns a MIN(B2) value... but that's ok. Thanks for the help

John