Automate Harvey ball color based on multiple variables

I'm not sure if what I want to do is feasible or not so please let me know if I'm going off the deep end. I would like to have a Harvey ball change color based on several criteria/variables to show likelihood the task will, or won't be, completed on time.

Has anyone made a formula that will show a red Harvey ball if the task is through, lets say, 75% of the duration and % complete is less than %50 (or something along those lines)? Then criteria for yellow and green?

Is it possible to include a variable of whether or not the task was started on time?

If anyone has already done something along these lines I would love to see what you have rather than reinvent the wheel.

Find more posts tagged with

Formulas

Project Management

Comments

Mike Wilday

Hi there, you can use IF statements intermingled with AND statements to make the formula you are looking for. But I don't have time to figure the duration percentage... you'll need to develop that...

=IF(AND([duration % column]1 < .75, [% complete]1 < .5), "Red", IF(And...

build it out that way. But you have to have either a formula or column calculating your duration to check the % of time.

Eric R

Thanks for pointing me in the right direction!

Mike Wilday

Sure thing! Let me know if you encounter any issues or if you have any questions.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…