+ Reply to Thread
Results 1 to 4 of 4

Help - Need to check values in order to assign correct commission multiplier

  1. #1
    Registered User
    Join Date
    09-17-2014
    Location
    Cambridge, England
    MS-Off Ver
    2016
    Posts
    58

    Help - Need to check values in order to assign correct commission multiplier

    Hello,

    Hopefully this reads correctly. Basically I need to find a formula to check specific values within one column and then use these values to find the correct percentage multiplier so that a certain premium can have its commission worked out correctly.

    I have attached a basic table to show my meaning. And need to be able to work out the correct commission from a given premium using the right commission rate.

    Basically the three locations shown in columns I and J (Cambridge, Ely and Newmarket) all have a specific percentages of commission assigned to them. I need to then search column C to find any locations that match these three and then take their Amount Paid (Column D) and multply it by the assigned percentage in Column J. Column F will then need to show the amount of commission.

    I have tried a mix of the IF statement and VLOOKUP formula but cannot get one that fits.

    I hope what I am trying has been explained correctly.

    Any help would be greatly appreciated.

    Regards
    Attached Files Attached Files

  2. #2
    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: Help - Need to check values in order to assign correct commission multiplier

    Perhaps, this?

    =VLOOKUP(C2,$I$2:$J$4,2,0)*D2
    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

  3. #3
    Registered User
    Join Date
    09-17-2014
    Location
    Cambridge, England
    MS-Off Ver
    2016
    Posts
    58

    Re: Help - Need to check values in order to assign correct commission multiplier

    Mr FDibbins - You are a star!! Works Perfectly. Would seem I was over complicating things by also using the IF statement.

    Thanks

  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: Help - Need to check values in order to assign correct commission multiplier

    Happy to help and thanks for the feedback

+ 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] Check cells are correct format and contain correct data
    By rikosborne in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-26-2014, 02:53 PM
  2. [SOLVED] Calculating commission based on category, multiplier and percentage
    By edahlgren in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-07-2013, 03:48 PM
  3. [SOLVED] Check if the values of a list are in progressive order.
    By Askalian in forum Excel General
    Replies: 11
    Last Post: 01-24-2013, 08:56 PM
  4. Data validation for multiplier values
    By Egan in forum Excel General
    Replies: 2
    Last Post: 05-24-2010, 02:35 PM
  5. Calculating order entry times with a multiplier
    By Apel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-17-2009, 02:22 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