Looking for the expert....

[Yoohah]

First, I've freed up plenty of space in my email box(!)
Now for the challenge. 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

 

[Ragdyer]

Check your first post!

 

[David McRitchie]

You have a reply in your other thread in this newsgroup.

May I suggest that you
-- use your first and last name when you post.
-- choose a descriptive subject
-- only post once, you can add more information to your thread
but don't start another one. Just because you don't get a reply
in 24 hours doesn't mean your posting wasn't seen.

To make it easier on people, show what your layout is and what
you want lined up in text like it would be in a spreadsheet, it makes
it a lot easier for someone to visualize what you want, and to know
that they are calculating what your are looking for. If you don't make
it easy for people to read, you cut down your chances of getting a
fast answer or even possibly getting an answer at all (thought I think
it is not very often that a legitimate question actually goes unanswered.

The nice things about newsgroups is that someone in another
part of the world may answer your question at any hour of the day,
but in an English speaking newsgroup most people are going to
be in the US and midnight to 7 AM EDT is not the most active
time particularly on a 3 day weekend.

 

[Arvi Laanemets]

Hi

* In row 1 you have column headers (Sale Price, Units, etc.);
* Format column A as currency (for enough rows, p.e. A2:A1000);
* Select same number of cells in column B (B2:B1000), and then select from
menu Data.Validation and set validation to list with 'L,S,LS' in source;
* Into cell C2 enter the formula
=IF(OR(A2="",B2=""),"",IF(OR(B2="L",B2="S"),0.5,IF(B2="LS",1,0))*A2)
, format C2 as currency, and copy it down for same number of rows as
previous columns (C2:C1000);
* Select similar range in column D (D21000), and format it using data
validation like column B, with '3%,5%,6%' as list source;
* The same for column E, with '0%,10%,20%,30%, ... ' as list source (as much
selections as reasonable);
* Into cell F2 enter the formula
=IF(OR(A1="",D2=""),"",A2*D2*IF(E2>0,E2,1))
, format F2 as currency, and copy it down for same number of rows as
previous columns (F2:F1000);
* You didn't give a routine for Agent Commission, but probably you can do
it now.

 

[Don]

Thank you, Arvi.

I'm not doing a very good job of explaining how Company
Commission is affected by the Affinity Fee % (if there is
one). If the Sale Price is $100,000, and the Commission %
=3%, the result, so far, is a Company Commission of
$3000. However, if there is an Affinity Fee % of say 10%
(or, in this case, $300), that amount would be deducted
from the $3000 Company Commission, leaving the Company
Commission at $2700 in this case. The way that the
formula is now written is populating the Company
Commission field with the Affinity Fee %, but the % in
the Affinity Fee % cell should only be the *deduction*
factor to the Company Commission.

Also, the reason that I did not enter the Agent
commission is because I did not want to overcomplicate
things. But if you wish to take a stab at this one, this
is how it needs to work:

The Agent Commission is broken down depending on a final
commission of 30/10/0% of the 'post-Affinity' amount
going to the Company as Company Commission, the remainder
of which goes to the Agent as Agent Commission (i.e.
70/90/100%). So if we continue with the example above,
the Company Commission was $2700 after the Affinity Fee.
Let's call that "Total" Commission. Beginning on January
1, the Total Commission as it grows is taken into
consideration in this calculation. So we probably will
also need a Year-to-Date Total Commission. The reason is
because from $0 to $29,999.99 (of the YTD Total
Commission), the Agent Commission is 70% of the Total
Commission and the Company Commission is 30%. As soon as
the YTD Total Commission reaches $30,000, the Agent
receives a commission of 90%, until he reaches $39,000,
at which point his commission is 100.

 

[Arvi Laanemets]

Hi

Then the formula for Company Commmission is
=IF(OR(A2="",D2=""),"",C2*D2*(1-IF(E2>0,E2,0)))
(NB! There was an error in my formula - A1 was instead of A2)

I didn't catch this Agent Commission thing at all - haven't any experience
in this field. But here are a couple of formulas to consider:

=IF(OR(A1="",D2=""),"",A2*D2*IF(E2>0,E2,0)) returns the amount (Affinity
Fee?) substracted from Commission.

To calculate the summary commission for current row, you can use for row 2
the formula like (on fly)
=IF(OR(A2="",D2=""),"",SUM(F$2:F2))
or
=IF(OR(A2="",D2=""),"",SUMPRODUCT((A$2:A2)*(D$22)))
or
=IF(OR(A2="",D2=""),"",SUMPRODUCT((A$2:A2)*(D$22))-SUMPRODUCT((A$2:A2)*(D$
22)*(E$2:E2)))
Maybe some of them is for some use!

 

[Don]

Arvi, you are the Wizard! The first formula below was
what I was looking for.

Let's forget everything but the following, because with
the exception of the last two columns, the spreadsheet is
perfect:

I've got four columns - F (Total Commission -this
transaction), G (YTD Total Commission), H (Company
Commission) and I (Agent Commission)

- Column F works perfectly
- Column G seems to work perfectly
- Column H should be 30% of F if G is < $30,000, 10% of F
if G is $30,000 to $38,999.99, and 0% of F if G is
$39,000 or greater.
- Column I should be the other 70% of F if G is <
$30,000, 90% of F if G is $30,000 to $38,999.99, and 100%
of F if G is $39,000 or greater.

Is this possible?

Thanks again for your expertise, Arvi -

 

[Frank Kabel]

Hi Don
try:
H2:
=IF(G2<>"",ROUND(F2*IF(G2>=39000,0,IF(G2>=30000,0.1,0.3)),2),"")

I2:
=IF(G2<>"",F2-H2,"")

 

[Domenic]

Hi,

Try,

H2=IF(G2<30000,F2*0.3,IF(G2<39000,F2*0.1,0))

I2=IF(G2<30000,F2*0.7,IF(G2<39000,F2*0.9,F2))

Hope this helps!

 

[Arvi Laanemets]

Hi


Frank did you give an excellent advice, but for column H you can also use
H2=IF(OR(G2="",F2=""),"",((G2<39000)*0.01+(G2<30000)*0.02)*F2)
(there are always at least 3 different ways to do something in Excel!)

 

[Frank Kabel]

Hi Arvi
at least 3 ways <vbg>

 

[Arvi Laanemets]

Hi

I count only those we are aware of! )