Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2007
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multi criteria formula (Excel 2003)

    I am trying to figure bonuses for my employees. Their bonus is based on % growth of Apples and % growth of Oranges. See attached spreadsheet. Please help.
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi criteria formula (Excel 2003)

    What are the expected results?

    Is it an intersection of the two percentages?

    <table border 1><td> </td><td>Apples</td><td>Oranges</td><td>Bonus</td><td>Bob</td><td>0.07</td><td>0.22</td><td>3000</td><td>Jill</td><td>0.33</td><td>0.11</td><td>5000</td><td>Jim</td><td>0.19</td><td>0.25</td><td>4000</td></table>

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi criteria formula (Excel 2003)

    If my assumption was correct the attached version should work.

    You would need to restructure your headers to show the lower limit of the growth for the formula to work:

    This is the formula I used to figure out the intersection.

    =OFFSET($C$5,MATCH(E11,$C$6:$C$9,1),MATCH(D11,$D$5 :$G$5,1))
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi criteria formula (Excel 2003)

    I think your formula should "officially" be

    =OFFSET($C$5,MATCH(D11,$C$6:$C$9,1),MATCH(E11,$D$5 :$G$5,1))

    although it doesn't make a difference for the result, since the table is symmetric with respect to apples and oranges. Or alternatively

    =INDEX($D$6:$G$9,MATCH(D11,$C$6:$C$9),MATCH(E11,$D $5:$G$5))

Posting Permissions

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