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
Aileen@RoyalCanin
edited 12/09/19 in Archived 2017 Posts

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

Smartsheet Divide by Zero error.png

Comments

  • SmSulli
    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

     

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    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).

    That formula used "Actual" instead of "Actuals" from your example.

     

    Hope this helps

    Craig

     

     

  • Aileen@RoyalCanin
    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

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    You are welcome.



    Craig

This discussion has been closed.