I have a list of values in a column that is added to every other day ...
Using =AVERAGE(SMALL((OFFSET(J4,COUNT(J:J),0,-20)),ROW(1:8))) I get the AVERAGE of the lowest 8 values in the latest 20 values ...
And if I use =SMALL((OFFSET(J4,COUNT(J:J),0,-20)),ROW(1:8)) I get an array of those 8 values ...
But how can I fill (eg in yellow) the cells containing those 8 values?
If I put =SMALL((OFFSET(J4,COUNT(J:J),0,-20)),ROW(1:8)) into a Conditional Formatting rule, it fills the cells containing the first 10 values of the latest 20 values!!!
I must be missing something?
Thanks in advance ...
Using =AVERAGE(SMALL((OFFSET(J4,COUNT(J:J),0,-20)),ROW(1:8))) I get the AVERAGE of the lowest 8 values in the latest 20 values ...
And if I use =SMALL((OFFSET(J4,COUNT(J:J),0,-20)),ROW(1:8)) I get an array of those 8 values ...
But how can I fill (eg in yellow) the cells containing those 8 values?
If I put =SMALL((OFFSET(J4,COUNT(J:J),0,-20)),ROW(1:8)) into a Conditional Formatting rule, it fills the cells containing the first 10 values of the latest 20 values!!!
I must be missing something?
Thanks in advance ...