So I am working on a spreadsheet myself & Real Estate professionals in my office. I'm trying to calculate from commission -> Pay - any fees.
What I'm trying to accomplish is I want Column J to take (Commission - Mentor Fee -Referral Fee)*.3 IF J2 doesn't equal $20,000. However, J2 =SUM(J4:J200). I wrote an IF Function, but once it hit $20,000 all cells went to $0. I want it to add up to $20,000 and STOP.
Same for Column K to $3,000, but that's a 6%.
ExcelHelp.JPG
Last edited by Akeller10; 07-18-2018 at 05:28 PM.
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Not really sure why this is titled circ ref, there is no mention of it in your post?
If you want something to "max out" at a certain value, use the MAX function. Something like...
2 =(max(20000,SUM(J4:J200))
When all else fails, do as sandy suggested
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Excel Doc should be uploaded now.
Can you explain this again please?
You have J2=(G4-H4-I4)*0.3I want Column J to take (Commission - Mentor Fee -Referral Fee)*.3 IF J2 doesn't equal $20,000. However, J2 =SUM(J4:J200).
and you also have
J2 =SUM(J4:J200)
so what would the rule be?
Also, what would go into J4, later?
I want J4 to tell me how much the 30% is that I am giving the brokerage for the commission that I earned on that transaction. Unless the total in Column J = $20,000. If it = $20,000 then I don't have to pay anymore.
Maybe this?
=IF(SUM($H$4:$H$200) < 20000,(G4-H4-I4)*0.3,"")
I'll try that. Thanks. I've been at this for days.
Keep me posted
Didn't work. I'm stumped
Data to be input for agents: Client Name, Closing Date, Referral (1=yes 0=no), Contract Price, and Commission %.
The rest of the fields I want to auto calculate if possible. I'm trying to make this as dummy proof as possible.
Hi,
Try this in J4 copied down:
Formula:
Please Login or Register to view this content.
For J2:
Formula:Please Login or Register to view this content.
Refer to attached sample.
Btw, here in CA, referral fees are only allowed to be paid to licensed agents..
EDIT: Also included your Column K request, attached New sample.
Last edited by jtakw; 07-18-2018 at 07:25 PM.
Same here in Nebraska. I am a Licensed Agent and So is my mentor.
I've made an Edit to my Post #12 to include your Column K formulas, see the New uploaded sample in my post.
Perfect! Thank you so much!
You're welcome.
Add rep would be appreciated, click the Star at the lower left corner of the posts you found helpful.
Couldn't get Anything around here for the sample prices in your worksheet....
CA Broker.
All the data in the first 9 lines are more or less real data. :D Cheaper to live in Nebraska then Cali. Lived in Sacramento Area for 2.5 years.
New Nebraska Agent :D
oops looks like I used SUM H instead if sum JOriginally Posted by Akeller10
apologies
Ford, I think OP probably used a formula similar to yours in post #7 using Column J (Summing Column J), that's probably what OP meant by "circular reference", since the formula "goes into" J4 copied down.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks