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.

 

[Eric W]

Select C2:G2 and try this CF formula:

=OR(C2=LOOKUP($B2,$C2:$G2),B2=LOOKUP($B2,$C2:$G2))

This highlights both the under and over amounts. It should be robust against changing the location.

And as another thought, if you use a VBA macro to import the data, you may want the same macro to do the highlighting, instead of using CF.

 

[DixiePiper]

First, thank you all for taking time to try and help me. Second, I think things have gotten a bit garbled and for that, my apologies.

I tried to provide a simplified example but maybe it was too simple. Let me try again: (column/row references are actual)

1. My worksheet has a large table, the size of which is dynamic based on data imported into Column C.
2. With the exception of two columns, every other column is formula driven based on the values in Column C.
3. I need to analyze one of the data columns against the metric of survey data. Each row will have a different reference value based on user input and varying metrics based on the value of Column C.
4. The actual structure of the table is still in flux (adding/deleting columns) but all the reference values are in the same Column and all comparison values are in five consecutive columns.

Maybe this example will work better:
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]Category[/TD]
[TD="align: center"]Salary[/TD]
[TD="align: center"]Ref. Value[/TD]
[TD="align: center"](blank)[/TD]
[TD="align: center"]10th
[/TD]
[TD="align: center"]25th[/TD]
[TD="align: center"]50th[/TD]
[TD="align: center"]75th[/TD]
[TD="align: center"]90th[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]imported data which drives the formulas in all but Cols G and Col J[/TD]
[TD="align: center"]Keyed data entered by user; Col M is equal to Col G for the purpose of in-cell data analysis using CF data bars based on an INDIRECT formula[/TD]
[TD="align: center"]$135[/TD]
[TD="align: center"](currently a placeholder)[/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 actual content of each row is variable based on the data in Col C. Col M pulls from Col G, Cols O:S are based on INDEX+MATCH formulas that pull data from a separate tab.

I need some way to compare the value of M12 against the range O12:S12 and highlight the closest over/under values. For Row 12, that would be P12:Q12.

Because the size of the table is dynamic based on the contents of Col C, I am using a VBA script to refresh the CF in the "analysis" section. Col M has a data bar workaround that has to be copied to each cell individually and I have a VBA shortcut for that. I will simply link the same copy/paste formatting to Cols O:S.

Again, thank you all for your precious time.

 

[Eric W]

The formula I posted should still work. Select O12:S12 and enter this CF formula:

=OR(O12=LOOKUP($M12,$O12:$S12),N12=LOOKUP($M12,$O12:$S12))

which, come to think of it, can be shortened to:

=OR(LOOKUP($M12,$O12:$S12)=N12:O12)

 

[DixiePiper]

Eric,
Thank you so much for taking the time to restate your original solution. I got confused and couldn't figure out the references. Thank you for your time and patience.

DixiePiper

 

[Eric W]

Glad it works for you! I probably should have mentioned that the N12 reference intentionally points to the column just before the first column with data. It makes no real sense for the first column, but with relative referencing, it makes the rest of the columns look for the previous value. Without knowing that, it might have been tricky changing the references.

Thanks for the feedback!

 

[DixiePiper]

Thanks. I had to play with the referencing and setup when asking VBA to do the heavy lifting of copying the formatting. Your explanation explains a few of the hiccups I encountered. It works brilliantly and I grateful for your help.

DixiePiper