Do a favour please. Its about if function
Closed
Ali
-
Jan 11, 2011 at 07:45 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jan 19, 2011 at 09:29 AM
rizvisa1 Posts 4478 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.
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.
Related:
- Do a favour please. Its about if function
- Accessor function c++ - Guide
- Find function on mac - Guide
- Spreadsheet function - Guide
- Network card function - Guide
- Hard drive function - Guide
1 response
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jan 19, 2011 at 09:29 AM
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)
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
