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
-
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.
Answers
-
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.
-
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.
-
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.
-
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.
-
Hey, no problem. Glad you got it working!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!