Can cell have multiple functions?
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Can cell have multiple functions?

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    What I am trying to do is calculate comissions based on different % rates. What I would like to ideally do is have a menu in the cell that I can choose the different %rates to do the calculations with.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sure, sounds like a lookup table or a nested if statement.....can you give be a more elaborate example please.

  3. #3
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try using a named ranges in your formula. Something like "=Sales* Comm". Name a cell on your sheet Comm and change that cell to reflect the current commision rate. You might even add a drop down box or spin control to change the rate.


  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    If you have a list of rates that remain static, atleast for a period of time, you can type these rates in one column in consecutive rows, and refer to this list with validation.
    Put the list way out of site somewhere, pick your cell in which you want the dropdown, click on Data, Validation, Allow, List, Select(Highlight) the list you formerly created, Make sure "In Cell Dropdown" is checked. If you want this in more than one cell, Copy current cell, choose the cells to paste, right click on these, choose paste special, choose validation.
    This will provide a dropdown in a cell or cells with the values you predetermined.

    There may be other ways, but this should work for you.
    Have a nice day!
    Tom

    [ This Message was edited by: TsTom on 2002-03-29 06:58 ]

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok lets say column A is the sales total what I would like column B to do is be able to choose a rate from 17%,18%,19% or 20% from drop box. Column C would display the calculated % total of the percentage chosen.

  6. #6
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In column B's cell, do this:

    Data-Validation.
    Choose List.
    Type:

    17%,18%,19%,20%



    ~Anne Troy

  7. #7
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    First, you must have something that controls the choice. How is the Commision rate determined? Does it depend on who the Salesperson is, or on the amount of the sale, or something else? Once that's decided, you can easily automate the calculation based on input into one or two cells. Either a VLOOKUP, a Named Range, Data Validation Dropdown, or perhaps the CHOOSE function

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Wow thanks all for the help. Thought I would have to wait days for someone to answer(not seconds) LOL. Thanks again problem solved.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com