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

Options
Scott Lynch
Scott Lynch ✭✭
edited 12/09/19 in Archived 2017 Posts

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

  • Rob Hagan
    Rob Hagan ✭✭✭
    Options

    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.

  • Scott Lynch
    Scott Lynch ✭✭
    edited 02/06/17
    Options

    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

     

  • Kenyon Bajus
    Kenyon Bajus ✭✭✭✭✭
    edited 02/12/17
    Options

    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.

This discussion has been closed.