IFERROR -- Suppress Error Help Needed

Aileen@RoyalCanin

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

SmSulli

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

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

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

You are welcome.



Craig