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 @)