Special IF Formula for Stars

edited 12/09/19 in Formulas and Functions

I have two columns:

  • [Infrastructure Project] is a checkbox
  • [Product Priority] is stars (0 - 5)

If [Infrastructure Project]@row is checked, I would like [Product Priority]@row to be 5 stars. If it's not checked, I would like manual entry for the stars.

This would be simple if [Product Priority] were empty, but I already have it filled out. Is there a way to make the "if not" portion of the formula be "do nothing" (i.e. leave the current star rating)? Please let me know.


  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    I think you'd have to do it in 2 steps because once you copy the formula down, it would overwrite your existing stars unless someone knows a trick. 

    You could try this if this will work for you. Add an adjacent column, you could use a formula like this:

    =IF([Infrastructure Project]@row = 1, "Five", IF([Product Priority]@row > "empty", [Product Priority]@row, ""))

    It'll first run a check to see if your one column is checked and input 5 stars. If its not checked, but greater than "empty", it'll fill in current result, if not, it'll leave it blank. 

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    One caveat to this process might be if your users are using a report to set the number of stars manually. Columns that contain a formula are not editable in reports. 

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!