ExcelBanter - IF with #VALUE!

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - IF with #VALUE! (https://www.excelbanter.com/excel-discussion-misc-queries/130780-if-value.html)

Is it possible to return a value for #VALUE! ie

=IF(F6=#VALUE!,0,1)

???

=if(iserror(f6),0,1)

But that traps all possible errors--not just #value!'s.

Ashby wrote:

Is it possible to return a value for #VALUE! ie

=IF(F6=#VALUE!,0,1)

???

--

Dave Peterson

Try something like the following to test the value of A1. It will return one
of three results:

"#VALUE In A1" if A1 has a #VALUE error
"Other Error In A1" if A1 has some other error (e.g., #DIV/0!)
"No Error In A1" if there is no error in A1.

=IF(ISERROR(A1),IF(ERROR.TYPE(A1)=3,"#VALUE In A1","Other Error In A1"),"No
Error In A1")

If you don't want to consider #N/A as an error (it may or may not be a real
error, depending your context) change ISERROR(A1) to ISERR(A1).

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Ashby" wrote in message
...
Is it possible to return a value for #VALUE! ie

=IF(F6=#VALUE!,0,1)

???