Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Avoiding a circular reference or value error while trying to calculate commission - a challange for me!
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 @) |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Avoiding a circular reference or value error while trying to calcu
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 @) |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Avoiding a circular reference or value error while trying to calcu
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 @) |
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Avoiding a circular reference or value error while trying to calculate commission - a challange for me!
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,TR UE)*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 , "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 @) |
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Avoiding a circular reference or value error while trying to calculate commission - a challange for me!
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 ... 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 @) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Circular Reference for Commission % | Excel Worksheet Functions | |||
Help avoiding a circular reference | Excel Worksheet Functions | |||
adding on to an existing total & avoiding a circular reference blo | Excel Discussion (Misc queries) | |||
Macro avoiding circular reference | Excel Worksheet Functions | |||
Avoiding circular reference on formula | Excel Discussion (Misc queries) |