Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 17th 05, 04:31 PM
flummoxed
 
Posts: n/a
Default Vlookup finds a blank, but returns a zero - HELP!

I have a chart, and several series contain data that comes from elsewhere
using a VLOOKUP. However, when the VLOOKUP finds a blank, it returns a zero.
I want the chart to interpolate the data, but Excel charts can only
interpolate blanks, not zeroes. I've used ISBLANK with the VLOOKUP to return
what appears to be a blank, but the chart still sees it as a zero, and thus
will not interpolate. I'd like to know if there's a way to set the value of
a cell to NULL, because "" doesn't work...

  #2   Report Post  
Old January 17th 05, 07:21 PM
Dave O
 
Posts: n/a
Default

You have a couple of options for a workaround: one is to nest the
VLOOKUP into an IF formula, so the logic flow would be: IF(vlookup
value = "","",normal vlookup)

Another way to do it is to nest a MATCH function inside an INDEX
function, best shown by example: suppose values in A1:A4 are
penny
nickel
dime
quarter

....and suppose values in B1:B4 are
..01
..05
..1
..25

Copy the A1:A4 values into D14 (to show that the values can appear in
a different cell, must be spelled exactly, etc). Then in cell E1,
enter this formula:
=INDEX($A$1:$B$4,MATCH(D1,$A$1:$A$4,0),2)

The English translation is: find an exact match of D1 in the range
A1:A4 (which becomes a row reference for the INDEX function); in the
range A1:B4 go to the row specified by the MATCH function and the
column number supplied (i.e., 2).

  #3   Report Post  
Old January 17th 05, 08:22 PM
Aladin Akyurek
 
Posts: n/a
Default

Insert an additional column before the column where you have your
VLOOKUP formula. Suppose that column Y is the column housing those
retrieval formula:

In X2 enter & copy down:

