+ 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
    6

    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 Moderator
    Join Date
    05-14-2009
    Location
    RDC
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    7,039

    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
    6

    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 2010/2019
    Posts
    7,117

    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)

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