Solved

Division by 0 error check

Posted on 2012-03-29
7
241 Views
Last Modified: 2012-04-17
Hi guys whats the syntax trap Div/0 errors

=SUM(($B$34)/($B$33)) <===== else 0


Thanks
0
Comment
Question by:kingjely
7 Comments
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 100 total points
ID: 37785685
Look at this link. IfError
0
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 100 total points
ID: 37785689
0
 
LVL 8

Author Comment

by:kingjely
ID: 37785692
=IFERROR(SUM(($B$34)/($B$33)),"0")
=IFERROR(SUM(($B$34)/($B$33)),"END OF MONTH")
=IFERROR(SUM(($B$34)/($B$33)),0)


These all get the error #NAME?

Why?
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 50 total points
ID: 37785757
It looks like each of these have a non-breaking space (&nbsp) at the end so I'm assuming you copied the formula from a webpage and unintentionally included a space.  This space is not the same character as the one produced when hitting the spacebar on your keyboard.  Just delete that extra space after the last parenthesis and your formula should work.
0
 
LVL 8

Author Comment

by:kingjely
ID: 37785762
Hi
I copied and pasted from my spreadsheet directly onto here

=IFERROR(SUM(($B$34)/($B$33)),0)
=IFERROR(SUM(($B$34)/($B$33)),"0")
=IFERROR(SUM(($B$34)/($B$33)),"END OF MONTH")

Both dont work
0
 
LVL 16

Assisted Solution

by:Peter Kwan
Peter Kwan earned 50 total points
ID: 37785821
Please try:

=if(iserror(SUM(($B$34)/($B$33))),0)
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 50 total points
ID: 37786882
SUM function isn't required here, lets remove that for a start so you are left with

=$B$34/$B$33

Now that will only give you a #DIV/0 error if the divisor (B33 here) is zero or blank, so you can check that

=IF($B$33=0,0,$B$34/$B$33)

IFERROR is only available in Excel 2007 and later - in earlier versions you'll get #NAME? error, my suggestion will work in any version of Excel

regards, barry
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question