Formula to display Red Flags

cramsey
cramsey
edited 12/09/19 in Formulas and Functions

I am attempting to have a RED Flag populate in the "Risk" Column for cells where the "Available Space" is less than 10. 

Kindly when answering, please complete the entire formula from start to end as from research I tend to get bits and pieces as seen in my screenshot. 

Thanks, 

Flag disk space.JPG

Tags:

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Hi there, 

    1st, you have to make sure the column you are placing the formula in is a symbol column set to a flag,

    Then, you can use the following formula. 

    =IF([Available Space (GB)]@row > 10, 1, 0)

    I use @ row because it will work for whatever row the formula is in. Flags in smartsheet are calculated on the back end with a 1, or 0 for no flag. This IF statement is saying if the available space is greater than 10 then flag the row. 

  • cramsey
    cramsey

    Great and quick response!!

     

    So, I'm guessing this same formula cannot be used on the other listed Symbols ei; RYG Balls. If so, why can't I get either to populate in a Dashboard Chart?

     

    Thanks, 

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭

    Mike's formula flag when there is greater than 10Gb. Don't you want to know when it has LESS than 10?

    Also, since you are already capturing the percentage of disk space used, doesn't it make more sense to flag on BOTH? 

    10Gb out of 1Tb is miniscule and if you are filling up 1Tb, you are probably doing it in large chunks and 10Gb may be gone quickly. On the other hand a 40Gb drive, the 10Gb limit may be more accurate.

    I would suggest a limit for each and then use the OR() statement to determine if either limit is exceeded.

    Craig

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    No, the different symbol column types register differently in the backend. To automate for RYG you would use the keywords "Red", "Yellow", and "Green".

    here are some great reads on automating symbol columns

    https://community.smartsheet.com/announcement/working-symbol-formulas

    https://www.smartsheet.com/blog/support-tip-automate-RYG-balls

    And this article outlines the key words for several symbols: https://www.smartsheet.com/blog/support-tip-column-symbols

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Whoops. Thanks for catching that Craig. Yep. I put in > rather than < 

    Which makes me go  > < 

                                       ----

    To include an OR in your formula you can add, 

    =IF(OR([Available Space (GB)]@row < 10, [Available Space (GB)]@row > 100), 1, 0)

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭

    No prob. I'm actively procrastinating until 5pm.

    When I said OR, I did not mean this:

    =IF(OR([Available Space (GB)]@row < 10, [Available Space (GB)]@row > 100), 1, 0)

    I meant something like

    =IF(OR([Available Space (GB)]@row < 10, [PERCENT DISK USED (%)]@row > 90), 1, 0)

    Craig

     

  • cramsey
    cramsey
    edited 08/29/18

    Thank you for your attention and support!! You are a true valuable asset to the Smartsheet community. laugh

  • cramsey
    cramsey

    Thanks Mike. 

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Sure thing! Glad I could help out. 

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories