| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create formula-if value A falls between values B and C in time for | Excel Discussion (Misc queries) | |||
| How do I perform calculation on Filtered data | Excel Discussion (Misc queries) | |||
| formula result #value! needs to equal zero for average calculation | Excel Worksheet Functions | |||
| comparing two columns & then perform a calculation | Excel Worksheet Functions | |||
| Perform functions on the result of adding two columns | Excel Worksheet Functions | |||
Linear Mode
