0

In Excel, I have 7 columns of data (numbers) spanning 100 rows. For each row, I want to check whether at least one number exceeds all other values by a certain value (say 0.2 or greater). If so, the row should be highlighted.

For example:

A       B        C       D       E        F       G
0.24    0.32     0.18    0.39    0.55     0.41    0.28     <-- Should not highlight
0.22    0.81     0.33    0.55    0.38     0.17    0.41     <-- Should highlight as column B exceeds all others by > 0.2
...
...

Given the number of possible combinations in each row, manually subtracting each column from all the others is not an ideal solution. Any ideas on a better way to tackle such a problem? I'm open to VBA solutions, although have little VBA experience myself.

1 Answer 1

0

You can use a conditional formatting formula for that. Select all rows from A2 to G100. Then add a new conditional format with this formula:

=LARGE($A2:$G2,1)-LARGE($A2:$G2,2)>0.2

Note the placement of the $ signs. It is important.

enter image description here

You could also store the comparison value in a cell, say H1 and use

=LARGE($A2:$G2,1)-LARGE($A2:$G2,2)>$H$1

Then you can quickly change the value in H1 to a different number and the highlights will adjust immediately.

1
  • Perfect. That LARGE function was the missing link for me. Also great idea to include the comparison cell for quick changes. Thanks!
    – jaminday
    Jun 30, 2015 at 6:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .