Tom's Guide | Tom's Hardware | Tom's Games

Computing.Net > Forums > Office Software > Excel help/surpressing #DIV/0!

Computer Problems? Computing.Net has over 1,000,000 posts about all things technology related! Over 90% answered within 24 hours! Click here to start participating now! Also, be sure to check out the New User Guide.

Excel help/surpressing #DIV/0!

Reply to Message Icon

Name: GiveMePatience
Date: August 11, 2005 at 14:29:10 Pacific
OS: XP
CPU/Ram: 1.8ghz/512mb
Comment:

I'm a new poster (long time reader). I'm hoping someone can help me with a problem I've encountered. I'm working w/ a pretty elaborate excel spreadsheet w/ several worksheets that include various formulas.

I'm trying to include a "summary" worksheet that summarizes information from all the worksheets. Users are supposed to enter info in each of the worksheets so the totals at the bottom of the worksheets display the result of various formulas. Problem is that when a worksheet has not been completed, the total shows the "#DIV/0!" message. As the user enters data, the formula works fine.

The summary sheet references the total on each worksheet (and thus displays the "#DIV/0!" message). I've been trying to change the display on the summary sheet to "No Total" with an IF statement(example: =IF('Sheet1'!$B$25<0,'Sheet1'!$B$25,"No Total"). I've tried several variations of this, including reversing logic, and all other variations, but nothing seems to work. I'm starting to think that maybe an IF statement is not the best way to go about this. Any help would be appreciated.

GiveMePatience....SERENITY NOW!



Sponsored Link
Ads by Google

Response Number 1
Name: rhawk7938
Date: August 11, 2005 at 16:37:30 Pacific
Reply:

Would this work?

=IF('Sheet1'!$B$25="",'Sheet1'!$B$25,"No Total")

Let me know.


0

Response Number 2
Name: Bryco
Date: August 11, 2005 at 19:21:51 Pacific
Reply:

=IF(ISERROR(A1/B1),0,A1/B1)

Meaning if A1/B1 is an error then 0 else A1/B1.

HTH
Bryan


0

Response Number 3
Name: Bryco
Date: August 11, 2005 at 19:25:32 Pacific
Reply:

Or in your case...
=IF(ISERROR('Sheet1'!$B$25,"",'Sheet1'!$B$25)
Meaning if 'Sheet1'!$B$25 is an error then blank else 'Sheet1'!$B$25.

Regards,
Bryan


0

Response Number 4
Name: Bryco
Date: August 11, 2005 at 19:27:57 Pacific
Reply:

Oops, forgot a closing parenthesis.
=IF(ISERROR('Sheet1'!$B$25),"",'Sheet1'!$B$25)
Meaning if 'Sheet1'!$B$25 is an error then blank else 'Sheet1'!$B$25.

Bryan


0

Response Number 5
Name: GiveMePatience
Date: August 12, 2005 at 06:07:33 Pacific
Reply:

Works Perfectly! Thanks Bryan.


0

Related Posts

See More



Response Number 6
Name: Bryco
Date: August 12, 2005 at 10:17:46 Pacific
Reply:

Glad to share.

Bryan


0

Response Number 7
Name: alwayscurious
Date: August 18, 2005 at 08:52:52 Pacific
Reply:

I am also struggling with this issue. This is the formula that I have used but still get an error message of #VALUE at least that was a change from #DIV/0. I am computing the percentage of change between crime statistics from one month to the next. Thankfully they are zero from time to time. But this causes a huge problem for my spreadsheet.

=IF(C5=0," ", D5-C5)/ABS(C5)

Any suggestions would be appreciated.


0

Sponsored Link
Ads by Google
Reply to Message Icon

How to change Default sav... outlook rules



Post Locked

This post is quite old and has been locked from receiving new replies. Please create a new posting instead.


Go to Office Software Forum Home


Sponsored links
Ads by Google


Results for: Excel help/surpressing #DIV/0!
Avoid 0 & #DIV/0! www.computing.net/answers/office/avoid-0-div0/8453.html

Excel Help www.computing.net/answers/office/excel-help/9582.html

EXCEL Help www.computing.net/answers/office/excel-help/801.html