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

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!

Formula Help - Perform a calculation only if the result falls betw
 

Matt,

Try:-

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

"Matt.Russett" wrote:

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!

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

=MEDIAN(A2*B2,C2,D2)
--
David Biddulph

"Matt.Russett" wrote in message
ps.com...
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!

Formula Help - Perform a calculation only if the result falls betw
 

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*B2D2,D2,IF(A2*B2<C2,C2,A2*B2)), but the
MEDIAN formula is a bit shorter.
--
David Biddulph

"Mike H" wrote in message
...
Matt,

Try:-

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

"Matt.Russett" wrote:

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!

Formula Help - Perform a calculation only if the result falls betw
 

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

"Matt.Russett" wrote:

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!