0

I have a table in a sheet in Excel with over 2000 rows and about 500 columns of data (numbers). Values in a particular column header are being compared to each row underneath that header, and I am using a VBA function to calculate these values (in other words, I am comparing the value in B1 to the values in A2 through A2146, the value in C1 to the same range, etc). I want to highlight the lowest number in each row by filling the cell. I have researched this issue and tried to solve the problem by implementing this solution, but by using MIN instead of MAX:

=A1=MIN($A1:$TX1)

Columns A through TX contain data.

However, when I use this function as a formatting rule, Excel highlights the wrong values and sometimes multiple values in a row. For example, I have a row with a cell containing the value 10.2, which is the only cell highlighted in this row, even though it is neither the MIN nor MAX of the specified row. Additionally, I have another row which contains values 10.6, 8.4, 5.2, and 20.8. Each one of these values is highlighted based on the formatting rule above, though 5.2 is the obvious MIN; the other three cells with the aforementioned values, then, should not be formatted.

How can I remedy this? I have tried adjusting relative/absolute cell references to determine if this could be the problem, but I am quite sure that the way I'm referencing the cells is correct.

Matheus Lacerda
  • 5,983
  • 11
  • 29
  • 45
larryltj
  • 15
  • 7
  • 3
    Check that the "Applies to" range is correct. For the formula you quote (based on A1) the "applies to" range should also start at A1 – barry houdini Oct 24 '17 at 17:20
  • 2
    You might have 'text-that-looks-like-numbers'. –  Oct 24 '17 at 17:21
  • According to *'Values in a particular column header are being compared to each row underneath that header...'* you have the formula backwards. –  Oct 24 '17 at 17:25
  • @barryhoudini It makes sense that the "Applies to" range could be the source of the problem, but can you elaborate on the range I should be specifying? What I had at first is the rule above with the "Applies to" range saying "=$B:$TX". And perhaps I can be clearer on what exactly I'm doing: there are column headers in cells B1 through TX1, and there are row headers in cells A2 through A2146. The data exists in cells B2 through TX2146 (in a table). – larryltj Oct 24 '17 at 17:42
  • If you have "Applies to" range $B$TX with the formula above then that will give you incorrect results. I usually select the whole range first (and the applies to range is the same as that) and then apply the formula. You can change the range now but that will probably make the formula change too! – barry houdini Oct 24 '17 at 17:46
  • Sorry, based on your last comment - Select the whole range of numbers `B2:TX2146` (you can do that by typing that range in the box above A1 then press RETURN). Now set CF with the formula that applies to top left cell, i.e. `=B2=MIN($B2:$TX2)` – barry houdini Oct 24 '17 at 17:53
  • @barryhoudini This solved my problem. Thank you! – larryltj Oct 24 '17 at 18:01

1 Answers1

0

Courtesy @barry houdini (copied from a Comment):

Select the whole range of numbers B2:TX2146 (you can do that by typing that range in the box above A1 then press RETURN). Now set CF with the formula that applies to top left cell, i.e. =B2=MIN($B2:$TX2)

pnuts
  • 58,317
  • 11
  • 87
  • 139