Formula syntax error

Options

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

  • Kelsey Seitter
    Kelsey Seitter ✭✭✭
    Answer ✓
    Options

    @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

  • Kelsey Seitter
    Options

    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
    Options

    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
    Kelsey Seitter ✭✭✭
    Answer ✓
    Options

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

    Thank you! That works!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!