I am trying to create a worksheet to calculate commissions.

I am a REALTOR (anybody looking to buy or sell a home?)

I am trying to create a worksheet that will calculate my commissions. The
problerm is that my commission is based on the amount that has been paid to
the broker in the current fiscal year

If I have paid less than 28000, my split is 70/30. Once 28000 has been paid
to the broker, my split then becomes 100/0 split

Here are the critical columns (g,h,i)
Sale_Price : Brokerage_Percent :Brokerage_amount


This is the last way I have tried to get this working....

Sale_Price = the cost of the house
Brokerage_Percent =
=IF(SUM(I$2,ADDRESS(ROW()-1,COLUMN()+1,3))<28000,30,0)
Brokerage_amount = Sale_Price * Brokerage_Percent
This gives me a #value error

I have also tried stuffing a sum of Brokerage_Amount into a cell, and then
calculate brokerage_percent based on that cell. This gave me a circular
reference error.

There are 3 things I see wrong with what I am tring to do.

First, I am getting the errors (which I can see why, but not know how to get
around them)

The second is that as I get close to the 28000 threshold, I will need to
take just a portion of the commision reported by whatever calculation as the
correct amount of commission

The third (which I think I could figure out) is how to have the calculations
look only at the current fiscal year (right now, I might just use a new
sheet for each year)


How can I overcome these issues?

Bruce Johnson
www.YourAgentWithAHeart.com
bruce.at.YourAgentWithAHeart.com (remove the .at. and replace with @)

Hi,
Try this for your first problem:

=IF(SUM(I$2,INDIRECT(ADDRESS(ROW()-1,COLUMN()+1,3)))<28000,30,0)

Your formula was trying to sum an address e.e. $c$2, rather than the
contents of $c$2 which what I assume you want to do.

HTH

"Bruce Johnson" wrote:

> I am trying to create a worksheet to calculate commissions.
>
> I am a REALTOR (anybody looking to buy or sell a home?)
>
> I am trying to create a worksheet that will calculate my commissions. The
> problerm is that my commission is based on the amount that has been paid to
> the broker in the current fiscal year
>
> If I have paid less than 28000, my split is 70/30. Once 28000 has been paid
> to the broker, my split then becomes 100/0 split
>
> Here are the critical columns (g,h,i)
> Sale_Price : Brokerage_Percent :Brokerage_amount
>
>
> This is the last way I have tried to get this working....
>
> Sale_Price = the cost of the house
> Brokerage_Percent =
> =IF(SUM(I$2,ADDRESS(ROW()-1,COLUMN()+1,3))<28000,30,0)
> Brokerage_amount = Sale_Price * Brokerage_Percent
> This gives me a #value error
>
> I have also tried stuffing a sum of Brokerage_Amount into a cell, and then
> calculate brokerage_percent based on that cell. This gave me a circular
> reference error.
>
> There are 3 things I see wrong with what I am tring to do.
>
> First, I am getting the errors (which I can see why, but not know how to get
> around them)
>
> The second is that as I get close to the 28000 threshold, I will need to
> take just a portion of the commision reported by whatever calculation as the
> correct amount of commission
>
> The third (which I think I could figure out) is how to have the calculations
> look only at the current fiscal year (right now, I might just use a new
> sheet for each year)
>
>
> How can I overcome these issues?
>
> Bruce Johnson
> www.YourAgentWithAHeart.com
> bruce.at.YourAgentWithAHeart.com (remove the .at. and replace with @)
>
>
>
>
>
>
>
>

Hi,
I am not sure sure why I got this to work, because I think it needs a
multiple IF and AND(IF), but this is what I got. I added a column for
Cumulative_Brokerage_Amount, which is the Sum of Brokerage_Amount, this I
made Column J. Then you need to figure out the Cumulative Gross Sum of the
Sales_Price that equals the 28000 after it is multiplied by Brokerage_Percent.

I used .06 or 6% as the Brokerage_Percent, so commissions will not be paid
after a cumulative Sales_Price Exceeds $466,666. Then I used the formula
=IF(SUM($G$1:G2)>=466666,28000-J1,H2*G2) under Brokerage_Amount.

