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? |
Answer: How do I set a maximum value (e.g. cap at 200)
Yes, that makes sense! To set a maximum value of 200 for your formula result, you can use the MIN function in Excel. Here's how:
That's it! Now your formula will always return a value that is capped at 200. |
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? |
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? |
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? |
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? |
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? |
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? |
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 |
All times are GMT +1. The time now is 10:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com