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

tommygunnz

New Member
Joined
Dec 20, 2017
Messages
17
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?
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,473
Office Version
  1. 2016
Platform
  1. Windows
Hi,

This should do it:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Front Gross</td><td style=";">Back Gross</td><td style="text-align: right;;"></td><td style=";">Commission</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">-$2,000.00</td><td style="text-align: right;;">$4,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">$250.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">-$2,000.00</td><td style="text-align: right;;">$8,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">$600.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">-$20.00</td><td style="text-align: right;;">$4,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">$398.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">$200.00</td><td style="text-align: right;;">$4,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">$460.00</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=MAX(<font color="Blue">IF(<font color="Red">A2<0,SUM(<font color="Green">A2:B2</font>)*0.1,SUM(<font color="Green">A2*0.3,B2*0.1</font>)</font>),250</font>)</td></tr></tbody></table></td></tr></table><br />

D2 formula copied down, adjust cell references as needed.
 

tommygunnz

New Member
Joined
Dec 20, 2017
Messages
17
so my excel looks like this, but I can always change it
<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>
Commission should be Front Gross Back Gross
Gross Commission Gross Commission
$485 $ 2,234.00 $ 335.10 $1500 $150
$250 $(2,042.00) $ 250.00 $ 1,566.00 $ 156.60
$536$ 2,050.00 $ 307.50 $ 2,292.00 $ 229.20
$237$ 28.62 $ 250.00 $ 7,907.00 $ 790.70
$946.7$ 2,614.00 $ 392.10 $ 5,546.00 $ 554.60
$250
$(2,178.00) $ 250.00 $ 3,720.00 $ 372.00

<colgroup><col style="width: 102px"><col width="76"><col width="74"><col width="75"><col width="69"><col width="63"></colgroup><tbody>
</tbody>
 

Intronaut

New Member
Joined
Oct 11, 2017
Messages
7
Assuming front is in A1 and back is in A2:

=IF(A1>=0,MAX(0.3*A1+0.1*B1,250),MAX(0.1*SUM(B1,A1),250))

HTH
 

Intronaut

New Member
Joined
Oct 11, 2017
Messages
7

ADVERTISEMENT

Sorry I meant if front is in a1 and back is in b1. But jtakw already addressed so never mind!
 

tommygunnz

New Member
Joined
Dec 20, 2017
Messages
17
WOW!!! Fantastic!!

I forgot one thing! I Thank you so so much for taking the time to do this for me.

but sometime the deals are split in half. but only the front is split so it would be .15 not .3 the back is always 10%
my spreed sheet looks like this
a b c d e f g h i j k l
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
DateUsedNewStock #VehicleCustomer Front Gross Back Gross Deal # VinRDR
Gross Gross
112/150.5 $ 2,234.00 $ -
212/191 $ (2,042.00) $ 1,566.00
312/210.5 $ 2,050.00 $ 2,292.00
512/221 $ 28.62 $ 7,907.00
612/230.5 $ 2,614.00 $ 5,546.00
712/261 $ (2,178.00) $ 3,720.00
712/271 $ (794.00)
812/271 $ (2,895.00)
912/271 $ 3,033.00 $ 3,329.00
1012/281 $ 624.00 $ 4,700.00
1112/301 $ (2,262.00) $ -
1212/300.5 $ (1,560.00) $ 1,000.00
13

<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="63"><col width="55"><col width="35"></colgroup><tbody>
</tbody>


What i was originally doing was telling the front gross to multiply but by C1 or D1 to get either a full or half commission.

is this possible?
 

tommygunnz

New Member
Joined
Dec 20, 2017
Messages
17

ADVERTISEMENT

WOW!!! Fantastic!!

I forgot one thing! I Thank you so so much for taking the time to do this for me.

but sometime the deals are split in half. but only the front is split so it would be .15 not .3 the back is always 10%
my spreed sheet looks like this
a b c d e f g h i j k l
DateUsedNewStock #VehicleCustomerFront GrossBack GrossDeal #VinRDR
GrossGross
112/150.5$ 2,234.00$ -
212/191$ (2,042.00)$ 1,566.00
312/210.5$ 2,050.00$ 2,292.00
512/221$ 28.62$ 7,907.00
612/230.5$ 2,614.00$ 5,546.00
712/261$ (2,178.00)$ 3,720.00
712/271$ (794.00)
812/271$ (2,895.00)
912/271$ 3,033.00$ 3,329.00
1012/281$ 624.00$ 4,700.00
1112/301$ (2,262.00)$ -
1212/300.5$ (1,560.00)$ 1,000.00
13

