div/0 and #num!

[bastien86]

I have a column of data which is calculated based on the column adjacent to
it. I want to create an IF statement that will check to see if the number in
the column is div/0 or #num! and if it is, display the value above it instead
of trying to calculate something.

ex
a b c d

1 1 4
2 4 7
3 6 9
4 #num! 9***

this forumla doesn't seem to work =if(a4=#num!,b3,a4+3)

how do i say the logical statement a4=#num! or div/0?

 

[bastien86]

I have a column of data which is calculated based on the column adjacent to
it. I want to create an IF statement that will check to see if the number in
the column is div/0 or #num! and if it is, display the value above it instead
of trying to calculate something.

ex
a b c d
1 1 4
2 4 7
3 6 9
4 #num! 9***

this forumla doesn't seem to work =if(a4=#num!,b3,a4+3)

how do i say the logical statement a4=#num! or div/0?
i'd really like an if statemet that says =if(a4=(#num! OR div/0!),=a3,a4+3)

 

[bastien86]

Sorry i'm new to this board

i'd really like an if statemet that says =if(a4=(#num! OR div/0!),=a3,a4+3)

 

[Dav]

Perhaps if it is only an error you are interested in try

iserror(a4)

eg if(iserror(a4),b3,a4+3)

This also traps some other errors, but that should not be a problem t
you I imagine

Regards

Da

 

[Ian]

Posting the same request 3 times in 6 minutes won't get you a quicker
response!

Try this:
=IF(OR(ERROR.TYPE(A4)=2,ERROR.TYPE(A4)=6),B3,A4+3)

See ERROR.TYPE in Excel Help for an explanation.

 

[bastien86]

PERFECT! thanks man