How to insert a blank value with a function

[MJ]

Hi all,

I have a row with numbers that are the result of a VLOOKUP function, and I want cells to be blank (empty) when the source of the lookup is blank. The cells must be blank, not 0, because the results are plotted in a chart where blank values are interpolated.

The current formula is:

=IF(ISBLANK(E2),"",VLOOKUP(E2,R2:S12,2,FALSE))

This results in "" being interpreted as 0 in the chart . What do I use in stead of ""?

Thanks!
This message was edited by MJ on 2002-02-25 20:39
This message was edited by MJ on 2002-02-25 20:40

 

[Guest]

If I understand your intent correctly (I was/am quite unsure about MJ's), wouldn't just

=VLOOKUP(E2,R2:S12,2,FALSE)

suffice?

Not necessarily... what if R2:R12 contains a 0?
[/quote]

There's no 0's in R2:S12; it is a fixed vocabulary that is then translated to a fixed list of numbers starting at 1.0.

The simplest =VLOOKUP thus suffices! I didn't realize #N/A let's the chart interpolate that value without explicetly being told to, this is way better than my previous hacks. I thin kI discarded my first attempt with a simple VLOOKUP because I didn't like the look of all the #N/A's in the set and assumed it would cause errors in the chart. But as the column is hidden anyway, this is actually absolutely perfect.

Thanks!

 

[MJ]

Whoops, last post was of course me...

 

[Juan Pablo González]

Not necessarily... what if R2:R12 contains a 0?

Mark, if R2:R12 did have a 0, wouldn't be "necesary" to plot it ? I mean, the data IS there, then, it should be plotted... not ?

 

[Aladin Akyurek]

MJ,

Quel denoument!

I didn't dare to think that you just wanted to suppress 0-values from your charts. Mark has gone straight to that conclusion. Looks like rightly so.

Aladin

On 2002-02-26 07:58, Anonymous wrote:

If I understand your intent correctly (I was/am quite unsure about MJ's), wouldn't just

=VLOOKUP(E2,R2:S12,2,FALSE)

suffice?

Not necessarily... what if R2:R12 contains a 0?

There's no 0's in R2:S12; it is a fixed vocabulary that is then translated to a fixed list of numbers starting at 1.0.

The simplest =VLOOKUP thus suffices! I didn't realize #N/A let's the chart interpolate that value without explicetly being told to, this is way better than my previous hacks. I thin kI discarded my first attempt with a simple VLOOKUP because I didn't like the look of all the #N/A's in the set and assumed it would cause errors in the chart. But as the column is hidden anyway, this is actually absolutely perfect.

Thanks!
[/quote]

 

[Gra06021]

F16= Start date
G16= End Date

=NETWORKDAYS(F16,IF(ISBLANK(G16),TODAY(),G16))

If you end date is blank (they are still working for you) then it will give the number of days that they have worked between their start day and the current date.

 

[Smitty]

Welcome to the Board!

F16= Start date
G16= End Date

=NETWORKDAYS(F16,IF(ISBLANK(G16),TODAY(),G16))

If you end date is blank (they are still working for you) then it will give the number of days that they have worked between their start day and the current date.

Did you mean to reply to this thread? If you PM me the URL of the thread you wanted to reply to, I'll move your response.

 

[Gra06021]

Whoops! yes this went to the wrong thread!