Highlight cells, closest to zero and a specified number, and corresponding cells

I need help with the following:

1. Highlight the cell that contains the number closest to zero in the "-1.6" columns
2. Highlight the cell that contains the number closest to 1.600 in the "+/-%" columns
3. Highlight the cell that corresponds to the closest number to 1.600 in the "POST C276 +/-%" columns
4. Apply to all rows

image is attached to help visually bc this forum does not support tables


help.JPG

# SN TBD 10pF +8% -1.6 +6% -1.6 +4% -1.6 -5% -1.6 -9.2% -1.6 -13% -1.6 post c276 +8% post c276 +6% post c276 +4% post c276 -5% post c276 -9.2% post c276 -13%
1 1234 75 1.84221 1.98959 0.3896 1.95274 0.3527 1.91590 0.3159 1.75010 0.1501 1.67273 0.0727 1.60272 0.00272
2 1235 59 1.70000 1.83600 0.2360 1.80200 0.2020 1.76800 0.1680 1.61500 0.0150 1.54360 -0.0564 1.47900 -0.12100

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

thanks for the advice, Pepe Le Mokko
i'll update and upload in a bit

i updated and uploaded a workbook with before and after sheets

To compute the nearest values I needed an array formula.
As conditional formatting can't use array formulas I need a helper row (row 13).
In F13, H13, J13, L13, N13, P13, R13:W13

Formula:

=INDEX(F2:F5,MATCH(MIN(ABS(F2:F5-1.6)),ABS(F2:F5-1.6),0))

In G13, I13, K13, M13, O13, Q13

Formula:

=INDEX(G2:G5,MATCH(MIN(ABS(G2:G5)),ABS(G2:G5),0))

Apply the follwing conditional formatting to F2:W5

Formula:

=F2=F$13

and select bold/underline

See the attached workbook where I have updated the "before" sheet.

hi guys,

thank you for all the feedback and help
it's been great

i need help with one more thing

how can i highlight a cell according to a value...
according to the MOCKBOOK_gw
if Q1 is the closest to zero, i'd like to give Q1 and P1 a certain format, like a blue font
or if O2 if the closest to zero, i'd like to give O2 and N2 a certain format, like a purple underlined bold font
and so on

thanks you guys!

Hi FlameRetired!

Thanks so much for helping me out
this is my first time getting into excel

the two formulas you sent, it really helped me out


could you explain to me the formulas you posted on the thread??
i need to apply it to some more rows and a thorough explanation would really help.

For F2:Q5
=OR(F2=INDEX($F2:$Q2,MATCH(MIN(IF(--ISNUMBER(FIND("%",$F$1:$Q$1)),ABS($F2:$Q2-1.6))),IF(--ISNUMBER(FIND("%",$F$1:$Q$1)),ABS($F2:$Q2-1.6)),0)),F2=INDEX($F2:$Q2,MATCH(MIN(IF($F$1:$Q$1=-1.6,ABS($F2:$Q2))),IF($F$1:$Q$1=-1.6,ABS($F2:$Q2)),0)))

For R2:W5
=AND(INDEX($R2:$W2,MATCH(1000,$R2:$W2,1))=R2,(MATCH(1000,$R2:$W2,1)-1)*2+1=MATCH(MIN(IF(--ISNUMBER(FIND("%",$F$1:$Q$1)),ABS($F2:$Q2-1.6))),IF(--ISNUMBER(FIND("%",$F$1:$Q$1)),ABS($F2:$Q2-1.6)),0))


thanks again, you're lifesaver FlameRetired!
joshsrai

Sorry, but I'm not sure I'm clear on what you are asking. Are you now looking for the smallest values across all columns? So, per MOCKBOOK_gw.xlsx, you are looking for the smallest value out of G4 (=.0187), I4 (=-.0113), K5 (=0.0149), M3 (=0.0374), O3 (=-0.0350), Q2 (=0.0027) which would be Q2?

If not then please try to clarify.

Hi GeoffW283,

sorry for the confusion
the original issue/request has already been solved
but i do need some help in highlighting values...let me explain
we'll use the MOCKBOOK_gw.xlsx as the example
In row two, P2 and Q2 are Conditioned Formatted to be underlined and bold because P2 is closest the value 1.6 and Q2 is closest to 0
In row three, N3 and O3 are Conditioned Formatted to be underlined and bold because N3 is closest the value 1.6 and O3 is closest to 0
so on and so forth

the thing i need help with is,
how do i CF P2 and Q2 to be BLUE(or what ever formatting) and N3 and O3 to be Green...
i'd like each group (P&Q, N&O, L&M, J&K, H&I, F&G) to have different formatting, i.e. color

i hope this clarifies...

thank you again for the help

Like this?

MOCKBOOK_CF.png

Yes sir!
here's the formula i used to get the lowest value to 1.6
=ABS(F2)=MIN(ABS($G2),ABD($J2),ABS($M2),ABS($P2),ABS($S2),ABS($V2),ABS($Y2))

I'm not sure what you are telling me with the formula. The "1.6" columns are G, I, K, M, O, Q (at least in MOCKBOOK_gw.xlsx). Your formula is looking at G, J, M, P, S which is a mixture of column types.

Relative to MOCKBOOK_gw.xlsx the conditional formatting formulas are simply =N2=N$13, -P2=P$13 etc. as shown in post 12.

Are you trying to adapt the conditional formatting to Dave's post #6 formulas which dispensed with the helper row 13 (which I rely on for the answer I provided) ??

i've uploaded an updated version "MOCKBOOK_gw_2" with desensitized data in it

if Columns F and G are the closest to 1.6 and zero, respectively, then it should be filled purple with white font.

if Columns I and J are the closest to 1.6 and zero, respectively, then the border should be red with black font.

if Columns L and M are the closest to 1.6 and zero, respectively, then it should be filled green with black font.

if Columns O and P are the closest to 1.6 and zero, respectively, then it should be filled black with white font.

if Columns R and S are the closest to 1.6 and zero, respectively, then it should be filled yellow with black font.

if Columns U and V are the closest to 1.6 and zero, respectively, then it should be filled Pink with black font.


hope this helps

First, you had about a hundred CF rules on the C276 Percentages sheet - FYI I have removed these in the attached workbook.

As you want different formatting for each pair of cell columns then there has to be separate CF rules for each pair if columns.
Here is the formula for F3:G10

Formula:

=ABS($G3)=MIN(ABS($G3),ABS($J3),ABS($M3),ABS($P3),ABS($S3),ABS($V3),ABS($Y3))

Here is the rule for I3:J10:

Formula:

=ABS($J3)=MIN(ABS($G3),ABS($J3),ABS($M3),ABS($P3),ABS($S3),ABS($V3),ABS($Y3))

The formula for the additional column pairs is similar - the only change is $G3 >> $J3 >> $M3 etc.
In the attached file I have implemented these formulas and your required formatting for F:G and I:J.

Let me know if I have understood your requirement correctly. If so then I will leave it to you to complete the remainder of the column pairs!