Weekly Filter

If "weighted %" means the same thing it did in the last workbook I dabbled with for this OP, then he means that the percentages should be weighted based on the number of orders when combined, rather than averaged together. For example, if I have a 50% on time rate over 2 orders one day and a 90% on time rate on 100 orders the next day, then my total on time percentage could be the average of the two days (70%) or it could be the weighted percentage (89% or 91/102). I presume that the OP is looking for the latter. Using JohnTopley's attachment, I think the weighted average for totals can usually be obtained using SUMPRODUCT/SUM, so on 'All Day' in C4, I used:

=SUMPRODUCT(C6:C35,B6:B35)/B4

Adjust it for D4, then copy the pair across the row. The same structure can be used on 'Area Performance' in C9:D9, C21:D21, etc. The more substantial change I made was to the weekly area totals. Building off of JohnTopley's work, I tried the following in C15, which should be array-entered (confirmed with Ctrl + Shift + Enter):

=SUM(IF(($A15='All Day'!$B$2:$X$2)*('All Day'!$A$6:$A$35>=$B$13)*('All Day'!$A$6:$A$35<=$D$13)=1,'All Day'!$B$6:$W$35*IF('All Day'!C$6:Y$35="",0,'All Day'!C$6:Y$35),0))/$B15

Fill right and down. Modify the date cells for the other even weeks. For the odd weeks, you'll also need to update the $A15 and $B15 references accordingly. I spot checked a few and the numbers seem to hold up, but I'd advise a more thorough check. Presuming that I'm understanding weighted% correctly, hopefully this gets it?

The first IF clause checks our three conditions:

($A15='All Day'!$B$2:$X$2)*('All Day'!$A$6:$A$35>=$B$13)*('All Day'!$A$6:$A$35<=$D$13)=1

Correct area*after start date*before end date = 1. TRUE counts as 1 for each term, FALSE calculates as 0. We multiply the 3 terms so that only the entries for which all three criteria are TRUE return an "=1" value for the entire 3-part IF check. That's why the =1 is at the end of the clause.

If the IF clause discussed above is true, then we want to return the number of orders for that entry multiplied by the on time %, which will give us total number of on time entries (B6:W35 * C6:Y35). The first IF formula basically breaks down to: IF(all 3 criteria are met, THEN total multiplied by % on time, OTHERWISE zero). This returns an array of the On Time totals for all entries that fit the criteria. We then sum this array to get the on time total for all qualified entries and divide it by the Total (in B15) for the weighted percentage.

The second IF clause was meant as a workaround to avoid errors when a slot on "All Day" is left blank, like K17:L17. It just replaces blanks with zeroes. Consequently, I don't see any #VALUE errors, so I'm not sure which ones you're referring to. Maybe double check to see if the formula in that cell is array-entered? If that doesn't work, post an attachment that shows the errors.

Hopefully that helps? Walking through the formula step-by-step with 'Evaluate Formula' on the Formulas tab will also prove instructive.

I'm not clear on what this means:

it first looks at area performance D3:D8 if it's lower than 3% and if so highlight the highest number in B4:B8 ( the ones above 3% in D should not count in B)

It might be instructive to provide a more detailed explanation of what should happen. I'm likely offline until Monday, but hopefully JohnTopley and Jose Augusto are still subscribed, or maybe someone else can jump in.

Ah, I get it (I hope). You can protect against VALUE errors for blank rows with another little IF clause to replace "orders" blanks with zeroes the same we fixed "on time %" blanks. C27 could be (array-entered):

=SUM(IF(($A27='All Day'!$B$2:$X$2)*('All Day'!$A$6:$A$35>=$B$25)*('All Day'!$A$6:$A$35<=$D$25)=1,IF('All Day'!$B$6:$W$35="",0,'All Day'!$B$6:$W$35)*IF('All Day'!C$6:Y$35="",0,'All Day'!C$6:Y$35),0))/$B27

Fill down and right for that week. Adjust as described earlier for the other weeks.

If I'm understanding your conditional formatting request correctly, you can highlight the range to be colored (H3:H8 in the sample), select "new rule" and "use a formula", then use the following:

=$H3+0.00000000001*(1-$I3)=MAX(IF($J$3:$J$8>=0.1,$H$3:$H$8+0.00000000001*(1-$I$3:$I$8),0))

It should return the highest H value among those with J values of 10% or higher, using lowest I value as a tie-breaker. In this case, it returns H4 (South) at 86%. In your attachment, you highlighted H6, which is also 86% and has a lower late%, but H4 is actually 86.18%, whereas H6 is 86.00%. If you prefer to round the H values before calculating, wrap each of the H terms in the formula in a ROUND, ROUNDDOWN, etc. function as desired.

Thanks for fixing the Value thing, it works perfect.

I tried to add a Round formula to to the ON time formula in column a but it always turns out to show me 100%

If you're still trying to implement the ROUND functionality, you can include it in the conditional formatting formula:

=ROUND($H3,2)+0.00000000001*(1-$I3)=MAX(IF($J$3:$J$8>=0.1,ROUND($H$3:$H$8,2)+0.00000000001*(1-$I$3:$I$8),0))

OR

You can wrap the formula in H in ROUND. In H3 and filled down, try:

=ROUND(INDEX('All Day'!$B$4:$X$4,,MATCH($A3,'All Day'!$B$2:$X$2,0)+1),2)