Conditional Formatting (Highest value closest to that of a cell)

Good day guys and sorry if such a question has been answered already in the past, but since I'm not an expert in Excel with conditional formatting, I would really like a "customized" advice.

The scenario:

Excel.jpg

According to the Excel above, my intention is to find the value in columns F, I, L, O, R, U, W which is closest to the one in cell B44 but does not surpass it (meaning the highest closest which is lower than that).

I did try the following syntax "=ABS(F6-$B$44)=MIN(ABS($F$6:$F$40-$B$44))" per column of interest, but as you can notice it only highlights the closest value, regardless the fact that it is higher than the B44 one. In this case, I need a formula that would highlight 44,7 from column F, 41 from column I etc, since these are the value which are closest to B44 one, without surpassing it.

Any ideas?

Already thankful for your kind support and advice.

Can you attach a small sample workbook rather than an image?

Go Advanced -> Manage Attachments -> Upload

Sure thing kersplash. Attached a sample.

Originally Posted by kvsrinivasamurthy

Pl see file.
In B45 then dragged across

Please Login or Register  to view this content.

Thank you very much for your trouble my friend, but I am afraid that the result is not what I was looking for. Specifically:

1) Instead of having the values written in other cells, I would prefer it if the requested values were just highlighted in the specific columns (thus the different color of F18 and I17 that I tried the forumla), the moment you inserted a value in B41. This was achieved through the conditional formatting I mentioned initially, but the result was not correct.

2) I am afraid that the formula you sent me was the exact opposite of what I was looking for. I think that it brings back the closest value per column higher than the B41 cell's value. What I need in particular is to have the closest value to the one in B41 that does not exceeds that. For example, the proper values that should be highlighted are the ones below:

F = 44,7
I = 41
L = 43,4
O = 45,9
R = 41
U = 43,3
W = 45,2

Hope I assist you some more.

How about
=ROW(F3)-2=MATCH(B$41,F$3:F$37,1)

Originally Posted by kvsrinivasamurthy

In B45 then copied across

Please Login or Register  to view this content.

Thank you very much kvsrinivasamurthy. This formula does the trick and brings back the correct values per column.

Still, though, the ideal scenario would be to highlight those values and not retrieve them and present them in others cells. Since I can not guarantee that something like that can be achieved after all, your solution is probably the next best thing, if I could also copy in a row below 45 the corresponding cells of column A. Something like the following:

F ----- I ----- L ----- O ----- R ----- U ----- W
44,7 -- 41 --- 43,4 --- 45,9 --- 41 --- 43,4 -- 45,2
29 ---- 28 ---- 26 ---- 26 ----- 24 --- 24 ---- 24 (values copied from Column A)

Hope the above makes sense. Any additional ides on that?

Originally Posted by Fluff13

How about
=ROW(F3)-2=MATCH(B$41,F$3:F$37,1)

I did try the above formula my friend, but all I got back was a "FALSE" result. With my limited knowledge on Excel, what I understand is that the above formula is searching for a value in F column that matches that of B41 cell, which is not what I am looking for.

Btw, what is it with commas and question marks? It seems that in all the formulas above, I had to replace the commas with a question mark in order to work.

That works wonders!!!

Thank you very much for managing to provide the perfect solution after all. This is really amazing!

Grateful for your effort and complete answer!

Originally Posted by Sontal

I did try the above formula my friend, but all I got back was a "FALSE" result.

That formula was for conditional formatting & should be used the same way as kvsrinivasamurthy did in the workbook in post#10