Formula syntax error

Alicia Delahunty

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?)

Kelsey Seitter

@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)))

Kelsey Seitter

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))

Alicia Delahunty

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.

Kelsey Seitter

@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)))

Alicia Delahunty

Thank you! That works!