Highlight max values

Kris W

I'm trying to highlight the highest value as well as the top 4 values in a column. 

Think of it as I need to identify the top 4 leaders in a contest

KPH

Try the LARGE function

https://help.smartsheet.com/function/large

To create a list of the top 4, create 4 LARGE functions, select your columns as your range and 1, 2,3 or 4 as the n for the 1st, 2nd, 3rd, 4th largest.

If you don't want a list but want something you can use for conditional formatting, you can combine LARGE with IF. This would return 1 if the value is col1 in the current row is the largest:

=IF([col1]@row = LARGE([col1]:[col4], 1), 1)

Kris W

Trying to highlight this data into the top 4 Total of Red & Black.

KPH

Sorry, I'm still unsure what you mean by "highlight this data". What should the end result look like?

If you want to create a table like the one you posted, showing the details of the top 4, from a larger data sheet, you could create a list of the top 4 scores using the LARGE function. To add other columns you could use INDEX and MATCH against the score (assuming there are no duplicate scores).

or

If the table you posted is your input data, you could add a column to that data and use an IF and LARGE function to check a box, or return a number, if the Total was in the top 4. Or use a RANKEQ function to return the rank of the score.

You could then use conditional formatting to highlight rows based on the value in the column you added.

This formula will add the rank to the original data:

=RANKEQ([Total of Red & Black]@row, [Total of Red & Black]:[Total of Red & Black])

And this will set up the conditional formatting:

Is that what you need?

Kris W

Perfect!

KPH

Excellent!