+ Reply to Thread
Results 1 to 11 of 11

calculate an agent's split based on certain criteria.

  1. #1
    Registered User
    Join Date
    08-24-2021
    Location
    New York
    MS-Off Ver
    Office 365
    Posts
    16

    calculate an agent's split based on certain criteria.

    Hello everyone.

    I'm in need of a formula for my Real Estate Office. The purpose of this formula would be to calculate an agent's split based on certain criteria.

    For example...

    John has a 75/25 split
    Maria has a 60/40 split

    John brings in a $20,550 commission
    Maria brings in a $10,420 commission

    I'm basically looking for a formula (based on the person's name) that when Maria brings in a commission, apply 60% and if John brings it in, apply 75%. See attachment for reference.

    Thanks for your help
    Attached Images Attached Images
    Last edited by FDibbins; 08-26-2021 at 11:48 PM. Reason: edited thread title

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Need Formula for Real Estate

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    08-24-2021
    Location
    New York
    MS-Off Ver
    Office 365
    Posts
    16

    Re: Need Formula for Real Estate

    Thanks for the quick reply, Alan.

    The gray column is what I'm trying to automate. Based on the initials put in column H, I'm trying to automatically populate column E, thus automatically calculating the rest of the spreadsheet. Let me know if that doesn't make sense.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: calculate an agent's split based on certain criteria.

    permo, I have edited your title to be more representative of what your question/problem is. Im sure you would agree that if you saw a question like "Need Formula for Real Estate" and you have no idea what it was about, the title alone would not give any clue as to what was required.

    For future threads, please take a moment and title them with something that describes your problem

    Thanks
    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

  5. #5
    Registered User
    Join Date
    08-24-2021
    Location
    New York
    MS-Off Ver
    Office 365
    Posts
    16
    Quote Originally Posted by FDibbins View Post
    permo, I have edited your title to be more representative of what your question/problem is. Im sure you would agree that if you saw a question like "Need Formula for Real Estate" and you have no idea what it was about, the title alone would not give any clue as to what was required.

    For future threads, please take a moment and title them with something that describes your problem

    Thanks
    Understood. Thanks for editing the title 👍🏼

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: calculate an agent's split based on certain criteria.

    In Cell E6 =VLOOKUP(H6,$J$19:$L$22,3,0)*C6

  7. #7
    Registered User
    Join Date
    08-24-2021
    Location
    New York
    MS-Off Ver
    Office 365
    Posts
    16

    Re: calculate an agent's split based on certain criteria.

    you are incredible. thank you

    I changed the formula to =VLOOKUP($H6,$J$19:$L$33,3,0)*$C6 so I can be able to drag it down the column. works great but now there's another issue...

    Being that column E is a Vlookup, it does not calculate the values in my Summation cells (E22, E33, E39) and is throwing off automated calculation in other cells. any advise?

    Updated Excel with your formula attached...
    Attached Files Attached Files

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: calculate an agent's split based on certain criteria.

    wrap your formula in an iferror function that makes the value a zero instead of #NA

    =IFERROR(VLOOKUP($H6,$J$19:$L$33,3,0)*$C6,0)

  9. #9
    Registered User
    Join Date
    08-24-2021
    Location
    New York
    MS-Off Ver
    Office 365
    Posts
    16

    Re: calculate an agent's split based on certain criteria.

    you're incredible, Alan. really appreciate your help

    the formula is perfect. it's exactly what i needed

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: calculate an agent's split based on certain criteria.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  11. #11
    Registered User
    Join Date
    08-24-2021
    Location
    New York
    MS-Off Ver
    Office 365
    Posts
    16

    Re: calculate an agent's split based on certain criteria.

    will do. thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] prorate real real estate taxes
    By InvGrp2 in forum Excel General
    Replies: 8
    Last Post: 11-10-2023, 11:05 AM
  2. Formula to Prorate Real estate taxes paid in arrears
    By regina85345 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-20-2021, 11:58 AM
  3. Real Estate Commissions
    By Mitch2797 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-29-2020, 03:48 PM
  4. [SOLVED] IF FUNCTION formula help Real Estate Condo Project absorption
    By $J$L$G in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-10-2015, 02:18 AM
  5. Returning #value! On real estate pro forma *formula help!
    By $J$L$G in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-11-2015, 09:16 PM
  6. Formula for my real estate business
    By codyschaaf in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-04-2014, 03:03 PM
  7. real estate calculations
    By ajp in forum Excel General
    Replies: 0
    Last Post: 05-10-2005, 07:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1