Nov 19 2019 01:00 PM
In the attached file I am trying to figure a way to do the following steps, in said order:
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
Nov 19 2019 02:35 PM
SolutionAs 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...
Nov 20 2019 04:05 AM
@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.
Nov 20 2019 04:09 AM
@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<>"")
Nov 20 2019 07:27 AM
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.
Nov 19 2019 02:35 PM
SolutionAs 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...