Sale_Price Br% BrAmt CumBrAmt
465000 0.06 27900 27900 -> =SUM($I$2:I2)
1000 0.06 60 27960
25000 0.06 40 28000
BrAmt -($G$1:G2)>=466666,28000-J1,H2*G2)

Hope it works for you.
Thanks,


"Bruce Johnson" wrote:

> I am trying to create a worksheet to calculate commissions.
>
> I am a REALTOR (anybody looking to buy or sell a home?)
>
> I am trying to create a worksheet that will calculate my commissions. The
> problerm is that my commission is based on the amount that has been paid to
> the broker in the current fiscal year
>
> If I have paid less than 28000, my split is 70/30. Once 28000 has been paid
> to the broker, my split then becomes 100/0 split
>
> Here are the critical columns (g,h,i)
> Sale_Price : Brokerage_Percent :Brokerage_amount
>
>
> This is the last way I have tried to get this working....
>
> Sale_Price = the cost of the house
> Brokerage_Percent =
> =IF(SUM(I$2,ADDRESS(ROW()-1,COLUMN()+1,3))<28000,30,0)
> Brokerage_amount = Sale_Price * Brokerage_Percent
> This gives me a #value error
>
> I have also tried stuffing a sum of Brokerage_Amount into a cell, and then
> calculate brokerage_percent based on that cell. This gave me a circular
> reference error.
>
> There are 3 things I see wrong with what I am tring to do.
>
> First, I am getting the errors (which I can see why, but not know how to get
> around them)
>
> The second is that as I get close to the 28000 threshold, I will need to
> take just a portion of the commision reported by whatever calculation as the
> correct amount of commission
>
> The third (which I think I could figure out) is how to have the calculations
> look only at the current fiscal year (right now, I might just use a new
> sheet for each year)
>
>
> How can I overcome these issues?
>
> Bruce Johnson
> www.YourAgentWithAHeart.com
> bruce.at.YourAgentWithAHeart.com (remove the .at. and replace with @)
>
>
>
>
>
>
>
>

The following solution works only with a two tier split between the
broker and the realtor. [While it (hopefully) lays the groundwork for a
multi-tier split, such a relationship is probably best handled through a
VBA function.]

Suppose the two-tier table is in I2:J3
0 0.3
28000 0

Now, define two names (Insert | Name > Define...):
BrokerSplitTable =OFFSET(Sheet1!$I$2,0,0,COUNTA(Sheet1!$I:$I)-1,2)
MaxBrokerAmt =MAX(INDEX(BrokerSplitTable,,1))

Finally, suppose the data are organized as follows (all starting with
row 2 since row 1 is assumed to be a header row)
Column A: Date of sale
Column B: House price
C: Commission (%)
D: Commission ($)
E: Broker share ($)
F: Realtor share ($)

A, B, and C will be numbers you enter.

D2 is calculated as =C2*B2
E2 is =MIN(MaxBrokerAmt,VLOOKUP(D2,BrokerSplitTable,2,TRUE)*D2)
F2 is =D2-E2
Copy D2 to D3, F2 to F3.
E3 is =MIN(MaxBrokerAmt-SUM($E$2:E2),VLOOKUP(SUM($E
$2:E2),BrokerSplitTable,2,TRUE)*D3)

Copy D2:F3 as far down as needed.

