Solved

#REF!

Posted on 2012-04-04
3
185 Views
Last Modified: 2012-04-18
Hi,

Can i change, when i have in a cell the result #REF!, it to 0 or Null (like #N/A)?

Tx
0
Comment
Question by:sonmic
3 Comments
 
LVL 19

Expert Comment

by:helpfinder
ID: 37805462
yes.
use IFERROR formula, e.g. =IFERROR(IF(B1='c'!A1;1;2);"UUPS")
0
 
LVL 23

Accepted Solution

by:
Eirman earned 500 total points
ID: 37805480
IFERROR will nor work in excel 2003. It works in excel 2007 & 2010

Use IF and ISERROR in combination
http://www.excel-examples.com/iserror.htm

or

Use this really excellent excel add-on for painless error handling (there is a free home use version)
http://www.asap-utilities.com/
0
 
LVL 6

Expert Comment

by:wshark83
ID: 37831793
you need to use the IF() and ISERR() combination for #DIV/0 or #REF error's
if you get #N/A then you need to use ISNA() function....

i.e.:  =IF(ISERR(a1+b1),0,a1+b1) or IF(ISNA(a1+b1),"Null",a1+b1)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

679 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