Multiple IF/AND calculations with function

[tdlpps]

First post. Great site. I had no interest in spreadsheets or math during college or in the first few years of my career—English major. However, about fifteen years ago my father—an accountant—showed me a few things using Quattro Pro, and the power of a good spreadsheet program was revealed.



Since, I began managing dealership Service Departments and my experience and knowledge has grown. I am, however, stumped with the development of a formula. I wish to calculate the total pay figures for my employees based on their commissions. I have tried several different combinations of IF and IF/AND code writing, but nothing so far has worked. The attached image below shows the worksheet:


What I wish to do is calculate is the payable percentage of CP sales (cell F13). The pay is based on several qualifiers. Written in text, I wish to calculate as follows:



If the payable total (F9) is less than $109,000 and (F11) is equal to or greater than 2.5, pay percentage is 5.5% (E29).



If the payable total (F9) is less than $109,000 and (F11) is less than 2.5, pay percentage is 4.75% (D29).



If the payable total (F9) is $109,000 to $134,999 and (F11) is equal or greater than 2.5, pay percentage is 5.75% (E30).



If the payable total (F9) is $109,000 to $134,999 and (F11) is less than 2.5, pay percentage is 5.0% (D30).



If the payable total (F9) is $135,000 to $159,999 and (F11) is equal to or greater than 2.5, pay percentage is 6.0% (E31).



If the payable total (F9) is $135,000 to $159,999 and (F11) is less than 2.5, pay percentage is 5.25% (D31).



If the payable total (F9) is $160,000 to $184,999 and (F11) is equal to or greater than 2.5, pay percentage is 6.25% (E32).



If the payable total (F9) is $160,000 to $184,999 and (F11) is less than 2.5, pay percentage is 5.5% (D32).



If the payable total (F9) is greater than $184,999 and (F11) is equal to or greater than 2.5, pay percentage is 6.5% (E33).



If the payable total (F9) is greater than $184,999 and (F11) is less than 2.5, payable percentage is 5.75% (E34).



I have written dozens of combinations of codes in trying to come up with the formula, but nothing has worked properly, and some exceeded the total number of Excel’s IF formula capacity. I could just look at the chart and manually type in the correct percentage, but there is no fun in that. It's much cooler to find a work through to automate the calculation.



So, does anyone have an answer or suggestions on how to calculate this percentage? Thanks in advance for any help you can offer.