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

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.

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.

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"))))
Help Article Resources
Categories
Check out the Formula Handbook template!