Help with Commission Log

[atxmichael]

I'm developing a sales/commission tracking log for my company, the problem I have run into is this:

I'm trying to calulate the projected bonus for my sales reps based on the accessory sales. When the accessory sales are within the following ranges it needs display the corresponding bonus, also their margin needs to be 65% or higer if not the bonus will be $0.00


$4000 = $400 BONUS (65% MIN MARGIN)
$3500 = $350 BONUS (65% MIN MARGIN)
$3000 = $300 BONUS (65% MIN MARGIN
$2500 = $250 BONUS (65% MIN MARGIN)
$2000 = $200 BONUS (65% MIN MARGIN)
$1500 = $150 BONUS (65% MIN MARGIN)
$1000 = $75 BONUS (65% MIN MARGIN)

I'm new to all this and I'm trying learn as I go, your help is greatly appreciated.

Thank You,
Michael Potter

If I left anything out let me know

 

[Smitty]

Welcome to the Board!

Follow this link to Colo's HTML Maker, which will allow you to post a shot of your sheet. VLOOKUP (advice found in the Excel helpfile) may help, but for multiple criteria you may need a combination of INDEX/MATCH or SUMPRODUCT. When you post back with an example someone will get you an answer!

Have a great weekend!

Smitty

P.S. and throw in your margin calculation if you can; it might add an extra variable.

 

[atxmichael]

does this help?

TRACKING.xls
	B	C	D	E	F	G	H	I	J
22					ProjAcc	ProjAcc	ProjAcc	ProjAcc	ProjBonus
23	Name	AccCost	AccSales	AccProfit	Sales	Profit	Margin	Comm	Comm
24	MichaelPotter	$326.58	$2,359.68	$2,033.10	$2,359.68	$2,033.10	86%	$304.97
Sheet1