Bonus Calculation Spreadsheet

[JenMcG]

Hello,

I need help creating a formula that will calculate a bonus payout based on the employees sales margin. The % of payout of job sale is determined by what % they achieve on the job margin (the higher the margin, the greater the % payout of the sale).

For example, if the job achieved a margin of 50.64% on a sale of $2,879.58, then the employee would be paid 2% of the sale (or $57.59) base on the below criteria:

Margin % (Column BE)	% of Total Sales
60% & greater	2.5%​
50% to 59.99%	2.0%​
40% to 49.99%	1.5%​
30% to 39.99%	1.0%​
25% to 29.99%	0.5%​
Less than 25%	0.0%​

I'd like for the spreadsheet to be plug & play where I can dump data into it and it auto determines the % bonus without me having to manually complete this.

I can provide a sample of the spreadsheet if required.

Thank you,

 

[Dave Patton]

You can paste the following example to a clean sheet and review the formula.

T202311a.xlsm
	A	B	C	D
1
2	Margin % (Column BE)	Margin	% of Total Sales
3	Less than 25%	0	0.00%
4	25% to 29.99%	0.25	0.50%
5	30% to 39.99%	0.3	1.00%
6	40% to 49.99%	0.4	1.50%
7	50% to 59.99%	0.5	2.00%
8	60% & greater	0.6	2.50%
9
10
11		Sale	Margin	Bonus
12		$2,879.58	50.64%	57.59
13
4d

Cell Formulas
Range	Formula
D12	D12	=B12*LOOKUP(C12,B3:C8)