If Formula

I have a sheet that has a score (0-5) for an inspection performed. If the score is <=3, the re-inspection date needs to be 2 weeks from the original date of inspection. If the score is <= 4, re-inspect date is 30 days. If the score is <= 5, re-inspect date is 120 days. I an attempting to use multiple if statements but not having success.

=IF([Score@row],<=3,[Inspection Date]+14)

This is the format I am using for the first condition. Please let me know how to create this formula without receiving an error message.

Best Answer

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    I think there is a logic issue in your statement, as <=4 would be inclusive of the number 3. I think you can just say = 4 and = 5.

    Also, I think you are looking for a nested if statement. From what I can tell, your formula for <=3 and =4 would be something like:

    =IF(Score@row <= 3,[Inspection Date]@row+14, IF(Score@row = 4, [Inspection Date]@row + 30))

  • This helps a lot. Thank you. You definitely cleared up some questions, but I am still having concerns.

    The average score is a decimal number between 0 and 5 so I need to incorporate all values in the following ranges: 0-3, through 4, and through 5.

    The other issue I am having is that I continue to receive an Invalid Data Type error. The Score is set as a text/Number and Date of inspection and re-inspection are both date fields.


    Any help would be much appreciated.

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    Here is the exact formula I have set on a test sheet where I tried to match your columns as close as I could. I also modified the formula for decimal values. I set this to a column formula and it appears to have entered dates for all fields. If you still get an error after inserting this, can you share a screenshot?

    =IF(Score@row <= 3, [Inspection Date]@row + 14, IF(Score@row <= 4, [Inspection Date]@row + 30, IF(Score@row <= 5, [Inspection Date]@row + 120)))


  • I have the "Next Inspection" column set up as a date field. I see that you have it set up differently and I am wondering if that is the cause of my problems. I continue to receive "UNPARSEABLE" error.


  • David Tutwiler
    David Tutwiler Overachievers Alumni

    It's set up as a date, but once I got the formula working I right-clicked it and set it up as a column formula so it does the same calculation for every row. That is why it looks different.

    I think I see your problem. In your last two calculations you have an extra @row in your formula. That will not parse through.


  • Jennifer Parins
    Jennifer Parins ✭✭✭
    Answer ✓

    You are awesome and amazing. I was looking at that formula so much that I didn't see such an easy error I was making.


    Works PERFECTLY now. A million Thank Yous.

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    Hey, no problem. Glad you got it working!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!