Nesting issues

My first thought is, describe first in a natural language under which conditions something has to be calculated and supplement this description with concrete examples.

By the way:
I think the formula contains strange conditions, which react very differently, as you expect.
And also different as I expected.
For example: =OR(J4={"REF","OTDT"},AND($B$2:$P$2,"12",$B$3:$P$3,"OT W","<=2.00") ) returns TRUE. I myself expected #VALUE. I think you expected FALSE.

Our worksheet is to track overtime that people work (OT W) and at the end of each quarter, we are required to keep everyone within 10% of the top overtime worker. There are two different overtime lists people can sign up for per quarter, 10 hours (2 hrs of OT) and 12 hours (4 hrs of OT) that apply each day. When someone is not available to work OT, then they are marked unavailable ("U"), and the reason why under their "Key" column, which has a drop-down list to select the reason. The worksheet we are trying to create is to average out the hours that people worked on any given day, and depending on which list they are on (10/12), what to charge those that were unavailable based on the reason "Key". Anyone on the 10/hr list will be charged for any difference the 12/hr list works over 10 hours, unless they are mandated to work over 10 hours, to help keep them within the 10% of the top worker we are required to. We've been manually calculating every day OT is worked and are trying to come up with a solution with this as sometimes we have around 25 people on the overtime lists and it's very time consuming. This is the breakdown of the formula in C4 and each "Key" reason someone could be unavailable, and how we expect them to be charged:
D4=

"",0 - there was no OT that day or the person worked the OT offered so there is no reason to charge them unavailable.

"HOL",0 - Holiday schedule, we have rotating days off, so if someone works their day off and it's also on a holiday or holiday schedule, the 8 hours of OT does not count toward the equitability. If they work more than 8 hours on their day off, then anything over 8 is counted. This just helps us track why they work a day off and it's not computed on the worksheet.

"MU",0 - If someone wasn't within the 10% at the end of the previous quarter, they are given the difference as a negative amount in the "OT W" in the new quarter and this is how we track it so hopefully they'll be caught up by the end of the current quarter. (Make Up hours)

"MAX",0 - If someone has already worked the maximum hours allowed, per our contract, we can no longer offer them or charge them unavailable, usually only happens during our peak season and they work both days offs along with some daily OT. This is how we notate why there's no OT W or Unavailable.

"OFF LIST",0 - At any time, an employee can get off the overtime list they signed up for and this is how we notate it on the worksheet as to why they will have no more OT worked or unavailable charged.

"RDO",8 - This is the employee turning down an offer to work their day off, they gave up 8 hours of overtime. (Refused Day Off)

Here is the part that comes to the averaging:

"10LIST" - This is to keep the 10 hr list equitable to the 12 hr list. Applies when the 12 hr list works over 10 hours (more than 2 hours of OT) and the 10 hr list only worked up to 10 hours. Averages the the hours the 12 list worked over 2 hours and then subtracts what the 10 list worked. So on 5/23, 2 of the 12 list worked an average of 3.67 hours, their was one 10 list that worked 1.67 hrs so they are marked "10LIST" and charged 2 hours unavailable. 3.67-1.67=2.00, The two that were marked "LV" (on leave/vacation/called in sick) are charged the whole 3.67 hrs regardless of what list they are on.

"REF" or "OTDT" - Refused/Overtime in Downtime: The employee refused/declined some or all of the overtime offered, or they did the some or all of the overtime offered faster than anticipated (less than 8 hrs) and then refused/declined to do more. Again, this will average what the others worked, and depending if the person that refused or did work some OT, that will be subtracted from the average and they will be charged unavailable for the difference. This is a bit tricky because unlike the "10LIST" where the formula takes only the 12 list people to average the OT W, this could be a combination of both 10 and 12 lists that work the full OT offered so defining which OT W to calculate the average can either be over 2 hrs or under 2 hrs. The other caveat is that the average can't include what the "REF" or "OTDT" carriers worked and sometimes the 10LIST if under 2 hrs, because it skews the average to lower than what it should be. As you can see in the worksheet I attached, it works for the over 2 hrs, but not for the 2 hrs and under. I did try create a formula to exclude "REF", "OTDT", and "10LIST" in the average but couldn't figure it out to work correctly. For this one on 5/27, there was a 12 list that worked 2.66, a 12 list that did part if on down time and only worked .50 of overtime, and a 10 list that worked 1.98. To get the everyone to what the 12 list worked at 2.66, I need the formula to average anything over 2 hrs. In this case, just the one person. The formula works where it took the 2.66 and then subtracted what the other 12 list worked, .50 and charged unavailable for 2.16 for OTDT, and the for the 10 list person, subtracted what they worked 1.98 to charge them .68 for 10LIST.
The formula doesn't work when it needs to average time at 2 hours or less as seen on 5/29.

"LV" and "SC" - The employee is on some kind of leave (vacation/sick) or they are on a change of schedule where they are not allowed to work OT that day. This charges them unavailable for the full amount of the average the other people worked. The formula works for anything worked over 2 hrs but not 2 hrs and under. This is similar to the REF, OTDT, and 10LIST where some of them need to be excluded from calculating the average and also there is nothing to subtract from the average. On 5/23 and 5/24 it works because the hours are over 2.00, but doesn't work on 5/28 or 5/29.

I hope that helps explain what we're trying to accomplish. We've been working on this sporadically for the last couple of months.

Thank you for your explanation, but not everything is clear to me.
For the following questions I refer to your formula in cell C4.

Please Login or Register  to view this content.

Looking at your formula it seems that if the code is not 10LIST you don't want to distinguish between the 10's and 12's.
After all, the averages in lines 3 and 4 are the same.
This also applies to the averages in lines 5 and 6, the averages in lines 7 and 8 and the averages in lines 9 and 10.
Is that right?

I don't understand when exactly which average should be calculated.
Your condition states something like >2.00 and <= 2.00, but it is unclear to me what should be >2.00 or less than 2.00. [In case of the formula in C4].
Even if the reference is to multiple numbers, what should happen when part of those numbers is greater than 2 and another part is less than 2?
After all, only 1 average can be calculated by the formula.

In addition, the conditions you mean must be written down in a completely different way.
The condition in line 3 always returns TRUE (to my surprise, because I expected an error).

Do I understand correctly that if at least 1 value OT W is present in a row with a value > 2.00, then lines 3 to 6 of the formula should be used
and if no value is greater than 2.00, then lines 7 to 10 of the formula must be used.

I don't yet fully understand the distinction you want to make in lines 3 to 10 with the 10's and 12's.
After all, whether line 3 or line 4 of the formula is executed, it will lead to the same average.
After all, the calculation of these two lines is exactly the same.

The same applies to lines 5 and 6, lines 7 and 8 and lines 9 and 10.

Please can you go into more detail about this?

For some reason I can get it to work independently, but not nested.

The problem is not the nesting, the problem here is the way the conditions are formulated is not right. This needs to be adjusted on several points, but because I don't exactly understand the conditions you mean, I'll ask you more about this in detail.

Please try in C4 and copy down and copy to the other columns:

Please Login or Register  to view this content.

Thanks for the feedback and rep . Glad to have helped.

In this formula translates the function N the result of a Boolean expression TRUE or FALSE in 1 or 0, after which SUM is possible.

Other methods to translate the result of a Booelan expression into a 0 or a 1 is to place the expression in a calculation (with an operand).
For example, by placing 0+ or ​​1* or -- in front of it.