# Formulas

Options
✭✭✭✭✭
edited 12/09/19

Hello,

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...

Tags:

## Comments

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

Mike: Which Article???

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"))))

• ✭✭✭✭✭✭
Options

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!