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

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

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

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

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