Help with simple function please

[tommygunnz]

I'm trying to add a commission structure.

front and back gross. back gross is paid 10%

if the front gross is negative, the you subtract that from the back gross, then multiply x 10%
if the front gross is positive then no deduction needed.


<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 76px"><col width="74"><col width="75"><col width="69"></colgroup><tbody>[TR]
[TD="colspan: 2, align: center"] Front Gross [/TD]
[TD="colspan: 2, align: center"] Back Gross [/TD]
[/TR]
[TR]
[TD="align: center"] Gross [/TD]
[TD="align: center"] Commission [/TD]
[TD="align: center"] Gross [/TD]
[TD="align: center"] Commission [/TD]
[/TR]
[TR]
[TD="align: center"] $ (2,000.00)[/TD]
[TD="align: center"] $ 250.00 [/TD]
[TD="align: center"] $ 5,000.00 [/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
G3 H3 I3 J3

so =I3-(G3<0)*(i3*.1) this excel says = $900

but its not right

should be
$5,000 back gross - ($-2000) front gross =$3000 * 10% = 300




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

 

[mrhstn]

Hi

Try this.

Book1
	G	H	I	J
2	Front Gross		Back Gross	Commission
3	-£(2,000.00)	£250.00	£5,000.00	£300.00
Sheet2

Cell Formulas
Range	Formula
J3	=(I3+MIN(G3, 0))*0.1

 

[inactiveuserps07]

Tommy, the above formula by @mrhstn should work. However, the fact that your original formula resulted in $900 instead of $4500 makes me wonder if something else is going on that hasn't been explained.

 

[tommygunnz]

Awesome Works like a charm!!!