ERROR.TYPEs need help!

Sazzle scott

New Member
Joined
Feb 1, 2004
Messages
10
:rolleyes: I am trying to write an If statement that check if a cell has an ERROR.TYPE=7 it will appear as " " if false it will return the value of the cell. However it does not recognise the FALSE statement to return the value;

IF(ERROR.TYPE(B8)=7,"",B8)
I will have #N/A! 's in the main worksheet
I could create a another table to filter it out but I thought there has to be an easier way. :oops:
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Sazzle scott said:
:rolleyes: I am trying to write an If statement that check if a cell has an ERROR.TYPE=7 it will appear as " " if false it will return the value of the cell. However it does not recognise the FALSE statement to return the value;

IF(ERROR.TYPE(B8)=7,"",B8)
I will have #N/A! 's in the main worksheet
I could create a another table to filter it out but I thought there has to be an easier way. :oops:

What do you get when TRUE, and what do you get when FALSE?
 

Sazzle scott

New Member
Joined
Feb 1, 2004
Messages
10
HI
ERROR.TYPE 7 is #N/A!

For True i get what I ask for which is " " ( a blank cell)

for False I get an Error message it doesn't want to retuen the value of the cell.

The cells the statement is referring to will either have #N/A! in them or a value i.e. 5
I want to return a blank cell if it sees #N/A! or if it doesn't then return the value of 5

i hope this makes sense
 

Todd Bardoni

Well-known Member
Joined
Aug 29, 2002
Messages
3,042

ADVERTISEMENT

FYI,
" " is not a blank cell...that is if you have a space in there like you posted.

"" will give you a blank cell.

Oh, and

=IF(ISNA(B8),"",B8)

or, if your error has a ! in it then how about,

=IF(B8="#N/A!","",B8)
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Seems, according to the cocumentation, that the ERROR.TYPE can be used only with error-filled cells. Use the construct tbardoni's given you, which does exactly what you want.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,895
Messages
5,766,978
Members
425,392
Latest member
Booknerd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top