Tiered Commission Structure (HELP)

First off, I appreciate all the help in advance. I certainly am no expert in Excel so this forum is greatly appreciated. That said, I am still struggling in creating the formula to calculate the commission on a per deal basis to the President and ASD (sales associate, if one is involved). The commission is structured in two different scenarios.

Scenarios 1 is the deal was handle solely by the President. As which point the president's commission is based on the tiered shown in Scenario one off the total JA cumulative commission. However, the commission rate is calculated based on the gross profit. For example: if there was no ASD involved, but the JA cumulative commission was $100,000, the president commission should be Gross Profit x 15% (scenario 1, 1st tier). Now if the JA cumulative commission was in equal to or greater than $250,001, but less than $500,000 and no ASD was involved, his commission would be Gross Profit x 25% (scenario 1, 2nd tier). The same would also apply for equal to or greater than $500,000.

Scenario 2 is when an ASD was also involved on the deal. At which point the tiered commission is based on Scenario 2. The Cumulative commission is based on the ASD's cumulative commission. For example, if there was an ASD involved, the percentage for the president, ASD, and JA are reflected in Scenario 2. Therefore, if the ASD's cumulative commission was under $250,000, the deal's commission for the President would be Gross Profit x 10%, the commission for the ASD would be Gross Profit x 25%. Now is the ASD's cumulative commission was $250,001 but less than $500,000, the President would be Gross Profit x 12.5%, and the commission for the ASD would be Gross Profit x 35%.

Attached is my spreadsheet with what the numbers should be for each. I am looking to automate the calculation based on whether there is an ASD or not and the cumulative commission.

I hope this helps and I look forward to you guys feedback.

If you are still looking for a solution, here it is:
C2=SUM(B$2:B2)
E2=IF(D2="Y",B2,0)
F2=IF(D2="Y",SUM(E$2:E2),0)
G2=$B2*IF($D2="N",VLOOKUP($C2,$K$4:$M$6,2,1),VLOOKUP($F2,$K$10:$N$12,2,1))
H2=$B2*IF($D2="N",0,VLOOKUP($F2,$K$10:$N$12,3,1))
I2=$B2*IF($D2="N",VLOOKUP($C2,$K$4:$M$6,3,1),VLOOKUP($F2,$K$10:$N$12,4,1))
copy down