+ Reply to Thread
Results 1 to 4 of 4

Formula to return a code per row based on multiple criteria and steps-EXPERT needed

  1. #1
    Registered User
    Join Date
    09-11-2018
    Location
    South Africa
    MS-Off Ver
    Microsoft Office 365
    Posts
    7

    Talking Formula to return a code per row based on multiple criteria and steps-EXPERT needed

    Good day Excel forum,

    I need the help of an expert to solve a puzzling and challenging problem.

    I will try to explain in the shortest way possible, but need to be thorough to explain in clarity.

    An excel sheet has been attached to be used to work out the formula needed.

    Any help will be greatly appreciated.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Formula to return a code per row based on multiple criteria and steps-EXPERT needed

    Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!

  3. #3
    Registered User
    Join Date
    09-11-2018
    Location
    South Africa
    MS-Off Ver
    Microsoft Office 365
    Posts
    7

    Re: Formula to return a code per row based on multiple criteria and steps-EXPERT needed

    Good day Excel forum,

    I need the help of an expert to solve a puzzling and challenging problem.

    I will try to explain in the shortest way possible, but need to be thorough to explain in clarity.
    In the table below, I have need of a formula in the "Code" column to do some very specific calculations regarding multiple criteria as outlined in the steps below.

    An excel sheet has been attached to be used to work out the formula needed.

    Attachment 623750

    Formula breakdown

    1) Step 1 sum all hours by Emp ID and weeknum not equal to 7
    2) Step 2 calculate overtime by summing difference between sum of hours and over column and deposit in "over" column
    3) Step 3 check in "hours" column if a value exists which is the same as the "over" column, return TRUE, FALSE
    4) step 4 if step 3 is TRUE, find the row where this value is stored in "hours" column and deposit value in same row of column "Hours2"
    5) step 5 return code 1 in all rows for emp ID in column "Code" except for the value obtained in step 4, use code 2 instead
    6) if a value for "over" does not exist in the "hours" column, replace the minimum value found for emp ID with real number in "Hours2" column from value in "over column"
    7) step 7 replace maximum value in "Hours" column for Emp ID with 45-(helper column value-over value-max value in list)
    8) return code 1 in all rows for Emp ID except the value obtained for "over" column, use code 2 instead

    Any help will be greatly appreciated
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Formula to return a code per row based on multiple criteria and steps-EXPERT needed

    I am confused. As it would appear that you have already completed step 1 using the SUMIFS function as seen in cell E7 I'll move on to step 2.
    It sounds as if you want to use the values already in cell E7:E15 to calculate a new value and then replace the existing values in E7:E15 with the new value. That would produce a circular reference. Perhaps clearing up a bit of the confusion will help us find appropriate formulas/code to resolve the issues.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Expert needed... Validation On Multiple Sheets
    By timflinn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-12-2014, 11:32 AM
  2. Array formula to return multiple values based on various (OR) criteria
    By dancing-shadow in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-20-2013, 05:12 AM
  3. Replies: 7
    Last Post: 09-06-2013, 11:59 PM
  4. VBA Code that will return max date based on multiple criteria
    By dubuquer in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-19-2013, 02:13 AM
  5. Additional steps needed to this formula to calculate multiple if scenarios...
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-07-2013, 01:11 AM
  6. [SOLVED] Formula to Return ID based on Multiple Criteria
    By boldcode in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-09-2013, 02:54 PM
  7. Formula needed to return multiple results based on one reference
    By AMSBenji in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-08-2013, 12:15 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