#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# IFERROR -- Suppress Error Help Needed

Options
edited 12/09/19

Hello!

I hope you're having a terrific day!  I am looking for suggestions for using the IFERROR formula to suppress and error message.  Here's the set-up:

Columns:

Planned Allocation = Out-of-Box Allocation column

Planned Effort by Day = [Allocation] * 8

Duration = Out-of-Box Duration column

Sum Planned Effort (Estimate) = [Planned Effort by Day]*[Duration]

Sum Actual Effort (Actuals) = Number

Hours Burn = [Sum Actual Effort (Actuals)] / [Sum Planned Effort (Estimate)]

Results:

When either Sum Planned Effort (Estimate) or Sum Actual Effort (Actuals) are blank or zero, the following error message is returned:

#DIVIDE BY ZERO

Desired Outcome

When the formula returns an error message, display "0"

I've tried using the IFERROR function, but can't figure out how to nest it correctly.

I appreciate any suggestions you might have!

Thanks,

Aileen

• Options

Hi

Give this formula a try for "Hours Burn" column.  If there is an error it will put a 0, else Actual/Estimate.

=IF(ISERROR([Sum Actual Effort (Actual)]1 / [Sum Planned Effort (Estimate)]1), 0, [Sum Actual Effort (Actual)]1 / [Sum Planned Effort (Estimate)]1)

Shawn

• ✭✭✭✭✭✭
edited 05/27/17
Options

Aileen,

Here is a shorter version:

=IFERROR([Sum Actual Effort (Actuals)]23 / [Sum Planned Effort (Estimate)]23, 0)

for row 23.

Note: If you copied SmSulli's formula directly, you likely got an error (#UNPARSEABLE).

Hope this helps

Craig

• Options

Thank you, J. Craig Williams and SmSulli's!  I'm sorry for my tardy reply.  I've meant to log in for weeks and thank you.

THANK YOU!

Craig, the solution was perfect.  Just what I needed!  Thank you so much!

-Aileen

• ✭✭✭✭✭✭
Options

You are welcome.

Craig

This discussion has been closed.