# Thread: Multi criteria formula (Excel 2003)

1. ## 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.

2. ## 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. ## 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))

4. ## 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
•