Formula Help - Perform a calculation only if the result falls between 2 values

[Matt.Russett]

I have 4 columns (Weight, Rate, Min Charge, Max Charge). I am looking
for a formula that will return the correct charge whether it be the
Weight*Rate calculation, Min Charge or the Max Charge.

If the Weight*Rate is less than the Min Charge then return the Min
Charge.
If the Weight*Rate is greater than the Max charge then return the Max
Charge.
If the Weight*Rate is between the Min Charge and the Max Charge return
the Weight*Rate calculation.

Sample Data:

Weight Rate Min Max
1280 $4.70 $89.00 $225.00
6598 $4.50 $124.00 $270.00

(1280 is in cell A2)

Thanks for your help!

 

[Guest]

Matt,

Try:-

=IF(A2*B2>D2,D2,IF(A2*B2<C2,C2,IF(AND(A2*B2>C2,A2*B2<D2),A2*B2,)))
Mike

 

[David Biddulph]

=MEDIAN(A2*B2,C2,D2)

 

[David Biddulph]

Note that Mike's formula will return the answer FALSE if A2*B2=C2 or if
A2*B2=D2. If you want to go down the route of using that sort of formula,
you could simplify it to =IF(A2*B2>D2,D2,IF(A2*B2<C2,C2,A2*B2)), but the
MEDIAN formula is a bit shorter.

 

[Guest]

=min(Max_charge, max(Min charge,Weight*rate))