Ask Your Question
0

How to highlight max values in each row of a table? [closed]

asked 2013-11-14 13:07:40 +0200

Sitalatma gravatar image

I can apply conditional formating to a row and get what I want but I can't do it manually for a large table with 50+ rows.

If I use Paste Special and select only Formats I get several cells in the same row highlighted at once, seemingly at random.

If I select the whole area for conditional formatting, how can I modify the formula to calculate values only from each cell's row? Let's say I start with this in conditional formatting dialogue: MAX(A1:E1), how to change it to something like MAX(A'value of this row':E'value of this row') so that for cell F3, for example, conditional formatting calculates MAX(A3:E3)?

=RIGHT(ADDRESS(ROW(),COLUMN(),4,1),LEN(ROW())) gives me the row number, but how to incorporate it into formula for MAX?

Or maybe there's another solution,

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-14 14:46:10.795177

2 Answers

Sort by » oldest newest most voted
0

answered 2013-11-14 13:38:03 +0200

Sitalatma gravatar image

I got it:

MAX(INDIRECT("A"&RIGHT(ADDRESS(ROW(),COLUMN(),4,1),LEN(ROW()))):INDIRECT("E"&RIGHT(ADDRESS(ROW(),COLUMN(),4,1),LEN(ROW()))))

Change "A" and "E" to suit your particular table, and if looking for max values in columns use

MAX(INDIRECT(LEFT(ADDRESS(ROW(),COLUMN(),4,1),LEN(ADDRESS(ROW(),COLUMN(),4,1))-LEN(ROW()))&"1"):INDIRECT(LEFT(ADDRESS(ROW(),COLUMN(),4,1),LEN(ADDRESS(ROW(),COLUMN(),4,1))-LEN(ROW()))&"17"))

Here substitute "1" and "17" for row numbers.

This goes into conditioned formating formula which then applies cell style to change font color or background.

MAX can, of course, be changed to MIN, too.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-11-14 13:07:40 +0200

Seen: 5,440 times

Last updated: Nov 14 '13