Using Conditional Formatting in #Excel to Highlight the N Closest Values to the Mean of a Range by David Hager

 

Conditional Formatting (CF) is one of the most powerful tools in Excel for visualizing data. Because CF can use formulas as input to the CF process, the ability to create formulas based on different data visualization requirements is important. In this demonstration, the goal is to highlight values in a numeric range that are clostest to the average of that range. So, we first make the range dynamic with the following defined name formula.

NumRange =OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1)

Next, we use that range to make an array of the absolute differences of each value of the range from the average.

ABS_Range =ABS(NumRange-AVERAGE(NumRange))

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. 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.

 CF_Closest1

The example file can be downloaded here.

CF_Closest

 

Advertisements

3 thoughts on “Using Conditional Formatting in #Excel to Highlight the N Closest Values to the Mean of a Range by David Hager

  1. 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

  2. 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

  3. Pingback: Using Conditional Formatting in #Excel to Highlight the N Closest Values to the Mean of a Range by David Hager — Excel For You – SutoCom Solutions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s