How do i highlight a cell that is the closest number to a different cell.

[dbravo]

Please Login or Register  to view this content.

New member so forgive my lack of formating on this site...pretend it is on the top left beginning of the excel page.

I am running a time series linear regression on a credit card company. The left most column is the amount of cards they had on a certain year and the prediction is the cards the regression formula predicted. The + and - are the columns added or subtracted the standard error which is 3.25. I am trying to highlight the closest +/- number to the original cards. For example, in the first Row 76.98 is closer to 78 than 83 is so i would like to have 76.98 highlighted...showing that subtracting the standard error gave us the closest prediction. The second column the 87.085 cell should be highlighted because adding the standard error gave us the closest prediction to 86.4.

Thank You in advance!!

[JBeaucaire]

Assuming those +/- values are in columns C and D:

1) Highlight C2:D9 (or as far down as needed)
2) Apply this conditional formatting formula:

=MIN(ABS($A2-$D2),ABS($A2-$C2))=ABS($A2-C2)

http://screencast.com/t/6ACwcO7J2l

[Bernie Deitrick]

If your data is in columns A to D, with headers in row 1, select from B2 to D??? - with B2 as the activecell - and use CF with the formula option, using the formula

=ABS($A2-B2)=MIN(ABS($A2-$B2:$D2))

[dbravo]

You are awesome! Thank You

[JBeaucaire]

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.