# multiple criteria computation!!

1. ## multiple criteria computation!!

Untitled.png

need an expert on this please!

am trying to compute staff's contribution in F2 based on criteria B2, C2, D2 AND E2.

1) if B2 = "Y", F2 = "SPR"
2) if D2 = "N", F2 = 0 then if all FALSE,
3) search C2 within B8:E8 of CON'EE table A7:E12 and search E2 in A9:A12, both just need the closest match and return intersecting value

example #1:
Staff A, Age 39 and Gross Pay \$650, F2 will return value (B10*(E2-500)) and TRUNC -1

example #2
Staff B, Age 68 and Gross Pay \$6800, F2 will return (E12)

*if return value falls under Row 9 or Row 12, = (intersection cell value)
*if return value falls under Row 10, = (intersection cell value*(Gross-500)) and TRUNC -1
*if return value falls under Row 11, = (intersection cell value*Gross) and TRUNC -1

hope the above is understood to you all. really having a hard time trying to get this right!! really appreciate any help now.

thank you!!!

2. ## Re: multiple criteria computation!!

Hello and welcome to the forum.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

3. ## Re: multiple criteria computation!!

Hi there! thank you for the reply!!

4. ## Re: multiple criteria computation!!

Hi there. as attached

5. ## Re: multiple criteria computation!!

Why do some rows (such as F4, F9, and F10) multiply the CON'EE table value by column E when others (such as F7) don't?

6. ## Re: multiple criteria computation!!

Here's where I'm at so far. I edited your table to show the minimum values in each group.

F2 =IF(B2&C2="YN",0,IF(B2&C2="YY","SPR",IF(B2&C2="NY",TRUNC(INDEX(B\$17:E\$20,MATCH(E2,A\$17:A\$20,1),MATCH(D2,B\$16:E\$16,1))*(E2-IF(AND(E2>500,E2<=750),500,0)),-1))))

This gives the expected results for all of the rows except for F7 due to the question that I asked in post #5.

See attachment for clarification.

7. ## Re: multiple criteria computation!!

I understand now, the only multipliers are in rows 17-19.

Try this in F2:
=IF(B2&C2="YN",0,IF(B2&C2="YY","SPR",IF(B2&C2="NY",IF(E2>=6000,LOOKUP(D2,B\$16:E\$16,B\$20:E\$20),TRUNC(INDEX(B\$17:E\$20,MATCH(E2,A\$17:A\$20,1),MATCH(D2,B\$16:E\$16,1))*(E2*IF(E2>=6000,0,1)-IF(AND(E2>500,E2<=750),500,0)),-1)))))

8. ## Re: multiple criteria computation!!

OMG YOU ARE A LIFE SAVER!!! thank you so much. its working perfectly!!! im so grateful for this!

9. ## Re: multiple criteria computation!!

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

##### Users Browsing this Thread

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

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