If statement with Min-Max values

[vjwd1500]

This is my formula, it works
=IF((B2)<=500,B2*0.19,IF(AND((B2)>=1000,(B2)<2000),B2*0.15,IF(AND((B2)>=2000,(B2)<5000),B2*0.12,B2*0.09)))

Now I need to add a Min and a Max value
min=34.80
Max=459

How do I incorporate the min and max values into the formula above?

Please help.....

 

[MrKowz]

Welcome to the forums!

You might be able to easier accomplish this with a LOOKUP formula:

What are your thresholds and their accompanying values - from what I can tell they are the below? And how does the MIN/MAX come into play with this?

0-499.99999 = B2*.19
1000-1999.99999 = B2*.15
2000-4999.99999 = B2*.12
5000+ = B2*.09

 

[Aladin Akyurek]

This is my formula, it works
=IF((B2)<=500,B2*0.19,IF(AND((B2)>=1000,(B2)<2000),B2*0.15,IF(AND((B2)>=2000,(B2)<5000),B2*0.12,B2*0.09)))

This can be shortened by invoking a LOOKUP formula instead:

=B2*LOOKUP(B2,{0;500;1000;2000},{0.19;0.09;0.15;0.12})

If so desired, a table can be set up instead of constant arrays.

Now I need to add a Min and a Max value
min=34.80
Max=459

How do I incorporate the min and max values into the formula above?

Please help.....

Would you elaborate on what you mean incorporating those values?

 

[vjwd1500]

Thank you-

This is the table I need to use- I have no problem with changing the formula- the numbers are weights- so if a shipment weighs 550, the formula would do 550*.19. I have a cell (b2) where you enter the weight. This formula needs to be tell me how much I will pay.


<table style="width: 273px; height: 288px;" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" span="2" width="64"> <tbody><tr style="height:15.75pt" height="21"> <td class="xl84" style="height:15.75pt;width:48pt" height="21" width="64">Min</td> <td class="xl85" style="width:48pt" width="64"> $ 34.80 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl84" style="height:15.0pt" height="20">100
</td> <td class="xl85"> $ 0.19 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl84" style="height:15.0pt" height="20">500
</td> <td class="xl85"> $ 0.19 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl84" style="height:15.0pt" height="20">1000
</td> <td class="xl85"> $ 0.15 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl84" style="height:15.0pt" height="20">2000
</td> <td class="xl85"> $ 0.12 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl84" style="height:15.0pt" height="20">3000
</td> <td class="xl85"> $ 0.12 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl84" style="height:15.0pt" height="20">5000
</td> <td class="xl85"> $ 0.09 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl84" style="height:15.0pt" height="20">MAX</td> <td class="xl85"> $ 459.00 </td> </tr> </tbody></table>

 

[Aladin Akyurek]

Thank you-

This is the table I need to use- I have no problem with changing the formula- the numbers are weights- so if a shipment weighs 550, the formula would do 550*.19. I have a cell (b2) where you enter the weight. This formula needs to be tell me how much I will pay.


<table style="width: 273px; height: 288px;" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" span="2" width="64"> <tbody><tr style="height:15.75pt" height="21"> <td class="xl84" style="height:15.75pt;width:48pt" height="21" width="64">Min</td> <td class="xl85" style="width:48pt" width="64"> $ 34.80 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl84" style="height:15.0pt" height="20">100
</td> <td class="xl85"> $ 0.19 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl84" style="height:15.0pt" height="20">500
</td> <td class="xl85"> $ 0.19 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl84" style="height:15.0pt" height="20">1000
</td> <td class="xl85"> $ 0.15 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl84" style="height:15.0pt" height="20">2000
</td> <td class="xl85"> $ 0.12 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl84" style="height:15.0pt" height="20">3000
</td> <td class="xl85"> $ 0.12 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl84" style="height:15.0pt" height="20">5000
</td> <td class="xl85"> $ 0.09 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl84" style="height:15.0pt" height="20">MAX</td> <td class="xl85"> $ 459.00 </td> </tr> </tbody></table>

What do I pay if my shipment weight is 20 and when it is 6000? I'm trying to figure out what min an max values mean here...

 

[vjwd1500]

Thank Aladin

I can not pay less than $34.80 and more than $459. In your example at 6000, that is over the max, 6000*.09=$540, so the max amount would apply.

If 20 then the min will apply, 20*.19=3.80. I can not pay less than $34.80

Thank you for trying to help me.

 

[Scott Huish]

Your table only needs to include the MIN and MAX and the low end of where the values change:

Excel Workbook
	B	C	D	E	F	G	H	I
1							Min	$ 34.80
2	99	$ 34.80					100	$ 0.19
3							1000	$ 0.15
4							2000	$ 0.12
5							5000	$ 0.09
6							Max	$ 459.00
Sheet1

 

[Aladin Akyurek]

Thank Aladin

I can not pay less than $34.80 and more than $459. In your example at 6000, that is over the max, 6000*.09=$540, so the max amount would apply.

If 20 then the min will apply, 20*.19=3.80. I can not pay less than $34.80

Thank you for trying to help me.

Create the following table on a separate sheet, say, Sheet1, in E2:F7...

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 16.8pt" height=22><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #e6e6e6; WIDTH: 48pt; HEIGHT: 16.8pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" id=td_post_2748115 class=xl65 height=22 width=64 align=right>100</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #e6e6e6; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 width=64 align=right>$0.19 </TD></TR><TR style="HEIGHT: 16.8pt" height=22><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #e6e6e6; WIDTH: 48pt; HEIGHT: 16.8pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 height=22 width=64 align=right>500</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #e6e6e6; WIDTH: 48pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 width=64 align=right>$0.19 </TD></TR><TR style="HEIGHT: 16.8pt" height=22><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #e6e6e6; WIDTH: 48pt; HEIGHT: 16.8pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 height=22 width=64 align=right>1000</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #e6e6e6; WIDTH: 48pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 width=64 align=right>$0.15 </TD></TR><TR style="HEIGHT: 16.8pt" height=22><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #e6e6e6; WIDTH: 48pt; HEIGHT: 16.8pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 height=22 width=64 align=right>2000</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #e6e6e6; WIDTH: 48pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 width=64 align=right>$0.12 </TD></TR><TR style="HEIGHT: 16.8pt" height=22><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #e6e6e6; WIDTH: 48pt; HEIGHT: 16.8pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 height=22 width=64 align=right>3000</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #e6e6e6; WIDTH: 48pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 width=64 align=right>$0.12 </TD></TR><TR style="HEIGHT: 16.8pt" height=22><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #e6e6e6; WIDTH: 48pt; HEIGHT: 16.8pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 height=22 width=64 align=right>5000</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #e6e6e6; WIDTH: 48pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 width=64 align=right>$0.09 </TD></TR></TBODY></TABLE>

Name E2:E7 WEIGHTS and F2:F7 PRICES. You can name
such ranges via the Name Box on the Formula Bar.

Now we can invoke:

=IF(B2 < MIN(WEIGHTS), 34.8, 
    IF(B2 > MAX(WEIGHTS), 459, 
      B2 * LOOKUP(B2, WEIGHTS, PRICES)))

 

[vjwd1500]

WOW- I am so impressed with all your help! Thank you so much. have a great weekend