+ Reply to Thread
Results 1 to 4 of 4

Complex If Formula or alternative formula?

  1. #1
    Registered User
    Join Date
    08-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1

    Complex If Formula or alternative formula?

    I have been searching for a solution to this for a couple of days and I am trying to determine if I can use a standard if Formula, if I need to include a vlookup/lookup or some other formula as the only way I have been able to get my formula to work is if I type in the "goals" (will be explained below) manually into the formula, rather than having the formula pull the data from the cell.

    To simply: I have three sales groups where sales are combined for overall attainment, bonus is achieved based on the total sales volume and is paid on the attainment of each group. ie. Group A = $200 Group B = $400 Group C = $600. Total attainment is 1,200, however each group would be paid a bonus based on the group they are in. To make things less complex I will be using a separate if formula for each group.

    I have five attainment (bonus) tiers. ie.
    <=$300 = 1% for group A, 2% for group B, 3% for group C,
    301-$600 = 2% for group A, 3% for Group B, 4 % for group C
    ...
    ...
    >=$1201 = 5% for group A, 6% for Group B, 7% for Group C

    Under this scenario the overall attained sales is $1200. Now comes the tricky part. I am trying to get the formula to work where I first use Group A. (Very simplified) If Overall salesTier 1<=Tier 2, $200* 2%, etc etc.

    I would then write a formula for groups B and C separately in the same fashion.

    What formulas would you use to make this function work?


    Attached: Example.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Complex If Formula or alternative formula?

    Maybe something like this.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Complex If Formula or alternative formula?

    If in B11:B15 you can enter the lower bounds... e.g. in B11:0, in B12:300, in B13:600, in B14:800, B15:1200

    then use

    =E2*LOOKUP(E$6,$B$11:$C$15) copied down
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Complex If Formula or alternative formula?

    See attached file, which has this formula in F2:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I've amended your table slightly, as well.

    Hope this helps.

    Pete
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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