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
...
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 D1:D4 (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).
|