using error type and if statement to return a value.

[GAIDEN]

help,

if error.type(column C) is <=7, i want the value of "0" returned. otherwise
i want the value in column B returned. the formula i'm using in column D is
IF(ERROR.TYPE(C1)<=7, 0, B1). i'm getting the desired "0" value if there is
an error type in column C but not the value of column B.
A B C D

98 105 #N/A 0
97 104 #N/A 0
96 103 #N/A 0
95 102 #N/A 0
94 101 #N/A 0
93 100 #N/A 0
92 99 #N/A 0
91 98 98 #N/A
90 97 97 #N/A
89 96 96 #N/A

 

[Bob Phillips]

=IF(ISERROR(C1),IF(ERROR.TYPE(C1)<=7, 0, B1),B1)

 

[Max]

Try in C1, copied down: =IF(ISERROR(C1), 0, B1)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik

 

[Max]

Try in C1, copied down: =IF(ISERROR(C1), 0, B1)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik

 

[GAIDEN]

it didn't work if entered it in C1 but it worked when entered in D1. thanks
for the help.

 

[Max]

Welcome, and apologies for the typo in the response, it should be in D1, not
C1, as you mention.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik

 

[Pete_UK]

ERROR.TYPE itself returns the error #N/A if the cell being tested does
not actually contain an error (yes, strange logic!!), so you would
need to test for that as well. Better to avoid using it (as you are
not actually making use of the error value), and do it something like
this:

=IF(ISERROR(C1),0,B1)

Hope this helps.

Pete