Is there a way to define "today's" health, based on today's date and corresponding "% completion" va

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

Hey everyone,

I've got a set of data pulling in completion % by date, and want to give my team a quick snapshot of overall health of the project based on completion % for TODAY(). I have used the following formula, which works for giving RYG per day:

=IF(AND(Date9 = TODAY(), Difference9 < 0.05), "Green", IF(AND(Date9 = TODAY(), Difference9 > 0.15), "Red", "Yellow"))

  • Date9 in the above is where I have defined each given date. In this example, Date9 = 07/06/18 (Date8 = 07/07/18, etc).

This gives me RYG for every day..but what I am actually trying to do is say something like: "Based on whatever today's date is, find the completion % TODAY, and tell me if we are at a Red, Yellow or Green health status (based on the <.05, >.15, etc criteria I defined above). My hope is to pull this value into a Smartsheet dashboard so that it only pulls in one RYG ball for today's date (again, I've gotten it to work in my sheet to pull in health for every single date I've defined..but I just want data for whatever date happens to be today, if that makes sense).

I've also messed around with adding a check column to say "tell me which date in my Date column = today..I was thinking maybe this could be a flag, but I haven't figured out how to make it work with that, and also am not sure if it's totally necessary.

Any help that can be provided here would be much appreciated.

 

NOTE: I've given a screenshot below to help you see what I'm trying to do ("Difference" = % difference between "total points left to complete" vs. "left to complete(Straight avg)")

Screen Shot 2018-07-06 at 11.40.53 AM.png

Tags:
· Share on FacebookShare on Twitter

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 07/06/18

    Try this out...

    =VLOOKUP(TODAY(), Date:Difference, 6)

     

    Her's what it does: First it will search the leftmost column your table (all columns from Date to Difference) for today's date. It will then return the value from the 6th column (Difference) of that row.

     

    You can also use this in your IF statement if you should so choose to use RYG balls or flags or whatever else you may need it for.

     

    =IF(VLOOKUP(TODAY(), Date:Difference, 6) = "Criteria", "Then input this data", "If not then input something else")

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Jon_Beattie
    Jon_Beattie ✭

    Hi Paul, thanks for the help! When I try either of those statements it gives me a "# DIVIDE BY ZERO" message. Do you have any idea why it might be giving that error? From what I can tell, what you've given should be correct but I also haven't done much with VLOOKUP in Smartsheet yet.

     

    Thanks again!

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Hmm... I have retested both, and they worked just fine for me. The only errors I was able to generate were # DATE EXPECTED when I put the formula in a date type column and # NO MATCH when I changed the dates to something other than today. What is the exact formula you are using it and where exactly are you putting it?

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Jon_Beattie
    Jon_Beattie ✭

    Hi Paul, this is very strange. This is the equation I am using and it is still giving me a #DIVIDE BY ZERO error:

    =IF(VLOOKUP(TODAY(), Date:Difference, 6) > 0.15, "Red", "Yellow")

    I'm adding a screenshot here to try to show what it should be doing. As you can see, TODAY = 07/18/18 and the "Difference" value = .18203. This should give me a "Red" value, but it is giving me the #DIVIDE BY ZERO error and I'm not totally sure why.

     

    Thanks again for the help!

    Screen Shot 2018-07-18 at 8.24.38 AM.png

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are there any formulas in your Difference Column? What column type is the Difference Column?

     

    I tested it again today, and cannot get the divide by zero error.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Jon_Beattie
    Jon_Beattie ✭
    edited 07/25/18

    Hi Paul,

     

    Yes, I do have a calculation in the "Difference" column & it is a Text/Number column type. I found that there was one row in that column that had a #DIVIDE BY ZERO error so I cleared that. Now, my full RYG IF statement is as follows:

     

    =IF(VLOOKUP(TODAY(), Date:Difference, 6) > 0.15, "Red", IF(VLOOKUP(TODAY(), Date:Difference, 6) < 0.05, "Green", "Yellow"))

     

    I just did a manual check and no matter what the "difference" value is the RYG ball is still showing as Red. Here is the calculation I have in the Difference Column for today's "difference" calculation:

    =([Total Points Left to Complete (Actual)]11 - [Left to Complete (Straight Avg)]11) / [Left to Complete (Straight Avg)]11

     

    The '11' in the above refers to the row of data. The calculation in this case should give a Red RYG ball, but if I hand key in .04 to that row, the RYG ball is still showing as red (based on my IF statement above, it should be showing as green). I'm guessing it's still a user error on my end or there's a small tweak to make to my calculations/settings so any help you can provide would be much appreciated. I'm also happy to share my sheet with you if that is the most helpful.

     

    Thanks!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!