Remember Me?

#1
May 6th 09, 02:56 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: May 2009 Posts: 2
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
May 6th 09, 03:00 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2008 Posts: 143
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
May 6th 09, 03:15 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: May 2009 Posts: 2
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
May 6th 09, 03:19 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Mar 2009 Posts: 8,520
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
May 6th 09, 05:25 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2009 Posts: 222
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
May 6th 09, 05:32 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2009 Posts: 222
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
May 6th 09, 04:39 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2008 Posts: 143
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
June 24th 16, 03:04 PM
 Junior Member First recorded activity by ExcelBanter: Jun 2016 Posts: 3

Quote:
 Originally Posted by LeeLobb 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?
I have a similar problem but want the result to be constrained between two numbers . I can do the bottom and top constraints individually but cannot figure out how to combine the formulas. The formulas i have are...

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?

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Code Numpty Charts and Charting in Excel 2 November 19th 08 07:29 AM Louisq Excel Worksheet Functions 1 February 13th 07 03:36 PM RFJ Excel Worksheet Functions 1 August 19th 05 02:37 PM Carolan Excel Worksheet Functions 2 June 14th 05 06:05 PM jcliquidtension Excel Discussion (Misc queries) 2 February 23rd 05 08:43 PM

All times are GMT +1. The time now is 12:20 PM.