+ Reply to Thread
Results 1 to 9 of 9

multiple criteria computation!!

  1. #1
    Registered User
    Join Date
    11-09-2017
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    4

    Lightbulb 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. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    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. #3
    Registered User
    Join Date
    11-09-2017
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    4

    Re: multiple criteria computation!!

    Hi there! thank you for the reply!!

    ok, i re-read your reply. had a misunderstanding there. haha hang on

  4. #4
    Registered User
    Join Date
    11-09-2017
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    4

    Re: multiple criteria computation!!

    Hi there. as attached
    Attached Files Attached Files

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    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. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    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.
    Attached Files Attached Files

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    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. #8
    Registered User
    Join Date
    11-09-2017
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    4

    Re: multiple criteria computation!!

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

  9. #9
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excluding Same value in computation
    By khr0n0s in forum Excel General
    Replies: 19
    Last Post: 03-25-2017, 07:41 AM
  2. Computation in the same cell
    By jarr25 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-07-2015, 12:14 PM
  3. Computation of Criteria for Judging
    By marge30 in forum Excel General
    Replies: 1
    Last Post: 03-15-2015, 10:07 PM
  4. Overtime computation
    By gerard_gonzales33 in forum Excel General
    Replies: 0
    Last Post: 07-30-2014, 01:40 AM
  5. [SOLVED] UDF for sum of multiple criteria in multiple columns and single criteria in multiple colum
    By Ganesh7299 in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 11-22-2013, 04:26 AM
  6. Tax computation
    By bfjunio05 in forum Excel General
    Replies: 4
    Last Post: 04-05-2012, 02:44 PM
  7. Tax Computation
    By harishs in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-10-2007, 08:07 AM

Tags for this Thread

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