Nested IF Functions

Hi,

The issue I am experience involves vested IF statements. I have been able to have the function work in 2 out of 3 situations but fails in that final 1 situation. The problem is a commission accelerator problem. To give some background a rep receives a certain % of commission on GP up to a certain level and than it accelerates and continues to do so. In th example I provided the rep has 2 accelerator levels, 1st provides a rate at 1.5x his base rate of 3% and 2nd rate is 2x his base rate of 6 %. Also, his range for his first accelerator would be his GP quota 4,200,000 and 5,040,000 (120/100*Quota)

I've attached a clear example of my issue. and the cells Im looking to solve are AO-AT. The rest of the document is correct. As you will see AO works however, AP fails when the rate isnt in the 2nd accelerator yet. Ive also changed to the formula around but it failed to calculate effective when the individual is at the top accelerator listed as 9999999

Thanks for taking a look and seeing if you can add the final piece to my formula

Originally Posted by MarvinP

Hi,
In reading your post I remembered step or piecewize linear functions back in school. The idea was if you invest less than 20k you get charged 3%, between 20k and 50k the charge is 2% and above 50k the charge is 1%. I believe your problem is similar to this and you are correct to set it up with If statements. My best suggestion is to lear the Evaluate Function Tool in Excel and step throught he calculation to see where it fails. See http://office.microsoft.com/en-us/ex...010066254.aspx

I hope this helps

Unfortunately I still havent been able to resolve it. The problem is the if statementcontinues to give me False in specific situation, and when I change it around to solve that issue it creates another hole in the formula

any help is appreciated .

bump.. still would love help with this, I know someone is smart enough to solve the if statement problem

thanks!