LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default Replace #/Div

"T. Valko" wrote...
....
=IF(ISERROR(your_formula),"N/A",your_formula)

That will trap *all* errors, not just the #DIV/0! error.

....

In the particular case of #DIV/0!, the most common causes are an
AVERAGE function call against a range containing no number values or a
formula like N/D where D = 0. Those are better handled using

=IF(COUNT(range),AVERAGE(range),"N/A")

or

=IF(-D<0,N/D,"N/A") [-D rather than D intentional]

More generally, it's possible to trap only specific errors using

=IF(COUNT(1/(ERROR.TYPE(formula)={1;2})),"N/A",formula)

This example traps #NULL! and #DIV/0! errors.


 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find and Replace - Replace with Blank Space Studebaker Excel Discussion (Misc queries) 4 April 3rd 23 10:55 AM
Can I replace a ' at the beginning of a text cell using Replace Hilde Excel Discussion (Misc queries) 4 September 10th 07 06:22 PM
Replace dialog should put focus on "Find What" not "Replace With" Michael Williams Excel Discussion (Misc queries) 0 May 24th 06 12:45 PM
find and replace - replace data in rows to separated by commas msdker Excel Worksheet Functions 1 April 15th 06 01:00 AM
How can I use replace(alt+H) for mutiple items needing replace Gery Excel Worksheet Functions 1 June 15th 05 05:51 PM


All times are GMT +1. The time now is 09:30 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"