Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Find the Max and Min value in a column
Hi,
I have a sheet with a single column that has a large range of numbers. I would like to use conditional formatting to highlight the highest number and the lowest number within this column so its easy to see when viewing the sheet.
I can use the functions Max() and Min() to find the highest and lowest values, but these results are in separate cells to the main column.
How do I then use these two results to conditional highlight the corresponding cells / rows?
Regards
Comments
-
Hi Scott,
Let's assume that your helper column is called "Helper" and your main column is called "Main".
Let's also assume that your Helper column contains a formula like =IF(MAX(Main:Main) = Main<n>, "Max", IF(MIN(Main:Main) = Main<n>, "Min", "")) in every row (where <n> is the row number). This will then show the text "Max" next to all values that are equal to the largest (there may be more than one) and likewise "Min" for those that are equal to the smallest.
Given the above, then the conditional format that you need would look like:
If Helper is "Max" the apply <format for the max colour> to the Main column
If Helper is "Min" the apply <format for the min colour> to the Main column
(and you fill in the bits which I've bolded above).
The conditional format is clever in that it allows you to test one column and format a different column.
Cheers,
Rob.
-
Hi Rob,
Thank you very much indeed, that tip has worked perfectly.
Smartsheet need to add Max Value and Min Value within their conditional formatting custom criteria list.
Thank you again for the help.
Scott
-
Ditto what @Scott Lynch wrote- Smartsheet needs to add this. We shouldn't need to waste time or sheet space with an additional column. Missing basic features we have in Excel is fristrating.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives