Formula
Hello! New to Smartsheet and attempting to do something I think is easy, but I can't noodle through. Here is the problem statement:
Two columns: Allocation and Hrs/week. I need the Hrs/week to automatically adjust when Allocation is adjusted up/down. For example, 40 hour week and the allocation is set to 1. When allocation is adjusted to .5 it will automatically halve the hours to 20 hours.
Thank you!
Voo
Best Answer
-
2 things on this one:
Your text values (Offshore, USA, Nearshore) need to be in quotation marks to be found properly. For numbers it's fine to leave them without (in fact putting them in sometimes causes problems!)
You're also missing the final IF statement in your formula.
Your formula should be along the lines of this:
=IF(Location@row = "USA", 30, IF(Location@row = "Nearshore", 40, IF(Location@row = "Offshore", 43.75)))
Result:
As these are exact matches (=), the actual order of the IF statements doesn't matter, but if you're using number/date ranges it's a good idea to have them in increasing or decreasing order in order to make the nested IFs easier to write.
Hope this resolves your issue! 😊
Answers
-
Hi @Voo,
The formula you would want in the Hrs/Week column is:
=IF(Allocation@row = 1, 40, IF(Allocation@row = 0.5, 20))
Example results:
Hope this helps; if you've any questions then just ask! 😊
-
Just awesome thanks and easy enough! So, let me complicate it a bit. What if the allocation was more fluid meaning someone could work 33% or 71%. Is there a formula to account for more variability?
-
You can have the nested IF formula either have exact values (like above) or have ranges (i.e. between A% and B% allocation is C hours). How the formula would look would depend on what ranges you would want.
-
What your help with earlier worked, but the request morphed into something a bit more complicated. I attempted to adjust the formula, but I'm getting an 'Unparseable' message. Here is the problem statement:
Three columns: Location (containing USA, Nearshore, Offshore), Allocation (multivariable), Hrs/week. Here is the condition I'm trying to accommodate: When USA in Location = 30, Nearshore 40, Offshore 43.75, but adjust values when Allocation is modified by 25%, 50%, 75%, 100%.
I started with this an threw my hands up when I go the 'Unparseable' message.
=IF(Location@row = Offshore, 43.75, IF(Location@row = USA, 30, (Location@row = Nearshore, 40)))
Hope you follow.
-
2 things on this one:
Your text values (Offshore, USA, Nearshore) need to be in quotation marks to be found properly. For numbers it's fine to leave them without (in fact putting them in sometimes causes problems!)
You're also missing the final IF statement in your formula.
Your formula should be along the lines of this:
=IF(Location@row = "USA", 30, IF(Location@row = "Nearshore", 40, IF(Location@row = "Offshore", 43.75)))
Result:
As these are exact matches (=), the actual order of the IF statements doesn't matter, but if you're using number/date ranges it's a good idea to have them in increasing or decreasing order in order to make the nested IFs easier to write.
Hope this resolves your issue! 😊
-
I can't thank you enough...it is like you unlocked my brain to get it. Everything is working perfectly!
-
No problem, happy to have helped!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!