Formula

Voo

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

Nick Korna

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! 😊

Nick Korna

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! 😊

Voo

@Nick Korna

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?

Nick Korna

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.

Voo

@Nick Korna

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.

Nick Korna

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! 😊

Voo

@Nick Korna

I can't thank you enough...it is like you unlocked my brain to get it. Everything is working perfectly!

Nick Korna

No problem, happy to have helped!