# IF STATEMENT

#### geralde

##### New Member
I have this spreadsheet and i need to insert a IF statement that says if the location is Inner, then it would be the current amount multiplied by 15% however, if the result is is less than 3000 then it is 3000 but if it is more than 5000 then insert 5000 but if the result is between 3000 and 5000 then it is 15% multiplied by the current amount. I need to repeat the same thing for Outer which will be the current amount multiplied by 20% and also Fringe which will be the current amount multipled by 40%

Location Current Amount
Inner 276.2
Outer 279.31
Inner 690.5
Inner 552.4
Inner 883.84
Inner 552.4
Inner 552.4
Outer 328.6
Inner 690.5
Inner 911.46
outer 410.75
outer 410.75
Inner 2458.18
Outer 2762
Fringe 2762
Fringe 2762
Fringe 2762
Fringe 2762

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hi,

Not sure I completely get your explanation but you could try:

=MIN(MAX(B2,3000),5000)*VLOOKUP(A2,{"Inner",0.15;"Outer",0.2;"Fringe",0.4},2,0)

You might want to consider a User Defined Function in this case since it would simplify things greatly. Assuming that your data above is in Col A and B, then it would be simply: =YourUDF(A1, B1), etc.

If you are interested in that angle, post again and we can help you. Assuming you want to use a nested IF, try the following realizing that it is going to be pretty ugly:
Code:
``=IF(ISERROR(FIND("inner",LOWER(A14))),IF(ISERROR(FIND("outer",LOWER(A14))),IF(ISERROR(FIND("fringe",LOWER(A14))),"Check",IF(B14*1.4<=3000,3000,IF(B14*1.4>=5000,5000,B14*1.4))),IF(B14*1.2<=3000,3000,IF(B14*1.2>=5000,5000,B14*1.2))),IF(B14*1.15<=3000,3000,IF(B14*1.15>=5000,5000,B14*1.15)))``
Embedded in the middle is a Check in case it was anything other than Inner, Outer or Fringe. I also used Lower throughout to protect you from inconsistent data (as your example had some).

One of the reasons it is so ugly is because the Find function returns an Error instead of 0 if the string is not found, so you have to check IsError instead - eg =IF(IsError(a),True,False). So if you do not have an error condition, it is on the false side instead. Good Luck!

Location (Col A) Current Amount (Col B) New Amount (Col C)
Inner 276.2
Outer 279.31
Inner 690.5
Inner 552.4
Inner 883.84
Inner 552.4
Inner 552.4
Outer 328.6
Inner 690.5
Inner 911.46
outer 410.75
outer 410.75
Inner 2458.18
Outer 2762
Fringe 2762
Fringe 2762
Fringe 2762
Fringe 2762

Thanks for your messages but probably I did not explain myself properly. My apologies. Anyway what I need is in Col C, if Col A is Inner, then it would be Col B*0.15. If the result is less than 3000, then insert 3000 in Col C, if the result is more than 5000 then insert 5000, if however the result is between 3000 and 5000 then insert Col*0.15.

If however Column A is Outer then in Col C would be Col B*0.2. If the result is less than 3000, then insert 3000 in Col C, if the result is more than 5000 then insert 5000, if however the result is between 3000 and 5000 then insert Col*0.2

If however Column A is Fringe then in Col C would be Col B*0.4. If the result is less than 3000, then insert 3000 in Col C, if the result is more than 5000 then insert 5000, if however the result is between 3000 and 5000 then insert Col*0.4

Ok.

Try:

=MIN(MAX(B2*VLOOKUP(A2,{"Inner",0.15;"Outer",0.2;"Fringe",0.4},2,0),3000),5000)

Many Thanks for this. I tried the formula but it is only giving me the minimum and maximum but not the answer if the calculation falls between these two figures.

Try this User Defined Function. Copy it into a standard module and then invoke it as below. Tools, Macro, Visual Basic Editor will get you there. Then click Insert, Module to get a blank module for the code:
Code:
``````Public Function CalcValue(ByVal Location As Variant, _
ByVal CurrentAmt As Variant) As Variant
'Convert location range object (eg A4)
If IsObject(Location) Then
If TypeName(Location) = "Range" Then
Location = UCase\$(Location.Value)
Else
Exit Function
End If
ElseIf TypeName(Location) = "String" Then
Location = UCase\$(Location)
Else
Exit Function
End If
'Convert current amount range object (eg B4)
If IsObject(CurrentAmt) Then
If TypeName(CurrentAmt) = "Range" Then
CurrentAmt = CurrentAmt.Value
Else
Exit Function
End If
ElseIf IsNumeric(CurrentAmt) Then
CurrentAmt = Val(CurrentAmt)
Else
Exit Function
End If
Select Case Location
Case "INNER"
CurrentAmt = CurrentAmt * 1.15
Case "OUTER"
CurrentAmt = CurrentAmt * 1.2
Case "FRINGE"
CurrentAmt = CurrentAmt * 1.4
Case Else
MsgBox "Invalid location: " & Location, vbInformation
Exit Function
End Select
If CurrentAmt < 3000 Then
CalcValue = 3000
ElseIf CurrentAmt > 5000 Then
CalcValue = 5000
Else
CalcValue = CurrentAmt
End If
End Function 'CalcValue``````
You would invoke it like this:
Code:
``=CalcValue(A2,B2)``

Replies
7
Views
404
Replies
3
Views
266
Replies
0
Views
335
Replies
0
Views
220
Replies
12
Views
633

### Forum statistics

1,203,379
Messages
6,055,094
Members
444,761
Latest member
lneuberger ### 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.

### Which adblocker are you using?    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

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