Formula for showing both dashes and zeros for #DIV=0! errors

[daved104]

Hello. I need some guidance on trying to fix a problem in a formula that I've created.

Basically, I have an IFERROR formula that puts a dash in the cell if a dividing by zero error occurs, however I need an extra piece of logic to show a zero instead of dash if a cell that isn't part of the formula is greater than zero. I've tried some IF formulas beside the IFERROR formula, but I can't get them to do what I want, so I clearly need something else. Any suggestions of what that should be would be great. I'm not an Excel master, so I probably won't understand any explanations that are too complicated. Thanks!

[Sam Capricci]

upload a sample workbook (per yellow banner at the top of the post) with what you have AND what you want. It is difficult to debug a formula that we can't see.

[Gregb11]

Maybe this? (assumes cell A1 is the cell that if >0, show a zero)

=if(A1>0,0,iferror(your formula,"-")

If this isn't it, like Sam has stated, please attach a sample workbook.

[daved104]

I tried the formula above and it just overrides the IFERROR formula with a zero. The spreadsheet is pretty big - I'll make a smaller version and upload it later unless there's another suggestion that works before I get to it.

[Gregb11]

It would only override the IFERROR with a 0 if A1>0, which is what you wanted. Yes, please attach a file.

[MrShorty]

What does your overall logic look like?

If I assume that your logic is A) Perform calculation -> B) If the calculation errors then C) If this other cell is greater than 0 return 0, else return dash. Is that the correct logic? I can represent that logic by:

=IFERROR(calculation,IF(other_cell>0,0,"-"))

[daved104]

What does your overall logic look like?

If I assume that your logic is A) Perform calculation -> B) If the calculation errors then C) If this other cell is greater than 0 return 0, else return dash. Is that the correct logic? I can represent that logic by:

=IFERROR(calculation,IF(other_cell>0,0,"-"))

This one is the winner - it shows the dash if there is a zero in the requested cell and the number if needed. Thanks!

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.