Need help writing formulas for following functions -

[Guest]

I am building a spreadsheet which only needs to contain a
few fields but I am having great difficulty getting the
calculations right. If you can help I would be eternally
grateful! Thanks in advance -

Here are the columns I wish to include:
Sale Price, Units, Sale Volume, Commission %, Affinity Fee
(%), Company commission, Agent commission.

Here is what needs to be calculated:
Sale Price - manually entered (e.g. $100,000)
Units - L, S or LS manually entered are the only possible
entries (where L(ist) is worth .5, S(ell) is worth .5 and
LS is worth 1)
Sale Volume - $ amount that one gets credit for, based on
Sale Price, depending on whether the Units sold were .5
or 1. If .5 (either L or S), and the Sale Price was
$100,000, Sale Volume will equal $50,000. If Units = LS
(i.e. "1"), Sale Volume in this example = $100,000. This
field must be calculated.
Commission % - Manually entered percentage. Examples are
3%, 5% or 6%.
Affinity Fee % - Manually entered percentage. May be 0%,
10%, 20%, etc.
Company Commission - This field must be calculated. It is
the Sale Price multiplied by the Commission %, the result
of which is then multiplied by the Affinity Fee % (if
there is one). The resulting $ amount is the Company
Commission.

If you can figure this out and send a spreadsheet
containing the necessary formulas, you will be my savior.
I am at wit's end on this, despite the fact that for a
veteran Excel user, the formulas may not be terribly
difficult.

Again, many thanks in advance for any assistance you can
offer.

Thank you,

Yoohah

 

[rjb]

Sale Price Units Sale Volume Commission % Affinity Fee (%)
Company commission Agent commission
$100,000.00 L $50,000.00 3% 0% $0.00 $3,000.00


Sales Volume =IF(B2="LS",1,0.5)*A2
Company commission =A2*D2*E2
Agent commission =A2*D2

Format Commission and Affinity Fee as a percentage

 

[Yoohah]

Works great except for 1 thing:

1. If there is no Affinity Fee %, Company Commission
winds up equalling zero. Instead, what should happen is
no amount should be deducted from the Company Commission
if no Affinity Fee % exists. Any Affinity Fee % > 0%
should take that percentage off of the Sale Price.

Thanks again, RJB. I bow to thee!!

 

[Domenic]

Works great except for 1 thing:

1. If there is no Affinity Fee %, Company Commission
winds up equalling zero.

Hi,

Try,

=A2*D2*IF(E2>0,E2,1)

Hope this helps!

 

[Guest]

Thanks, Domenic. That helps but I'm still running into
the problem I cited above in the message to "Arvi