referenced blank cell returned zero (0) as values

[Abdmujib]

Hello everyone,

The problem is that, I have some values I want to reference from one sheet to another in order to use it to plot a graph.

so when the cell that is referenced is blank, it will return zero (0) as the value, which the zero would be plotted.
I tried using if() to put "", but it sees it as a text and spoil the graph.

in the picture above, let assumed Column B,C is on sheet 1, while Column I,J,K are are on sheet 2. I reference the depth value in sheet 1 to sheet 2 and same goes to Value, but the issue is the zero that is there. I want it to return blank too, and not space ("") that it will see as a text data type.


Thank you

 

[Peter_SSs]

See if something like this would help. The #NA values should not show up on your graph.

24 08 17.xlsm
	B	C	D	E	F	G	H	I	J
4	2	3						2	3
5	4	4						4	4
6	6	5						6	5
7	8							8	#N/A
8	10							10	#N/A
9	12							12	#N/A
10	14							14	#N/A
Stop blanks as 0

Cell Formulas
Range	Formula
J4:J10	J4	=LET(v,VLOOKUP(I4,B$4:C$10,2,0),IF(v="",NA(),v))

 

[Abdmujib]

See if something like this would help. The #NA values should not show up on your graph.

24 08 17.xlsm
	B	C	D	E	F	G	H	I	J
4	2	3						2	3
5	4	4						4	4
6	6	5						6	5
7	8							8	#N/A
8	10							10	#N/A
9	12							12	#N/A
10	14							14	#N/A
Stop blanks as 0

Cell Formulas
Range	Formula
J4:J10	J4	=LET(v,VLOOKUP(I4,B$4:C$10,2,0),IF(v="",NA(),v))

Thank you sir, the laptop am using to do it, is an office laptop and it uses 2016 version of excel not 365 sir, so the Let() won't work sir

 

[Peter_SSs]

In future if you need an answer in a different version to that in your forum profile, best to say so in your opening post.

Try this instead

24 08 17.xlsm
	B	C	D	E	F	G	H	I	J
4	2	3						2	3
5	4	4						4	4
6	6	5						6	5
7	8							8	#N/A
8	10							10	#N/A
9	12							12	#N/A
10	14							14	#N/A
Stop blanks as 0

Cell Formulas
Range	Formula
J4:J10	J4	=IF(VLOOKUP(I4,B$4:C$10,2,0)="",NA(),VLOOKUP(I4,B$4:C$10,2,0))

 

[Abdmujib]

See if something like this would help. The #NA values should not show up on your graph.

24 08 17.xlsm
	B	C	D	E	F	G	H	I	J
4	2	3						2	3
5	4	4						4	4
6	6	5						6	5
7	8							8	#N/A
8	10							10	#N/A
9	12							12	#N/A
10	14							14	#N/A
Stop blanks as 0

Cell Formulas
Range	Formula
J4:J10	J4	=LET(v,VLOOKUP(I4,B$4:C$10,2,0),IF(v="",NA(),v))

It works sir, I split out the formula for the Let() function into another cell. then I applied the next argument. It works but, the issue was that the blank appeared as 0 not "", so I make the Let() argument =0.

Then from your formula I was able to have a good idea that NA() would work, so I used isblank() in my previous formula.
Tough I am not sure yet the 2016 has Isblank(), I will get to check when I get to office today, if not I will go with your formula

Thank you so much sir

 

[Peter_SSs]

If you are not actually looking up the value from the first range but targeting it directly then your ISBLANK formula should be fine, or you could shorten it to

=IF(C4="",NA(),C4)