IF STATEMENT

geralde

New Member
Joined
Mar 31, 2003
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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)
 
Upvote 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!
 
Upvote 0
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
 
Upvote 0
Ok.

Try:

=MIN(MAX(B2*VLOOKUP(A2,{"Inner",0.15;"Outer",0.2;"Fringe",0.4},2,0),3000),5000)
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
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.
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
Back
Top