div/0 and #num!

B

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?
 
B

bastien86

bastien86 said:
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)
 
B

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)
 
D

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
 
I

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top