If / then statements with subtraction problem
Hello everyone,
I was hoping some of the pro's on here could help me figure something out. I'm creating a hours tracker for employees to use remotely and I need to calculate the overtime hours as anything over 8 or anything over 10 depending on the answer given in a column.
So if [certified] = "Yes" then subtract 8 from [total hours] = OT Hours
if [Certified] = "No" then subtract 10 from [total hours] = OT Hours
It should look like this once done
I was trying to do something like
if ([Certified]@row = "Yes") -8 Value([Total Hours]@Row)
if ([Certified]@Row = "No") -10 Value([Total Hours]@Row)
I'm sure there could be more logical ways of doing this however I'm pretty new and am trying to keep this as simple as possible while I learn. I appreciate any and all feedback.
Thank you!
Best Answer
-
I made a few tweaks - lower case r in Row, removed [ ] around certified since it shouldn't be required. Try this:
=IF(AND(Certified@row = "No", VALUE([Total Hours]@row)>10), VALUE([Total Hours]@row)-10, IF(AND(Certified@row = "Yes", VALUE([Total Hours]@row)>8), VALUE([Total Hours]@row)-8, " "))
Everything looks right based on the screen shot you included.
Any luck?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Hi James,
Try:
=IF(AND([Certified]@row = "Yes", Value([Total Hours]@Row)>8), VALUE([Total Hours]@Row)-8, IF(AND([Certified]@Row = "No", VALUE([Total Hours]@Row)>10, VALUE([Total Hours]@Row)-10, ""))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hey Mark,
Thanks so much for your help with this. It would've taken me forever to figure out. I'm still getting an unparseable error. I'm going to try walking through the formula you provided after my upcoming meeting to see if I can find what is causing it, any additional input would be great though. Thanks again. -James
-
Excellent. You're missing a paren after the 2nd >10. You need a paren to close the AND function.
Thanks for using the community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
I'm sorry I can't get past this unparseable error. I added the second paren ) behind the 2nd > 10 so the formula now looks like...
=IF(AND([Certified]@row = "Yes", Value([Total Hours]@Row)>8), VALUE([Total Hours]@Row)-8, IF(AND([Certified]@Row = "No", VALUE([Total Hours]@Row)>10), VALUE([Total Hours]@Row)-10, ""))
When I was clicking through the formula it wasn't highlighting the cells referenced so I'm going to try working through this slow using the above formula as reference to try to figure it out. If anyone has any more insight as to where I might be going wrong it would be greatly appreciated. Also Thank you Mark for being so helpful and for welcoming me to this community. :)
-
I made a few tweaks - lower case r in Row, removed [ ] around certified since it shouldn't be required. Try this:
=IF(AND(Certified@row = "No", VALUE([Total Hours]@row)>10), VALUE([Total Hours]@row)-10, IF(AND(Certified@row = "Yes", VALUE([Total Hours]@row)>8), VALUE([Total Hours]@row)-8, " "))
Everything looks right based on the screen shot you included.
Any luck?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hey Mark,
Thank you so much for your help with this. Looks like that formula did it. I'll have to pay attention to capitalization a bit more. Also why aren't [] required around certified? It's not important just trying to learn as much as I can.
-
Hi James,
Glad you found a solution. Square brackets [ ] are required when the column name has a space or special character in it. Tip: use one word column names to make formula builds easier.
Thank you for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!