Formula to display Red Flags

08/28/18 Edited 12/09/19

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

Comments

  • Mike WildayMike 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. 

  • 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 WilliamsJ. Craig Williams Top Contributor

    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 WildayMike 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 WildayMike 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 WilliamsJ. Craig Williams Top Contributor

    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

     

  • edited 08/29/18

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

  • Thanks Mike. 

  • Mike WildayMike Wilday ✭✭✭✭✭

    Sure thing! Glad I could help out. 

Sign In or Register to comment.