=IF(Y2=0,#N/A,Y2)

Exclude column Y from charts.

flummoxed wrote:
I have a chart, and several series contain data that comes from elsewhere
using a VLOOKUP. However, when the VLOOKUP finds a blank, it returns a zero.
I want the chart to interpolate the data, but Excel charts can only
interpolate blanks, not zeroes. I've used ISBLANK with the VLOOKUP to return
what appears to be a blank, but the chart still sees it as a zero, and thus
will not interpolate. I'd like to know if there's a way to set the value of
a cell to NULL, because "" doesn't work...

  #4   Report Post  
Old January 17th 05, 10:55 PM
flummoxed
 
Posts: n/a
Default

I appreciate your suggestion, but I've already nested the VLOOKUP in an IF
statement, similar to the way you've suggested. Mine follows this pattern:
IF(ISBLANK(VLOOKUP),"",VLOOKUP) I get a result that appears as a blank, but
still carries a value of zero. in your example, if you substitute a blank
for the value of a nickel instead of .05, then chart the results column (with
interpolation for blank cells), you'll see my dilemma. It won't interpolate,
because it has a zero value. The INDEX MATCH combination provides the same
result as a VLOOKUP.

"Dave O" wrote:

You have a couple of options for a workaround: one is to nest the
VLOOKUP into an IF formula, so the logic flow would be: IF(vlookup
value = "","",normal vlookup)

Another way to do it is to nest a MATCH function inside an INDEX
function, best shown by example: suppose values in A1:A4 are
penny
nickel
dime
quarter

....and suppose values in B1:B4 are
..01
..05
..1
..25

Copy the A1:A4 values into D14 (to show that the values can appear in
a different cell, must be spelled exactly, etc). Then in cell E1,
enter this formula:
=INDEX($A$1:$B$4,MATCH(D1,$A$1:$A$4,0),2)

The English translation is: find an exact match of D1 in the range
A1:A4 (which becomes a row reference for the INDEX function); in the
range A1:B4 go to the row specified by the MATCH function and the
column number supplied (i.e., 2).


  #5   Report Post  
Old January 18th 05, 01:50 AM
RagDyer
 
Posts: n/a
Default

The #N/A error is very friendly to making graphs show null.
So, simply revise your formulas to replace the null [ "" ] with "#N/A" (no
quotes).
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"flummoxed" wrote in message
...
I appreciate your suggestion, but I've already nested the VLOOKUP in an IF
statement, similar to the way you've suggested. Mine follows this pattern:
IF(ISBLANK(VLOOKUP),"",VLOOKUP) I get a result that appears as a blank, but
still carries a value of zero. in your example, if you substitute a blank
for the value of a nickel instead of .05, then chart the results column
(with
interpolation for blank cells), you'll see my dilemma. It won't
interpolate,
because it has a zero value. The INDEX MATCH combination provides the same
result as a VLOOKUP.

"Dave O" wrote:

You have a couple of options for a workaround: one is to nest the
VLOOKUP into an IF formula, so the logic flow would be: IF(vlookup
value = "","",normal vlookup)

Another way to do it is to nest a MATCH function inside an INDEX
function, best shown by example: suppose values in A1:A4 are
penny
nickel
dime
quarter

....and suppose values in B1:B4 are
..01
..05
..1
..25

Copy the A1:A4 values into D14 (to show that the values can appear in
a different cell, must be spelled exactly, etc). Then in cell E1,
enter this formula:
=INDEX($A$1:$B$4,MATCH(D1,$A$1:$A$4,0),2)

The English translation is: find an exact match of D1 in the range
A1:A4 (which becomes a row reference for the INDEX function); in the
range A1:B4 go to the row specified by the MATCH function and the
column number supplied (i.e., 2).





  #6   Report Post  
Old January 18th 05, 02:17 PM
flummoxed
 
Posts: n/a
Default

Thanks! That worked like a charm -- Excel sometimes works in mysterious
ways, doesn't it? Sorry if I didn't keep a response within the group, but
this is my initial forray into this medium.

"RagDyer" wrote:

The #N/A error is very friendly to making graphs show null.
So, simply revise your formulas to replace the null [ "" ] with "#N/A" (no
quotes).
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"flummoxed" wrote in message
...
I appreciate your suggestion, but I've already nested the VLOOKUP in an IF
statement, similar to the way you've suggested. Mine follows this pattern:
IF(ISBLANK(VLOOKUP),"",VLOOKUP) I get a result that appears as a blank, but
still carries a value of zero. in your example, if you substitute a blank
for the value of a nickel instead of .05, then chart the results column
(with
interpolation for blank cells), you'll see my dilemma. It won't
interpolate,
because it has a zero value. The INDEX MATCH combination provides the same
result as a VLOOKUP.

"Dave O" wrote:

You have a couple of options for a workaround: one is to nest the
VLOOKUP into an IF formula, so the logic flow would be: IF(vlookup
value = "","",normal vlookup)

Another way to do it is to nest a MATCH function inside an INDEX
function, best shown by example: suppose values in A1:A4 are
penny
nickel
dime
quarter

....and suppose values in B1:B4 are
..01
..05
..1
..25

Copy the A1:A4 values into D14 (to show that the values can appear in
a different cell, must be spelled exactly, etc). Then in cell E1,
enter this formula:
=INDEX($A$1:$B$4,MATCH(D1,$A$1:$A$4,0),2)

The English translation is: find an exact match of D1 in the range
A1:A4 (which becomes a row reference for the INDEX function); in the
range A1:B4 go to the row specified by the MATCH function and the
column number supplied (i.e., 2).




  #7   Report Post  
Old January 18th 05, 04:15 PM
RagDyeR
 
Posts: n/a
Default

You DID keep your response within the news group,

AND, thanks for the feed-back.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"flummoxed" wrote in message
news Thanks! That worked like a charm -- Excel sometimes works in mysterious
ways, doesn't it? Sorry if I didn't keep a response within the group, but
this is my initial forray into this medium.

"RagDyer" wrote:

The #N/A error is very friendly to making graphs show null.
So, simply revise your formulas to replace the null [ "" ] with "#N/A" (no
quotes).
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"flummoxed" wrote in message
...
I appreciate your suggestion, but I've already nested the VLOOKUP in an IF
statement, similar to the way you've suggested. Mine follows this

pattern:
IF(ISBLANK(VLOOKUP),"",VLOOKUP) I get a result that appears as a blank,

but
still carries a value of zero. in your example, if you substitute a blank
for the value of a nickel instead of .05, then chart the results column
(with
interpolation for blank cells), you'll see my dilemma. It won't
interpolate,
because it has a zero value. The INDEX MATCH combination provides the

same
result as a VLOOKUP.

"Dave O" wrote:

You have a couple of options for a workaround: one is to nest the
VLOOKUP into an IF formula, so the logic flow would be: IF(vlookup
value = "","",normal vlookup)

Another way to do it is to nest a MATCH function inside an INDEX
function, best shown by example: suppose values in A1:A4 are
penny
nickel
dime
quarter

....and suppose values in B1:B4 are
..01
..05
..1
..25

Copy the A1:A4 values into D14 (to show that the values can appear in
a different cell, must be spelled exactly, etc). Then in cell E1,
enter this formula:
=INDEX($A$1:$B$4,MATCH(D1,$A$1:$A$4,0),2)

The English translation is: find an exact match of D1 in the range
A1:A4 (which becomes a row reference for the INDEX function); in the
range A1:B4 go to the row specified by the MATCH function and the
column number supplied (i.e., 2).








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
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. QUEST41067 Excel Discussion (Misc queries) 1 January 15th 05 10:29 PM
Non Blank - Blank Cells???? Reggie Excel Discussion (Misc queries) 3 January 12th 05 01:04 AM
I need a VLOOKUP to display 0 or blank instead of N/A Rachel Excel Discussion (Misc queries) 3 January 8th 05 08:33 PM
Using Jet to read excel file returns blank for last cell - sometim Ron Excel Discussion (Misc queries) 1 December 9th 04 09:21 AM
Vlookup returns incorrect match Smichaud Excel Discussion (Misc queries) 2 November 30th 04 11:51 AM


All times are GMT +1. The time now is 03:38 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017