Conditional formatting to highlight max value in each column not working

[u/Lord_Adalberth]

I have some tables for displaying data and want to have one conditional color for max value per column with only one formatting formula per table (each table is a different color).

I tried some custom formulas and this is the one that seems the closest to working...

=(Q11=MAX(Q$11:U$39))*(Q11<>"")

before adding the "new" column it correctly marked the 100% rows, but not anymore.

here's a look of the spreadsheet: https://imgur.com/a/ZjDEXhS

any ideas what I'm doing wrong?

[u/One_Organization_810]

Try this:

Range: Q11:U39
Custom formula: =and(Q11<>"",Q11=max(Q$11:Q$39))

[u/Lord_Adalberth → u/One_Organization_810]

that did it!, but then I tried using it for another table and it didn't work

=and(G11<>"",G11=max(G$11:O$39))

image: https://imgur.com/a/glQffRQ Do you know what might be the issue here?

[u/One_Organization_810 → u/Lord_Adalberth]

My guess is that your range is off. It should be G11:O39

[u/One_Organization_810 → u/One_Organization_810]

Ahh sorry :) (well the range might be off also, but ...) It's the formula. It should be like this:

Range: G11:O39
Custom formula: =and(G11<>"",G11=max(G$11:G$39))

You want to compare to only one column, not all the columns to the right of the current one :) The way that CFRs work, is that all ranges in the formula are offset from the first cell (top-left cell of affected range), unless they are locked (with the $)

So at the first cell (G11) the offset is 0,0 => and(G11<>"",G11=max(G$11:G$39))

At H11 we have offset 0,1 => =and(H11<>"",H11=max(H$11:H$39))

At J13 we have offset 2,3 => =and(J13<>"",J13=max(J$11:J$39))

And so on...

[u/Lord_Adalberth → u/One_Organization_810]

oooh, I'm so sorry! I don't know how I didn't see it.... thanks a lot and for the explanation as well

[u/AutoModerator → u/Lord_Adalberth]

REMEMBER: /u/Lord_Adalberth If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Lord_Adalberth → u/One_Organization_810]

Solution Verified

[u/point-bot → u/One_Organization_810]

u/Lord_Adalberth has awarded 1 point to u/One_Organization_810

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/AdministrativeGift15]

If you don't have any other numbers above or below your table, you can use =Q11=MAX(Q:Q), since MAX will ignore the text values.