It is conceivable that a new year can be handled correctly since we know
the date of each sale. However, rather than complicate matters, it will
be a lot simpler to just use the E2 formula for the first sale in each
year and adjust the subsequent formulas (which would be copied from E3)
so that they use that cell as the base reference rather than $E$2.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article <cv-dnVtnEO5j9OXfRVn-(E-Mail Removed)>, "Bruce Johnson"
<bruce.at.YourAgentWithAHeart.com> says...
> I am trying to create a worksheet to calculate commissions.
>
> I am a REALTOR (anybody looking to buy or sell a home?)
>
> I am trying to create a worksheet that will calculate my commissions. The
> problerm is that my commission is based on the amount that has been paid to
> the broker in the current fiscal year
>
> If I have paid less than 28000, my split is 70/30. Once 28000 has been paid
> to the broker, my split then becomes 100/0 split
>
> Here are the critical columns (g,h,i)
> Sale_Price : Brokerage_Percent :Brokerage_amount
>
>
> This is the last way I have tried to get this working....
>
> Sale_Price = the cost of the house
> Brokerage_Percent =
> =IF(SUM(I$2,ADDRESS(ROW()-1,COLUMN()+1,3))<28000,30,0)
> Brokerage_amount = Sale_Price * Brokerage_Percent
> This gives me a #value error
>
> I have also tried stuffing a sum of Brokerage_Amount into a cell, and then
> calculate brokerage_percent based on that cell. This gave me a circular
> reference error.
>
> There are 3 things I see wrong with what I am tring to do.
>
> First, I am getting the errors (which I can see why, but not know how to get
> around them)
>
> The second is that as I get close to the 28000 threshold, I will need to
> take just a portion of the commision reported by whatever calculation as the
> correct amount of commission
>
> The third (which I think I could figure out) is how to have the calculations
> look only at the current fiscal year (right now, I might just use a new
> sheet for each year)
>
>
> How can I overcome these issues?
>
> Bruce Johnson
> www.YourAgentWithAHeart.com
> bruce.at.YourAgentWithAHeart.com (remove the .at. and replace with @)
>
>
>
>

Thanks for all of the help!

I did get it working (actually a little differant than the suggestions...)
Here is what I did:
I created a new column Q (Previous_broker_commision) which adds the previous
commissions and the current commission. I use this as the base of my
testing.
I then compare the q column from the last transaction to 28000, if it is
less than 28k, then my commision is
=IF(Q2>=28000,0,IF(E3*0.3<(28000-Q2),E3*0.3,28000-Q2))

This seems to work very well.
I did have to have one "starter" row that had a 0 for q


Thanks for all of the help and suggestions.

Bruce Johnson
www.YourAgentWithAHeart.com
bruce.at.YourAgentWithAHeart.com (remove the .at. and replace with @)

"Bruce Johnson" <bruce.at.YourAgentWithAHeart.com> wrote in message
news:cv-dnVtnEO5j9OXfRVn-(E-Mail Removed)...
>I am trying to create a worksheet to calculate commissions.
>
> I am a REALTOR (anybody looking to buy or sell a home?)
>
> I am trying to create a worksheet that will calculate my commissions. The
> problerm is that my commission is based on the amount that has been paid
> to the broker in the current fiscal year
>
> If I have paid less than 28000, my split is 70/30. Once 28000 has been
> paid to the broker, my split then becomes 100/0 split
>
> Here are the critical columns (g,h,i)
> Sale_Price : Brokerage_Percent :Brokerage_amount
>
>
> This is the last way I have tried to get this working....
>
> Sale_Price = the cost of the house
> Brokerage_Percent = =IF(SUM(I$2,ADDRESS(ROW()-1,COLUMN()+1,3))<28000,30,0)
> Brokerage_amount = Sale_Price * Brokerage_Percent
> This gives me a #value error
>
> I have also tried stuffing a sum of Brokerage_Amount into a cell, and then
> calculate brokerage_percent based on that cell. This gave me a circular
> reference error.
>
> There are 3 things I see wrong with what I am tring to do.
>
> First, I am getting the errors (which I can see why, but not know how to
> get around them)
>
> The second is that as I get close to the 28000 threshold, I will need to
> take just a portion of the commision reported by whatever calculation as
> the correct amount of commission
>
> The third (which I think I could figure out) is how to have the
> calculations look only at the current fiscal year (right now, I might just
> use a new sheet for each year)
>
>
> How can I overcome these issues?
>
> Bruce Johnson
> www.YourAgentWithAHeart.com
> bruce.at.YourAgentWithAHeart.com (remove the .at. and replace with @)
>
>
>
>
>
>
>