Home 
Search 
Today's Posts 
#1




How do I set a maximum value (e.g. cap at 200)
I have a formula set to multiply a rate by a quantity, I then need to cap the
total at 200 example: 42 people x 86 minutes x $0.05 = $180.60 but I need the total to cap at $200 when a different amount of people is entered and takes it above $200 make sense? 
#2




How do I set a maximum value (e.g. cap at 200)
Try this:
A B C D 42 86 .05 =IF(A1*B1*C1200,200,A1*B1*C1)  Regards, PJ Please rate this post using the vote buttons if it was helpful. "LeeLobb" wrote: I have a formula set to multiply a rate by a quantity, I then need to cap the total at 200 example: 42 people x 86 minutes x $0.05 = $180.60 but I need the total to cap at $200 when a different amount of people is entered and takes it above $200 make sense? 
#3




How do I set a maximum value (e.g. cap at 200)
That works exactly how I wanted it...thanks for your assistance!
"PJFry" wrote: Try this: A B C D 42 86 .05 =IF(A1*B1*C1200,200,A1*B1*C1)  Regards, PJ Please rate this post using the vote buttons if it was helpful. "LeeLobb" wrote: I have a formula set to multiply a rate by a quantity, I then need to cap the total at 200 example: 42 people x 86 minutes x $0.05 = $180.60 but I need the total to cap at $200 when a different amount of people is entered and takes it above $200 make sense? 
#4




How do I set a maximum value (e.g. cap at 200)
Suppose you have your values in A,B,C
=IF((a1*b1*c1)200,200,a1*b1*c1) If this post helps click Yes  Jacob Skaria "LeeLobb" wrote: I have a formula set to multiply a rate by a quantity, I then need to cap the total at 200 example: 42 people x 86 minutes x $0.05 = $180.60 but I need the total to cap at $200 when a different amount of people is entered and takes it above $200 make sense? 
#5




How do I set a maximum value (e.g. cap at 200)
You can eliminate the double evaluation with a simple MIN() formula:
=MIN((A1*B1*C1,200) This will always show the lower number...your formula or 200.  "Actually, I *am* a rocket scientist."  JB Your feedback is appreciated, click YES if this post helped you. "Jacob Skaria" wrote: Suppose you have your values in A,B,C =IF((a1*b1*c1)200,200,a1*b1*c1) If this post helps click Yes  Jacob Skaria "LeeLobb" wrote: I have a formula set to multiply a rate by a quantity, I then need to cap the total at 200 example: 42 people x 86 minutes x $0.05 = $180.60 but I need the total to cap at $200 when a different amount of people is entered and takes it above $200 make sense? 
#6




How do I set a maximum value (e.g. cap at 200)
You can eliminate the double evaluation with a simple MIN() formula:
=MIN((A1*B1*C1,200) This will always show the lower number...your formula or 200.  "Actually, I *am* a rocket scientist."  JB Your feedback is appreciated, click YES if this post helped you. "LeeLobb" wrote: That works exactly how I wanted it...thanks for your assistance! "PJFry" wrote: Try this: A B C D 42 86 .05 =IF(A1*B1*C1200,200,A1*B1*C1)  Regards, PJ Please rate this post using the vote buttons if it was helpful. "LeeLobb" wrote: I have a formula set to multiply a rate by a quantity, I then need to cap the total at 200 example: 42 people x 86 minutes x $0.05 = $180.60 but I need the total to cap at $200 when a different amount of people is entered and takes it above $200 make sense? 
#7




How do I set a maximum value (e.g. cap at 200)
Good point.
 Regards, PJ Please rate this post using the vote buttons if it was helpful. "JBeaucaire" wrote: You can eliminate the double evaluation with a simple MIN() formula: =MIN((A1*B1*C1,200) This will always show the lower number...your formula or 200.  "Actually, I *am* a rocket scientist."  JB Your feedback is appreciated, click YES if this post helped you. "Jacob Skaria" wrote: Suppose you have your values in A,B,C =IF((a1*b1*c1)200,200,a1*b1*c1) If this post helps click Yes  Jacob Skaria "LeeLobb" wrote: I have a formula set to multiply a rate by a quantity, I then need to cap the total at 200 example: 42 people x 86 minutes x $0.05 = $180.60 but I need the total to cap at $200 when a different amount of people is entered and takes it above $200 make sense? 
#8




Quote:
Bottom =MAX(350,(A1*0.15%)) Top =MIN(1500,(A1*0.15%)) essentailly i want the result to be constrained between 350 and 1500 when multiplying cell A1 by 0.15%. Any suggestions? thanks in advance 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Return Maximum from Column directly above Maximum in Row  Charts and Charting in Excel  
Maximum  Excel Worksheet Functions  
Maximum If Help  Excel Worksheet Functions  
MAXIMUM VALUE  Excel Worksheet Functions  
Specify Maximum Value  Excel Discussion (Misc queries) 