<tbody>
</tbody>


What i was originally doing was telling the front gross to multiply but by C1 or D1 to get either a full or half commission.

is this possible?
 
Last edited:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,473
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Is this what you mean?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date</td><td style=";">Used</td><td style=";">New</td><td style=";">Stock #</td><td style=";">Vehicle</td><td style=";">Customer</td><td style=";">Front Gross </td><td style=";">Back Gross </td><td style=";">Deal # </td><td style=";">Vin</td><td style=";">RDR</td><td style=";">Commission</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">15-Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$2,234.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">335.1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">19-Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">($2,042.00)</td><td style="text-align: right;;">$1,566.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">250</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">21-Dec</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$2,050.00 </td><td style="text-align: right;;">$2,292.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">536.7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">22-Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$28.62 </td><td style="text-align: right;;">$7,907.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">799.286</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">23-Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$2,614.00 </td><td style="text-align: right;;">$5,546.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">946.7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">26-Dec</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">($2,178.00)</td><td style="text-align: right;;">$3,720.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">250</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">27-Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">($794.00)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">250</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">27-Dec</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">($2,895.00)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">250</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">27-Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$3,033.00 </td><td style="text-align: right;;">$3,329.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1242.8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">28-Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$624.00 </td><td style="text-align: right;;">$4,700.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">657.2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">30-Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">($2,262.00)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">250</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">30-Dec</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">($1,560.00)</td><td style="text-align: right;;">$1,000.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">250</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L2</th><td style="text-align:left">=MAX(<font color="Blue">IF(<font color="Red">G2<0,SUM(<font color="Green">G2:H2</font>)*0.1,SUM(<font color="Green">G2*IF(<font color="Purple">OR(<font color="Teal">B2=0.5,C2=0.5</font>),0.15,0.3</font>),H2*0.1</font>)</font>),250</font>)</td></tr></tbody></table></td></tr></table><br />

L2 formula copied down.
 

tommygunnz

New Member
Joined
Dec 20, 2017
Messages
17
This works fantastic!!! Your awesome!!




When I add the formula, all my fields show a $250 commission, when I haven't added anything to the row. How can it show blank or 0 till I add something?

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
17$250.00
18$250.00
19$250.00
20$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"></colgroup><tbody>
</tbody>
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,473
Office Version
  1. 2016
Platform
  1. Windows
Hi,

This will leave the "Commission" column BLANK if there are no entries in columns G and H (Front Gross and Back Gross):

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date</td><td style=";">Used</td><td style=";">New</td><td style=";">Stock #</td><td style=";">Vehicle</td><td style=";">Customer</td><td style=";">Front Gross </td><td style=";">Back Gross </td><td style=";">Deal # </td><td style=";">Vin</td><td style=";">RDR</td><td style=";">Commission</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">15-Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$2,234.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">335.1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">19-Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">($2,042.00)</td><td style="text-align: right;;">$1,566.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">250</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">21-Dec</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$2,050.00 </td><td style="text-align: right;;">$2,292.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">536.7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">22-Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$28.62 </td><td style="text-align: right;;">$7,907.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">799.286</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">23-Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$2,614.00 </td><td style="text-align: right;;">$5,546.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">946.7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">26-Dec</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">($2,178.00)</td><td style="text-align: right;;">$3,720.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">250</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">27-Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">($794.00)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">250</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">27-Dec</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">($2,895.00)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">250</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">27-Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$3,033.00 </td><td style="text-align: right;;">$3,329.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1242.8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">28-Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$624.00 </td><td style="text-align: right;;">$4,700.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">657.2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">30-Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">($2,262.00)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">250</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">30-Dec</td><td style="text-align: right;;">0.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">($1,560.00)</td><td style="text-align: right;;">$1,000.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">250</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">31-Dec</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">G2="",H2=""</font>),"",MAX(<font color="Red">IF(<font color="Green">G2<0,SUM(<font color="Purple">G2:H2</font>)*0.1,SUM(<font color="Purple">G2*IF(<font color="Teal">OR(<font color="#FF00FF">B2=0.5,C2=0.5</font>),0.15,0.3</font>),H2*0.1</font>)</font>),250</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

L2 formula copied down.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,320
Messages
5,600,946
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top