Do a favour please. Its about if function

Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I am trying to write an IF() function with more than 8 conditions (to be exact I will almost have 50) I understand that excel 2007 allows for 64 but my company is not intending to upgrade anytime soon. Here is an example of what I want the function to perform.

=(IF(B3>2000,".5%",IF(B3>2500,".75%",IF(B3>3000,"1.5%",IF(B3>3500,"2.5%",IF(B3>4500,"3.5%",IF(B3>5500,"4.5%",IF(B3>6500,"5.5%",IF(B3>7500,"6.5%","0"))))))))) Similarly i have 15 conidtions.

It is about sale and commission percentage. if a the monthly sale of a sale man is US $ 200 he deserve for .5% commission of that amount, if upto US $ 2500 he deserve .75% commission of that amount and so on. But the problem is that i have 15 conditions. How can i cope with this matter
Please help me in this regard. I'll be really thankfull to you from the inner core of my heart.

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
you need to write a user defined function to handle that

Not sure if your if statement was correct, as B3>2000 appears before B3>6500.

Assuming you if statement is not correct, this is a sample function. you would call if like

=myLousyIF(b3)

Public Function myLousyIF(myCell As Range) As String
   
   Select Case myCell.Value
      Case Is > 7500
         myLousyIF = "6.5%"
      Case Is > 6500
         myLousyIF = "5.5%"
      Case Is > 5500
         myLousyIF = "4.5%"
      Case 4500
         myLousyIF = "3.5%"
      Case Is > 3500
         myLousyIF = "2.5%"
      Case Is > 3000
         myLousyIF = "1.5%"
      Case Is > 2500
         myLousyIF = ".75%"
      Case Is > 2000
         myLousyIF = ".5%"
      Case Else
         myLousyIF = "0"
   End Select
End Function
0