Multiple IF statements

davidanaya11579

New Member
Joined
Dec 26, 2017
Messages
11
I need to add an additional IF statement to a cell where I already have an IF statement. I need to add an additional IF statement to the below that states if A31="Destination Handling" then set a minimum limit value of 100 and a maximum limit value of 550 to P31, otherwise, use the below IF statement.

=NUMBERVALUE(IF(E31="per unit",K31*H31,H31))

Any ideas?
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try

=MIN(IF($A$31="Destination Handling",550,9.99999999999999E+307),MAX(IF($A$31="Destination Handling",100,-9.99999999999999E+307),NUMBERVALUE(IF(E31="per unit",K31*H31,H31))))
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
It works for me

In example below, If I change A31 to Destination Handling, then
M31 changes to the maximum value 550 and M32 changes to the minimum value 100

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>E</th><th>H</th><th>K</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">31</td><td style=";">Other</td><td style=";">Per Unit</td><td style="text-align: right;;">25</td><td style="text-align: right;;">100</td><td style="text-align: right;;">2500</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">32</td><td style="text-align: right;;"></td><td style=";">Per Unit</td><td style="text-align: right;;">2</td><td style="text-align: right;;">10</td><td style="text-align: right;;">20</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M31</th><td style="text-align:left">=MIN(<font color="Blue">IF(<font color="Red">$A$31="Destination Handling",550,9.99999999999999E+307</font>),MAX(<font color="Red">IF(<font color="Green">$A$31="Destination Handling",100,-9.99999999999999E+307</font>),NUMBERVALUE(<font color="Green">IF(<font color="Purple">E31="per unit",K31*H31,H31</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M32</th><td style="text-align:left">=MIN(<font color="Blue">IF(<font color="Red">$A$31="Destination Handling",550,9.99999999999999E+307</font>),MAX(<font color="Red">IF(<font color="Green">$A$31="Destination Handling",100,-9.99999999999999E+307</font>),NUMBERVALUE(<font color="Green">IF(<font color="Purple">E32="per unit",K32*H32,H32</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,127,342
Messages
5,624,114
Members
416,012
Latest member
rockermom59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top