# Do a favour please. Its about if function

Closed
Ali - Jan 11, 2011 at 07:45 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jan 19, 2011 at 09:29 AM
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 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jan 19, 2011 at 09:29 AM
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```