Calculating a value based on multiple criteria

[tsmith0813]

I am working on building a formula that calculates a bonus based on multiple criteria: number of units, total volume and fixed cost per unit. I can easily create one based on the volume and fixed cost per unit, but adding the units portion is giving me difficulty. Below is the chart in which the bonus is calculated. Any help with this formula would be highly appreciated!!!

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Fixed Cost[/TD]
[TD="align: center"]Volume[/TD]
[TD="align: center"]Volume[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]$500,000 -$1,500,000[/TD]
[TD]$1,500,000 - $3,000,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]4 - 8 Units[/TD]
[TD="align: center"]9 - 15 Units[/TD]
[/TR]
[TR]
[TD]$2,000 - $1,000[/TD]
[TD="align: center"]$2,300[/TD]
[TD="align: center"]$4,300[/TD]
[/TR]
[TR]
[TD]$1,000 - $0[/TD]
[TD="align: center"]$4,000[/TD]
[TD="align: center"]$5,500[/TD]
[/TR]
</tbody>[/TABLE]

So if a salesman had a total of $1,900,000 in volume, 5 units and $1,200 in fixed cost they would receive $2,300.

Below is the spreadsheet for calculating the bonus.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Manager[/TD]
[TD="align: center"]Unit[/TD]
[TD="align: center"]Volume[/TD]
[TD="align: center"]Fixed Cost[/TD]
[TD="align: center"]Bonus[/TD]
[/TR]
[TR]
[TD="align: center"]Jim Jones[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1,900,000[/TD]
[TD="align: center"]1,200[/TD]
[TD="align: center"]$2,300[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

Welcome to the forum.

I can't quite see what you're getting at. If a salesman had 5 units, he would be in Column1 yet he would be in Column2 for volume of 1.9mm. Please provide a longer list of salesman with varying amounts of Units, Volume and Fixed Cost, along with expected Bonus for each salesman.

 

[tsmith0813]

Welcome to the forum.

I can't quite see what you're getting at. If a salesman had 5 units, he would be in Column1 yet he would be in Column2 for volume of 1.9mm. Please provide a longer list of salesman with varying amounts of Units, Volume and Fixed Cost, along with expected Bonus for each salesman.

He is in column 1 because he didn't meet both criteria (units and volume). In order to be eligible for the 2nd column bonus he must have 9 or more units along with a volume equal to or greater than $1,500,000. If his volume is equal to or greater than 1,500,000 but less than 9 units he falls back to column 1 regardless of total volume.

 

[Andrew Fergus]

How about something like this? Copy the formula in F15 down as you add more reps and rows.

Sheet9

	A	B	C	D	E	F
	#VALUE!
	#VALUE!
	#VALUE!

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: center"]Volume[/TD]
[TD="align: center"]Volume[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: center"]$500,000 -$1,500,000[/TD]
[TD="align: center"]$1,500,000 - $3,000,000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: center"]min[/TD]
[TD="align: center"]$500,000[/TD]
[TD="align: center"]$1,500,001[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: center"]max[/TD]
[TD="align: center"]$1,500,000[/TD]
[TD="align: center"]$3,000,000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: center"]4 - 8 Units[/TD]
[TD="align: center"]9 - 15 Units[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: center"]min[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: center"]max[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]Fixed Cost[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: center"]min[/TD]
[TD="align: center"]max[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: center"]$1,001[/TD]
[TD="align: center"]$2,000[/TD]
[TD="align: center"]$2,300[/TD]
[TD="align: center"]$4,300[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: center"]$0[/TD]
[TD="align: center"]$1,000[/TD]
[TD="align: center"]$4,000[/TD]
[TD="align: center"]$5,500[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: center"]Manager[/TD]

[TD="align: center"]Unit[/TD]
[TD="align: center"]Volume[/TD]
[TD="align: center"]Fixed Cost[/TD]
[TD="align: center"]Bonus[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="align: center"]Jim Jones[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]1,900,000[/TD]
[TD="align: center"]1,200[/TD]
[TD="align: center"]$2,300[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: center"]Rep2[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]400000[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]
[TD="align: center"]Rep3[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]3500000[/TD]
[TD="align: center"]2500[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD="align: center"]Rep4[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]750000[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]$4,000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]
[TD="align: center"]Rep5[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]1500000[/TD]
[TD="align: center"]1001[/TD]
[TD="align: center"]$2,300[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]
[TD="align: center"]Rep6[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]1500001[/TD]
[TD="align: center"]1500[/TD]
[TD="align: center"]$4,300[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]
[TD="align: center"]Rep7[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]3000000[/TD]
[TD="align: center"]500[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]22[/TD]
[TD="align: center"]Rep8[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]2000000[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]$5,500[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F15 =INDEX($C$10:$D$11,IF(E15>$B$10,0,IF(E15>$B$11,1,2)),MIN(IF(C15<$C$6,0,IF(C15<=$C$7,1,2)),IF(D15<$C$3,0,IF(D15<$D$3,1,2)))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4