Calculations formula

[Guest]

I have a worksheet that I am figguring commissions on each sale. When a
customer pays by credit card, i have to deduct out of my gross profit margin
2.99% to cover for the Processing fee of the card. What I currently do right
now is calculate that myself, what I would like to do is see if there is a
formula to automatically deduct the 2.99% from the gross profit margin. An
example of what my sheet looks like:
In Column A I have : Date
In Column B I have : Customer Number
In Column C I have : Gross Profit Margin
In Column D I have : Commissions Which right now has this formula in it
=C70*$B$4
In Column E I have : Visa, MC, Terms, AMEX, Discover (Either one depending
upon the card used.)

What I would like to do is if i select any of the credit cards in column E
it would automatically minus 2.99% off of the number in Column C.

Please help me if you can

 

[Guest]

try this

=C1-((C1*0.299)/10)

hope this helps!!

 

[Guest]

No this really did not work. What Column would i put it in? I need it to
deduct IF visa, MC, AMEX, Discover is selected.

 

[David Biddulph]

As a matter of interest, why
=C1-((C1*0.299)/10) rather than
=C1-(C1*2.99%) or
=C1-(C1*2.99/100) ?

Of course, I doubt whether this gives the numbers the OP needs, as I would
have thought the deduction should be 2.99% of the transaction, not 2.99% of
the profit margin, but that's a different question from what the OP asked,
and the OP doesn't seem to have included the transaction amount in the data
provided.

 

[Guest]

Where would I put this formula?

No our commission is based off of 10% of Gross Profit Margin, but when ever
a credit card is used we get charged the 2.99% per transaction off of the
total gross Profit, then we are given our commissions after that deduction is
made.

 

[David Biddulph]

I'm even more confused than I was. If someone else understands you then
they can answer you. As with many problems on this group, the main
difficulty isn't with the Excel implementation, but in clarifying the
question as to what arithmetic needs doing and where your data are hidden.

 

[Pete_UK]

Presumably you enter the Gross Profit Margin manually, so it would be
difficult to apply a 2.99% reduction to it, as your entry would
overwrite any formula set up there. Better to adjust the formula in
column D. You seem to be working in row 70, so change the formula to
this:

=if(E70="",C70*$B$4,C70*$B$4*0.9701)

Hope this helps.

Pete