How to set up a Sliding Commission Calculator Formula

[Jaethomp]

I am trying to set up a formula for commission calculator to a cost workbook I have.


Contract Price - $ 20,808.00


Actual Cost - $ 17,481.89

Gross Profit - $ 3,326.11

Gross Margin - 16%

I would like for the commission to calculate if the gross margin is between 5% - 10% the commission pay out would be 10% of the gross profit. If gross margin is between 10-15% the commission pay out will be 12% of the gross profit. If the gross profit is greater than 15% the commission pay out will be 12% of up to 15% and 50% of the amount greater than the 15%


Any help or suggestions is appreciated.

 

[AhoyNC]

Something like this?

Excel Workbook
	A	B
2	Gross Profit	$ 3,326.11
3	Gross Margin	16%
4
5	Commission	$1,663.06
Sheet