I am extending the closest value technique I published recently to calculate the same based on a filtered list.
In this demonstration, the goal is to highlight values in a numeric range that are clostest to the average of that range in a filtered list. So, we first make the range dynamic with the following defined name formula.
NumRange =OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1)
Then, we modify that range to include only filtered values.
fNumRange =IFERROR(IF(SUBTOTAL(3,OFFSET(NumRange,ROW(NumRange)-MIN(ROW(NumRange)),,1)),NumRange,””),””)
For more information on the SUBTOTAL function as used here, see:
Next, we use that range to make an array of the absolute differences of each value of the range from the average.
ABS_Range =IFERROR(ABS(fNumRange-AVERAGE(fNumRange)),””)
We can then define a cell for the number of values to highlight.
N_Values =$B$2
The heavy work is done by the next formula, which creates an array of the N values to be higlighted.
Num_Array=INDEX(NumRange,N(IF(1,TRANSPOSE(MATCH(SMALL(ABS_Range,ROW(
INDIRECT(“1:”&N_Values))),ABS_Range,0)))))
This formula returns the position of each smallest deviation in the 2nd argument of the INDEX function, which then returns the values corresponding to those deviations, based on a filtered list. The use of the formula syntax needed to do this with the INDEX function is explained at the following link.
https://excelxor.com/2014/09/05/index-returning-an-array-of-values/
This formula can now be used in the creation of the CF, where CF Formula is =SUM(N(A1=Num_Array)), starting at A1 and applied to all of Column A.
The result of this CF is shown below.
The example file can be downloaded here.
Pingback: Conditional Formatting Gem in #Excel: Highlight the N Closest Values to the Mean of a Range in a Filtered List by David Hager — Excel For You – SutoCom Solutions
Pingback: #Excel Super Links #91 – shared by David Hager | Excel For You
Pingback: #Excel Super Links #138 – shared by David Hager | Excel For You