Help with IF statement formatting!

[gilmores33]

Experts-

I need a bit of help with a formula on a sales commission spreadsheet.
I pay our salesman an additional bonus when they sell equipment at mor
than 40% margin. Any $ amount above 40% margin is split between th
company and the salesman. A typical sale example:

Cost of Equipment - $3000
40% Margin Pricing - $5000
Actual Price Sold - $5500
Actual Sale Margin - 45%

So, in this case, the salesman received his commission on the firs
$5000 (40% margin pricing), then splits the remaining $500 (from sal
price $5500 - 40% Margin Pricing of $5000). Company gets half ($250)
salesman gets half ($250). So if a salesman received 10% commission
they would get 10% of $5000 ($500) plus the split ($250) for a total o
$750 commission on the above deal. I hope that makes sense.

Now, I've got columns for Actual Price Sold, Cost of Equipment
Commission % for that salesman (all salesman are different), Commissio
Amount (excluding split) and Split Amount columns.

I've got all columns figured except the split column. I think I hav
the Split Column math formula figured out, but am having trouble wit
the IF (if above 40% margin) portion working. The split column shoul
read false if deal is at or below 40%, but should have a $ figure in i
if above 40%.

Copy of formula so far:

=IF(D59>39,((B59-(C59/0.6))/2)-((B59-C59/0.6)*E59))

D59 - Margin % cell
B59 - Actual Sale Price cell
C59 - Cost of Equipment cell
E59 - Commission % rate of salesman

I hope this is enough for you experts to help out! The formula abov
is pretty complicated, so if there's a simpler way to do this, pleas
help. And, most importantly, the only part that's not working righ
now is the IF statement. The math works without a problem. Once I ad
the IF statement, if gives False every time.

Thanks in advance for jumping in to help!

K

 

[Jeff]

Hi,

I get =IF(D2>0.39,((B2-(C2/0.6))/2)+((C2/0.6)*E2),"")
to work for me.(if I've understood your data columns
correctly)
jeff

 

[gilmores33]

Jeff,

That worked! You made my day. Thanks so much for the help.

K

 

[gilmores33]

The IF statement now works, but i'm having another related issue that
need some help with.

The cell that the IF statement is in is added to the totals commission
cell to give me a total commissions including the split. The proble
is, when there's no split, I'm getting an #DIV/0! in that split cell
which in turn is causing the same error in the cell that adds tha
split cell to the total commissions cell.

Is there a fix for that?

Thanks.

K