Conditional formatting - highlight the most similar number

Hello,

Not sure if this will be possible, but I will ask anyway...

Say I have the value 15.05 in a cell

And then I have a range of numbers in other cells in a column, say: 14.00 15.00, 16.00 etc

Can I use a conditional format rule to highlight the nearest number to 15.05, so in this case the cell wit 15.00 in it?

Cheers, Jon

Hello
Maybe a Conditional Formatting formula will do the trick. For example, if your value of 15.05 is in cell D2 and you range of values in F2:F8, then the following Conditional Formatting formula would highlight the next lowest number to D2.

=MATCH($D$2,$F$2:$F$8)=ROW()-1

Note the -1 after the ROW function. If your range started in F3 for example then this would have to be -2 and so on.

Hope this helps.
DBY

Hi DBY,

Thanks for your reply - unfortunately that doesn't work I am afraid.

I am also trying to match it to the nearest number, rather than the next smallest number, and so it could be bigger or smaller.

Any more suggestions?

Cheers, Jon

Hi
Assuming you mean .5 and above goes to the next number, try this Conditional Formatting formula, as per the ranges in my previous formula:

=MATCH(MROUND($D$2,1),$F$2:$F$8)=ROW()-1

Hope this is more what you're after.
DBY

Hi DBY,

Thanks again for you post, but again, I am still struggling!

Is it possible to post up an example worksheet? And if so, how?

Cheers, Jon

Hello Jon

If you could upload a sample file it might make things a bit clearer. Below is a link to the forum FAQ's which tells you how to upload a file with your post.

http://www.excelforum.com/faq.php?fa...b3_attachments

DBY

Hi DBY,

Thanks for your response and apologies for the delay in posting an example.

I hope the attached workbook makes sense - I have an example of the problem, and two examples of how the solution would look.

Many thanks,

Jon