Highlight Closest Over/Under Values in Horizontal Range

[DixiePiper]

I have searched and searched on this and if there is an answer, I've either not found the magic combination of words to conjure the solution from the depths of the internet or cannot understand my own search result. As such, apologies for any redundancies.

I have a dynamic table in which I want to include some basic data analysis. The number of rows is variable based on data imported via VBA. I have a script to copy the conditional formatting so I only need a solution applicable to a single row.
I need to be able to highlight the closest over/under values to a given number. My table is set up as follows:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Ref. Value[/TD]
[TD="align: center"]LOW[/TD]
[TD="align: center"][/TD]
[TD="align: center"]MID[/TD]
[TD="align: center"][/TD]
[TD="align: center"]HIGH[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]$135[/TD]
[TD="align: right"]$111[/TD]
[TD="align: right"]$124[/TD]
[TD="align: right"]$139[/TD]
[TD="align: right"]$157[/TD]
[TD="align: right"]$173[/TD]
[/TR]
</tbody>[/TABLE]

The end result for the above example would be highlighting applied to C2 and D2, representing the closest under (C2) and the closest over (D2).

Caveats and Assumptions:

• my initial read is I should be able to accomplish this with two separate conditional formatting rules
• there will be instances where the reference value is outside the parameters of the range; no formatting here is acceptable as I can identify that elsewhere
• my goal is a visual "at a glance" idea of where the reference value falls on the scale.

Advance thanks for any help.

 

[Gerald Higgins]

Hi, welcome to the board.

There are probably lots of ways of doing this.

Are you values on each row always sequenced as shown, i.e. lowest value in col B, highest value in col F, etc, and only ever 5 values in total per row ?

 

[dmhexcel]

Hi, give this a try in the conditional formatting section:
=MATCH($A2,$B2:$F2,1)=COLUMN(B2:F2)-1
It should highlight the lower bound. Changing it to
=MATCH($A2,$B2:$F2,1)=COLUMN(B2:F2)-2
should highlight the upper bound in a different colour. It's quite specific to your problem in terms of it being defined by the number of columns and stuff, but play around with the match less than and greater than function.

 

[dmhexcel]

Hi, welcome to the board.

There are probably lots of ways of doing this.

Are you values on each row always sequenced as shown, i.e. lowest value in col B, highest value in col F, etc, and only ever 5 values in total per row ?

Oh yeah I forgot to ask that, my solution is dependent on them always being formatted that way.

 

[DixiePiper]

Gerald, thank you for chiming in.

Yes, the values are always sequenced as shown and there are only ever 5 values per row. FYI, the listed Col/Row are for reference only. This is part of a much larger table. Essentially it's a long list of labor categories with corresponding market data. I want to be able to compare the desired salary (Col A in the example) with the percentiles from the survey (Col B-F in the example) and highlight where Col A value falls in reference to the Col B-F data points.

My preference is for the most elegant and stable solution. I am not a programmer by trade and while I'm the primary user of this workbook, there may be occasion when it falls into other hands. I have gotten my feet wet in VBA (although it's been a few years since my last project) and I can generally figure things out with a bit of assistance and a lot of resolve.

 

[DixiePiper]

Hi, give this a try in the conditional formatting section:
=MATCH($A2,$B2:$F2,1)=COLUMN(B2:F2)-1
It should highlight the lower bound. Changing it to
=MATCH($A2,$B2:$F2,1)=COLUMN(B2:F2)-2
should highlight the upper bound in a different colour. It's quite specific to your problem in terms of it being defined by the number of columns and stuff, but play around with the match less than and greater than function.

dmhexcel, I appreciate the input but it didn't seem to work; I'm not sure why. The only thing I changed were the Col/Row references to match my worksheet which shouldn't impact anything. I wondered if it was an issue with the reference value as formula but using a hard-keyed value didn't work either. Am I missing something?

 

[DixiePiper]

Let me add that while I'm not highly skilled with VBA, I am fairly proficient in Excel.

 

[dmhexcel]

Changing the column reference will change things. I would change it to column(A#:LastCol#) and remove the -1 then if you want to make it work. How is your actual sheet formatted? because this will work for a sheet that looks like this:
A B C D E F
[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]$135 [/TD]
[TD="class: xl64, width: 64"]$111 [/TD]
[TD="class: xl64, width: 64"]$124 [/TD]
[TD="class: xl64, width: 64"]$139 [/TD]
[TD="class: xl64, width: 64"]$157 [/TD]
[TD="class: xl64, width: 64"]$173 [/TD]
[/TR]
[TR]
[TD="align: right"]120[/TD]
[TD="class: xl64, width: 64"]$111 [/TD]
[TD="class: xl64, width: 64"]$124 [/TD]
[TD="class: xl64, width: 64"]$139 [/TD]
[TD="class: xl64, width: 64"]$157 [/TD]
[TD="class: xl64, width: 64"]$173 [/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD="class: xl64, width: 64"]$111 [/TD]
[TD="class: xl64, width: 64"]$124 [/TD]
[TD="class: xl64, width: 64"]$139 [/TD]
[TD="class: xl64, width: 64"]$157 [/TD]
[TD="class: xl64, width: 64"]$173 [/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="class: xl64, width: 64"]$111 [/TD]
[TD="class: xl64, width: 64"]$124 [/TD]
[TD="class: xl64, width: 64"]$139 [/TD]
[TD="class: xl64, width: 64"]$157 [/TD]
[TD="class: xl64, width: 64"]$173 [/TD]
[/TR]
[TR]
[TD="align: right"]160[/TD]
[TD="class: xl64, width: 64"]$111 [/TD]
[TD="class: xl64, width: 64"]$124 [/TD]
[TD="class: xl64, width: 64"]$139 [/TD]
[TD="class: xl64, width: 64"]$157 [/TD]
[TD="class: xl64, width: 64"]$173 [/TD]
[/TR]
[TR]
[TD="align: right"]140[/TD]
[TD="class: xl64, width: 64"]$111 [/TD]
[TD="class: xl64, width: 64"]$124 [/TD]
[TD="class: xl64, width: 64"]$139 [/TD]
[TD="class: xl64, width: 64"]$157 [/TD]
[TD="class: xl64, width: 64"]$173 [/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD="class: xl64, width: 64"]$111 [/TD]
[TD="class: xl64, width: 64"]$124 [/TD]
[TD="class: xl64, width: 64"]$139 [/TD]
[TD="class: xl64, width: 64"]$157 [/TD]
[TD="class: xl64, width: 64"]$173 [/TD]
[/TR]
[TR]
[TD="align: right"]180[/TD]
[TD="class: xl64, width: 64"]$111 [/TD]
[TD="class: xl64, width: 64"]$124 [/TD]
[TD="class: xl64, width: 64"]$139 [/TD]
[TD="class: xl64, width: 64"]$157 [/TD]
[TD="class: xl64, width: 64"]$173 [/TD]
[/TR]
</tbody>[/TABLE]

 

[dmhexcel]

Hey I made a change to the original thing I posted. Try:

=MATCH(LookupValue,LookUpRange,1)=COLUMN(StartOfTable)

Start of table shouldn't have any $'s in it, should be something like A2
This should identify the lower bound.

=MATCH($A2,$B2:$F2,1)=COLUMN(A2)

(take note of the dollar signs and stuff though)

 

[dmhexcel]

Wait no, sorry, keep it in column A. My bad again....