Do a favour please. Its about if functionClosed

Question

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.

rizvisa1 · Answer

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

Do a favour please. Its about if function

1 response

Similar discussions

Subscribe To Our Newsletter!