using error type and if statement to return a value.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GAIDEN - 24 Jul 2008 23:50 GMT

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

Reply to this Message

Bob Phillips - 25 Jul 2008 00:23 GMT

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

Signature

__________________________________
HTH

Bob

> help,
>
[quoted text clipped - 18 lines]
> 90 97 97 #N/A
> 89 96 96 #N/A

Reply to this Message

Max - 25 Jul 2008 00:24 GMT

Try in C1, copied down: =IF(ISERROR(C1), 0, B1)

Signature

Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---

> help,
>
[quoted text clipped - 14 lines]
> 90 97 97 #N/A
> 89 96 96 #N/A

Reply to this Message

GAIDEN - 25 Jul 2008 00:31 GMT

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

> Try in C1, copied down: =IF(ISERROR(C1), 0, B1)
> > help,
[quoted text clipped - 15 lines]
> > 90 97 97 #N/A
> > 89 96 96 #N/A

Reply to this Message

Max - 25 Jul 2008 00:37 GMT

Welcome, and apologies for the typo in the response, it should be in D1, not
C1, as you mention.

Signature

Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---

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

Reply to this Message

Max - 25 Jul 2008 00:25 GMT

Try in C1, copied down: =IF(ISERROR(C1), 0, B1)

Signature

Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---

> help,
>
[quoted text clipped - 14 lines]
> 90 97 97 #N/A
> 89 96 96 #N/A

Reply to this Message

Pete_UK - 25 Jul 2008 00:38 GMT

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

> help,
>
[quoted text clipped - 14 lines]
> 90 97 97 #N/A
> 89 96 96 #N/A

Reply to this Message