Formula syntax error
The current formula I have that works for calculating Labor hours is below
=IF([Actual Duration in Hours]@row = 0, ([# of Staff Needed]@row * ([Baseline Duration in Hours]@row * 18)), ([# of Staff Needed]@row * [Actual Duration in Hours]@row))
The issue with this formula is the labor hours are always calculated even if the task is not performed which causes errors in my total labor hour reports for a sheet.
I would like to improve this in my sheet by first including IF([Status]@row=Skipped,0, followed by the above formula.
=IF([Status]@row=Skipped, 0, ([Actual Duration in Hours]@row = 0, ([# of Staff Needed]@row * ([Baseline Duration in Hours]@row * 18)), ([# of Staff Needed]@row * [Actual Duration in Hours]@row))
This just gives me an error
The goal is if status is skipped then the formula will= 0, but if the status is anything else then calculate the labor hours based on expression IF([# of Staff Needed]@row * ([Baseline Duration in Hours]@row * 18)), ([# of Staff Needed]@row * [Actual Duration in Hours]@row)) (IF/Then this, but if not then this Formula if that is a thing?)
Best Answer

@Alicia Delahunty  looks like the issue is around bracket placement  much easier to see from your sheet screenshot, so that is helpful.
=IF([Status]@row="Skipped", 0, IF([Actual Duration in Hours]@row = 0, ([# of Staff Needed]@row * ([Baseline Duration in Hours]@row * 18)), ([# of Staff Needed]@row * [Actual Duration in Hours]@row)))
Answers

Hi Alicia,
What error does your formula produce? This is helpful in diagnosing what the issue might be.
Looking at your formula, adding a few changes to the syntax should work:
=IF([Status]@row="Skipped", 0, IF([Actual Duration in Hours]@row = 0, ([# of Staff Needed]@row * ([Baseline Duration in Hours]@row * 18), ([# of Staff Needed]@row * [Actual Duration in Hours]@row))

I input the formula and still producing the following errors (Much closer than what I was doing). The Status is drop down select and restricted to following inputs: Not started, In progress, Completed, Delayed and Skipped.

@Alicia Delahunty  looks like the issue is around bracket placement  much easier to see from your sheet screenshot, so that is helpful.
=IF([Status]@row="Skipped", 0, IF([Actual Duration in Hours]@row = 0, ([# of Staff Needed]@row * ([Baseline Duration in Hours]@row * 18)), ([# of Staff Needed]@row * [Actual Duration in Hours]@row)))

Thank you! That works!
Help Article Resources
Categories
Check out the Formula Handbook template!