SharonR ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions


I am needing help with this formula

=IF({Colunm Range 1} >= 10, "Met", "To be Started")  (this works but will not be relevant for the whole year.)

How can I add in a date?  I was this formula to recognise if a range is equal to or great than 10 after the 01/03/19, "Met', "Risk"


Also can you AVG Harvey balls at all???? Im thinking of a helper column... 



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    To use a date in a formula, use


    DATE(yyyy, mm, dd)


    To AVG Harvey Balls, you will need to designate a number to each status level. There are a few ways to accomplish this. You mentioned a helper column in your post. That's the easiest way to do it actually.


    Use a nested IF statement to establish a number in place of the Harvey ball status then AVG your helper column.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    This article can shed some light on how to use Harvey Balls. You'll have to create a new symbols column and choose the Harvey ball option. Then you'll want to return words like "Half", Quarter, Etc. in your IF formulas to determine which ball is displayed. 

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Mike: Which Article??? winkcheeky


    What I was thinking was to use a single helper column and then use a nested IF along the lines of...


    =IF([Harvey Column Name]@row = "Full", 1, IF([Harvey Column Name]@row = "Three Quarters", .75, IF([Harvey Column Name]@row = "Half", .5, IF([Harvey Column Name]@row = "Quarter", .25, 0))))


    You can then average those numbers in the single helper column. To convert back to a Harvey Ball, I was thinking along the lines of basically the same nested IF, but reversed...


    =IF(AVG([Helper Column]:[Helper Column) = 1, "Full", IF(AVG([Helper Column]:[Helper Column) >= .75, "Three Quarters", IF(AVG([Helper Column]:[Helper Column) >= .5, "Half",  IF(AVG([Helper Column]:[Helper Column) >= .25, "Quarter", "Empty"))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!