• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF statement

D

dgavilanes

Member
Hello All,


I'm having a senior moment so I'm requesting for some help, I have a data source with "(blank)" cells that I need to convert them to #N/A using an IF statement but only where a value is not present, if a value exist then it should remain the same sample.The reason is that when working with graphs, a "(blank)" is treated as a number(0)but an #N/A works fine.


This is what I have:


A=(blank)

B=(blank)

C=10.2


What I'm looking for to happen:

A=N/A

B=N/A

C=10.2


Thanks a bunch!


Dennis
 
Dennis


Firstly, Welcome to the Chandoo.org Forums


=If(A2="",Na(),A2)

Copy down/across
 
Thanks for your quick reply, and happy to be here. I inserted your code but the output is the same. Please allow me to explain it.


My data


A= (blank)

B= (blank)

C= 10.2


when I refence these cell using the code, I get


A=(blank)

B=(Blank)

C= 10.2


What I need to happen is

A= #N/A 'The "blank" should be replaced by #N/A'

B= #N/A 'The "blank" should be replaced by #N/A'

C= 10.2 'the code should not chance this value'

The reason I'm doing so is because I'm using the data source to plot some graphs and a 'blank' is treated as a zero but the #N/A is ignored and treated as an empty and that is what I'm looking for.


Getting closer,


Regards, from Chicago


Dennis
 
Thanks for your quick reply, and happy to be here. I inserted your code but the output is the same. Please allow me to explain it.


My data


A= (blank)

B= (blank)

C= 10.2


when I refence these cell using the code, I get


A=(blank)

B=(Blank)

C= 10.2


What I need to happen is

A= #N/A 'The "blank" should be replaced by #N/A'

B= #N/A 'The "blank" should be replaced by #N/A'

C= 10.2 'the code should not chance this value'

The reason I'm doing so is because I'm using the data source to plot some graphs and a 'blank' is treated as a zero but the #N/A is ignored and treated as an empty and that is what I'm looking for.


Getting closer,


Regards, from Chicago


Dennis
 
Good evening dgavilanes


Huis code works, I copied in to three columns to to take the three data inputs A,B,C and it works .

[pre]
Code: 
Input Data        Output from A Output from B	Output from C
10	#N/A	   #N/A	           10
20		#N/A	    20	          #N/A
30	252		30	    252	          #N/A
10	254	258	10	    254            258
30	40	#N/A	    30	           40
10		25	10	   #N/A	           25
[/pre]
 
I have uploaded the worksheet for you to look at.


https://dl.dropbox.com/u/75495784/dgavilanes.xlsx
 
Dennis


In my formula There is no space between the two " 's

If you use a space in your blanks put a space between the " 's
 
Great to hear from you guys, is there a way to attached a file? This is what I get on my side


Input

A B C

(blank) (blank) 10.2


output below

(blank) (blank) 10.2 ' should be #N/A'

Formula


=IF(A2="",NA(),A2)


I think I see why it does not work on my side!!! the text 'Blank' is permanent on my file because is coming from a pivot table and cannot be deleted.


That's is why I need it to convert to NA.


Regards,


Dennis
 
Hi Hui,


Your code works as long a cell is empty, but the cells I'm working with have the actual text '(blank)' and using the code just duplicates the cell, this came from a pivot table and that's the need to convert it to #N/A.


Thanks for your support


Dennis
 
So change the formula in Template!A2 to suit:

Code: 
=IF(Ptable!A2="(Blank)",NA(),Ptable!A2)


Then copy across and down


I would also recommend reading the 3 Green sticky posts at http://chandoo.org/forums

Had you told us earlier that you were getting data from another worksheet and that it had text and wasn't blank you would have had the correct answer earlier
 
Hui,


You are 'da man' it worked beautiful. I'm so happy that I found this site and you guys were terrific.


Thanks for your support and keep up the great work you are doing!!


Regards,,


Dennis
 
Hi All,


One last question about conditonal formatting, if I want to hide all #N/A as white can I use #FFFFFF, if so where will I place it?


=IF(Ptable!A2="(Blank)",NA(),Ptable!A2)


Thanks,

Dennis
 
Use Conditional Formatting

Select the whole area

Apply CF using a formula

=iserror(A2)

Apply a white Font color
 
You must log in or register to reply here.
Back
Top