Exceeded Nested IF Suppressing #DIV/0!

I'm struggling with an alternative method of suppressing the #DIV/0 in my worksheet. I'm familiar with the ISERROR function and it's use as well as using =IF(A2=0,"",A1/A2), however I'm still getting #DIV/0 errors and I can't use ISERROR because I have exceeded the number of nested IF's.

I've attached an example. In the example, the only time the #DIV/0 appears is when "Y" appears in the Commit and In-House column for all rows. This is the condition I'm trying to suppress the #DIV/0 error for.

All the formula's I'm using are Array Formula, as are all my attempts to suppress the error.

Original Formula
=SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",C3:C5)/SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",B3:B5)))))))

(A2=0,"",A1/A2) Method
=IF(SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",C3:C5)/SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",B3:B5)))))))=0,"",SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",C3:C5)/SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",B3:B5))))))))

I looked at another possible solution that Dave posted that uses Custom Formatting on a per cell basis, but I can't seem to get that working either.

Quote from Dave

To hide zeros cell-by-cell use a Custom Number Format like 0.00;-0.00; where 0.00 is desired format for non zeros. Note the use of -0.00 for negatives.

Any alternatives or suggestions are greatly appreciated.

Re: Suppress #DIV/0! Alternatives

Quote from ByTheCringe2

What should be in cells D9 and E9? What are the formulas doing?

The formulas in D9 and E9 are accomplishing the same thing -- only in two different attempts by me - merely examples of my attempts by other means.

Here is what the formula does.

The first part performs a SUM of the data Column C (Calc) only when Commit = "Y" AND In-House and Funded are not equal to "Y" or blank. It then performs the same SUM on the data in Column B (Volume) with the same "Y" or blank criteria and divides the result by the SUM results in Column C.

Basically, it's Calc / Volume = Yield. My formula only sum's data that matches the criteria.

When there is nothing Committed or In-House the result should be ZERO, but I get the #DIV/0 error I want to suppress.

Re: Suppress #DIV/0! Alternatives

Quote from latarshanc

I had an answer all worked out then realized that it will only work on Office 2007 due to the nesting limitations in Office 2003. I will post the formula anyhow and perhaps you or someone else can figure out how to get around the nesting problem.

If you want it to show as a blank, I used a simple IF statement in front of the ISERROR, once the ISERROR results TRUE, then it results as a "BLANK", you can replace what is in quotes to be "0", or whatever value you want
=IF(ISERROR(SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",C3:C5)/SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",B3:B5)))))))),"0")
This formula gives a value of " ", A blank cell, this is the formula that is nested too many times.
If you want it to show as a true only, this just asks the formula if there is an error present in the cell TRUE =ISERROR(SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",C3:C5)/SUM(IF(D3:D5="Y",IF(F3:F5="",IF(E3:E5="",B3:B5))))))))
This formula gives a value of TRUE

LaTarsha Carr

Display More

I'm using Excel 2007. I know I can easily handle the nested IF limitation, however, this sheet has to work in Excel 2003 or older, darn you 7 nested IF limit!. Your second example gives me another course of action where I might be able to suppress the word TRUE with some Conditional Formatting. Incidentally, your second example appears to work in this case.

All I'm trying to do is suppress any TRUE, FALSE or #DIV/0 errors to the end-user.

SHG, I'll give your examples a go and report back her.

Re: Suppress #DIV/0! Alternatives

Quote from ByTheCringe2

Try:

=(SUMPRODUCT((B3:B5)*(D3:D5="Y")*(E3:E5<>"Y")*(F3:F5<>"y")))/MAX(1,SUMPRODUCT((C3:C5)*(D3:D5="Y")*(E3:E5<>"Y")*(F3:F5<>"y")))

This one partially works. It gives zero result, however when there is a deal(s) that are NOT in-house the Yield Result is not accurate - it's 709.47%. I'll dissect the formula and see why it's this result. Thanks for you help, TheCringe2 -- it gives me some more food for thought :)[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

Quote from EgoProwler

This one partially works. It gives zero result, however when there is a deal(s) that are NOT in-house the Yield Result is not accurate - it's 709.47%. I'll dissect the formula and see why it's this result. Thanks for you help, TheCringe2 -- it gives me some more food for thought

I got it fixed to give the correct result:

=(SUMPRODUCT((C3:C5)*(D3:D5="Y")*(E3:E5<>"Y")*(F3:F5<>"y")))/MAX(1,SUMPRODUCT((B3:B5)*(D3:D5="Y")*(E3:E5<>"Y")*(F3:F5<>"y")))

The Volume and Calc ranges were flip flopped. I'll plug this into my 'Production' work sheet and see if this fixes my issue. Thanks again, TheCringe2.

Re: Suppress #DIV/0! Alternatives

Quote from ByTheCringe2

If it's giving the wrong result, it is because you got your columns mixed up:

Those two statements are contradictory. To correct, swap the Bs and Cs in the formula.

You are correct, sir, it should have been the other way around Your solution DID work, however!

You introduced a new function to me, MAX, that I'll be doing some reading on now and put into my 'Excel Toolbelt'. Thanks a ton and appreciate everyone's time and efforts!

Re: Exceeded Nested IF Suppressing #DIV/0!

Dave,

I did think about a lookup, but I wasn't really sure if it was the most efficient solution. I will likely try this as an option in my next revision of the Production worksheet.

Thanks for another alternative