Conditional Formatting with multiple conditions

[Eklavya]

In the attached file I am trying to figure a way to do the following steps, in said order:

1. Highlight row every 10th day (completed)
2. Compare Column C values for every 10 day range and highlight in red the highest value (partially completed but need to automate it, currently have copy pasted conditional formatting for every 10 day range)
3. Compare Column D value for every 10 day range and highlight in green the lowest value (partially completed but need to automate it, currently have copy pasted conditional formatting for every 10 day range)
4. From 10 day range lowest value need to highlight 2 highest value's (Column C) pre and post lowest value date (need to figure this)

NOTE: New data will be added every day or week. Previous 10 day highlights should not be removed with new 10 day highlights, so if there is 6 months of data there would be ~18.25, 10 day ranges, therefore ~18 lowest value shown on the spreadsheet. Would prefer without Macro and/or VBA

[#2 · Accepted solution]

@Eklavya 

As for 2) and 3) you may apply rules with formulas

=(D2=AGGREGATE(15,6,1/((INT((ROW()-2)/10)+1)=(INT((ROW($D$2:$D$1000)-2)/10)+1))*$D$2:$D$1000,1))*(D2<>0)

 and

=(C2=AGGREGATE(14,6,1/((INT((ROW()-2)/10)+1)=(INT((ROW($D$2:$D$1000)-2)/10)+1))*$C$2:$C$1000,1))*(C2<>"")

As for 4) - have no solution so far, not for today...

[#3]

@Sergei Baklan This is good. Consulted an excel expert chat (ExcelChat by Got It,Inc), they are generally good but for this they did not have any any solution, wasted good 5-6 hours with them on this over an above trying to figure it out myself.

Really appreciate this.

The 4th one is a brain scratcher.

[#4]

@Sergei Baklan If you don't mind, can you walk me through the logic of the formulae 

=(D2=AGGREGATE(15,6,1/((INT((ROW()-2)/10)+1)=(INT((ROW($D$2:$D$1000)-2)/10)+1))*$D$2:$D$1000,1))*(D2<>0)

 and

=(C2=AGGREGATE(14,6,1/((INT((ROW()-2)/10)+1)=(INT((ROW($D$2:$D$1000)-2)/10)+1))*$C$2:$C$1000,1))*(C2<>"")

[#5]

@Eklavya 

Let start from inside. The

INT((ROW()-2)/10)+1)

returns 1 if the current row is in first 10 records, 2 for next 10, etc.

Next

INT((ROW($D$2:$D$1000)-2)/10)+1)

returns the same but as array for your entire range, i.e. array like {1,1,1,1,1,1,1,1,1,1,2,2,2,2,2...}

Comparing one with another we have array with 10 TRUE and with FALSE for the rest, depends on which row we are. Since equivalent of TRUE and FALSE are 1 and 0, next 1/(array) returns 1 for the rows from current ten and error (division on zero) for the rest.

Multiplying on range values

*$D$2:$D$1000

we have an array with column values for current ten records and others are errors.

AGGREGATE finds the first (4th parameter =1) smallest (1st parameter =15) value in the array (third parameter) ignoring all errors (2nd parameter = 6).

Next we use formula

=D2=AGGREGATE(...)

in conditional formatting rule. CF iterates our entire range to which CF rule is applied starting from first cell of the range D2. 

If for the current cell formula returns TRUE, CF applies to it defined in the rule format and do nothing in all other cases.

Rule in the formula returns TRUE if only current cell value is equal to minimum value of current ten returned by AGGREGATE.

In addition, we multiply above on (D2<>0) to exclude from formatting all blank cells in the range. Thus

=(D2=AGGREGATE(...))*(D2<>0)

 apply format if value for the current cell is equal to smallest in current ten AND cell is not blank.

Same for Max, but another range and first parameter in AGGREGATE is 14, i.e. it returns first largest value.