Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Stupid If statement question....

I have a vlookup in one column that has results = #N/A

I need to do an if statement, that looks like:
If the field =#N/A, then 0, if not, the field.
Basically, I want to convert all of the #N/A's to a 0 (zero) in another
column....


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Stupid If statement question....

You could copy and paste the results as values, then highlight the column and
'edit ' , 'replace' , find what = #N/A , replace with = 0

"Send Object Command - Two attachments" wrote:

I have a vlookup in one column that has results = #N/A

I need to do an if statement, that looks like:
If the field =#N/A, then 0, if not, the field.
Basically, I want to convert all of the #N/A's to a 0 (zero) in another
column....


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Stupid If statement question....

I know I can that....however, I need to have this in a formula.
I need this done automatically.

Any other suggestions?

Thanks -

"PhilinLondon" wrote:

You could copy and paste the results as values, then highlight the column and
'edit ' , 'replace' , find what = #N/A , replace with = 0

"Send Object Command - Two attachments" wrote:

I have a vlookup in one column that has results = #N/A

I need to do an if statement, that looks like:
If the field =#N/A, then 0, if not, the field.
Basically, I want to convert all of the #N/A's to a 0 (zero) in another
column....


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,574
Default Stupid If statement question....

=IF(ISNA(VLOOKUP([your vlookup]),0,VLOOKUP([your vlookup]))

Dave
--
Brevity is the soul of wit.


"Send Object Command - Two attachments" wrote:

I know I can that....however, I need to have this in a formula.
I need this done automatically.

Any other suggestions?

Thanks -

"PhilinLondon" wrote:

You could copy and paste the results as values, then highlight the column and
'edit ' , 'replace' , find what = #N/A , replace with = 0

"Send Object Command - Two attachments" wrote:

I have a vlookup in one column that has results = #N/A

I need to do an if statement, that looks like:
If the field =#N/A, then 0, if not, the field.
Basically, I want to convert all of the #N/A's to a 0 (zero) in another
column....


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 252
Default Stupid If statement question....

=IF(ISNA(formula),0,formula)

replace formula with your formula, or the cell in question. you can also
use the following formula to account for any other type of error (#DIV/0 for
example).

=IF(ISERROR(formula),0,formula)


"Send Object Command - Two attachments" wrote:

I have a vlookup in one column that has results = #N/A

I need to do an if statement, that looks like:
If the field =#N/A, then 0, if not, the field.
Basically, I want to convert all of the #N/A's to a 0 (zero) in another
column....




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 27
Default Stupid If statement question....


I have ssumed that you have a formula that either gives a value or #N/A

To do what you want you need to refer to the error, not the result

=IF(ISERROR(cell ref),0,1)

If the cell you are pointing to results in an error (eg #N/A) then it
will give a value of 0, if no error found then it will give a value of
1

Hope this helps

Steve

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Stupid If statement question....

Worked like a charm - thansk to all.

"Sloth" wrote:

=IF(ISNA(formula),0,formula)

replace formula with your formula, or the cell in question. you can also
use the following formula to account for any other type of error (#DIV/0 for
example).

=IF(ISERROR(formula),0,formula)


"Send Object Command - Two attachments" wrote:

I have a vlookup in one column that has results = #N/A

I need to do an if statement, that looks like:
If the field =#N/A, then 0, if not, the field.
Basically, I want to convert all of the #N/A's to a 0 (zero) in another
column....


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Stupid List Box Question Michael Link Excel Discussion (Misc queries) 4 April 3rd 06 03:06 PM
If statement Matt Montagliano Excel Discussion (Misc queries) 1 September 8th 05 08:47 PM
Do I need a sumif or sum of a vlookup formula? PeterB Excel Worksheet Functions 0 June 1st 05 12:23 PM
IF Statement question gryfon Excel Worksheet Functions 6 May 4th 05 09:21 AM
If Statement Question carl Excel Worksheet Functions 1 March 11th 05 05:55 AM


All times are GMT +1. The time now is 10:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"