I have a difficult one, Well I think it's difficult PLEASE HELP!!!

[tommygunnz]

I'm trying to make a commission structure for Excel and it's pretty difficult

So we get paid 30% of the front gross + 10% of the back gross = total commission.

Example: $2,000 front = $600, and $2000 back =$200 grand total $800

if the front is negative, subtract that to the back gross x 10%.
if its below 250, you get paid 250 minimum. if its above 250, you the higher pay.

example $-2000 front & $4000 back = 10% x $2000= $200 id get $250 commission
example $-2000 front & $8000 back = 10% x $6000=$600 id get paid $600
example $-20 front & $4000 back=10% x $3980 id get paid $398
example $200 front (200*.3=60) & $4000 back (4000*.1=400) total paid $460

We will always get paid a minimum of $250 total.

any way someone can help me make an function for this?

 

[tommygunnz]

=MAX(IF(H17<0,SUM(H17:I17)*0.1,SUM(H17*0.3*(C17+D17),I17*0.1)),250)


this formula works perfectly, but how do i get it to show 0 or blank if there is nothing in the row?

 

[tommygunnz]

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style><style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

14

$250.00

<colgroup><col style="width: 20px"><col width="34"><col width="37"><col width="36"><col width="63"><col width="111"><col width="102"><col width="76"><col width="75"><col width="96"><col width="63"><col width="55"><col width="35"><col width="55"><col width="55"><col width="84"><col width="55"><col width="145"><col width="55"><col width="55"><col width="55"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

USING FORMULA

=MAX(IF(H16<0,SUM(H16:I16)*0.1,SUM(H16*0.3*(C16+D16),I16*0.1)),250)

IT GIVES ME A COMMISSION, WHEN I HAVE NO ENTRIES, HOW CAN I GET IT TO SHOW BLANK OR $0?

 

[Dave Patton]

try
=IF(G3=0,0,MAX(IF(G3<0,G3+H3)*0.1+G3*IF(OR(B3=0.5,C3=0.5),0.15,0.3)+H3*0.1,250))

 

[tommygunnz]

iT MAKES THE COMMISSION SHOW $0 BUT WHEN I ADD ANYTHING IT GIVES ME "VALUE"

Never mind I got it to Work!!!