Formulas

SharonR

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

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

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

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

Mike Wilday

https://www.smartsheet.com/blog/support-tip-column-symbols This one!!!