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 ]

[anno]

you could try =IF(ISBLANK(E2),0,VLOOKUP(E2,R2:S12,2,FALSE)) and use custom format 0;-0;


[ This Message was edited by: anno on 2002-02-25 20:53 ]

[MJ]

Nope, no dice. The chart doesn't heed the formatting of the cells, it still sees a 0.

Thanks though!

[ This Message was edited by: MJ on 2002-02-25 20:58 ]

[MJ]

I solved this with a VB macro that copies over the values from one column to another one if the value in the cell is greater than 0, and clears the cell otherwise. The chart now uses the new, filtered column.

Any tips on performance improvements (only trigger the code if a cell in the original column E changes, or if a cell in column T is recalculated, for example..)?

Here's the code:

Private Sub Worksheet_Calculate()

    Dim rng As Range

    For Each rng In Range("T2", "T250").Cells

        If rng.Value > 0 Then

            Range("U" + CStr(rng.Row)).Value = rng.Value

        Else

            Range("U" + CStr(rng.Row)).Clear

        End If

    Next rng

End Sub

[ This Message was edited by: MJ on 2002-02-25 22:27 ]

[MJ]

Solved it myself (learning more Excel than I wanted to.. )

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 5 Then

        Dim rng As Range

        Set rng = Range("T" + CStr(Target.Row))

        If rng.Value > 0 Then

            Range("U" + CStr(rng.Row)).Value = rng.Value

        Else

            Range("U" + CStr(rng.Row)).Clear

        End If

    End If

End Sub

[Aladin Akyurek]

It's:

=IF(LEN(E2),IF(COUNTIF(R2:R12,E2),VLOOKUP(E2,R2:S12,2,0),""),"")

Aladin

On 2002-02-25 20:39, MJ wrote:
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 [img]/board/images/smiles/icon_frown.gif[/img]. 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 ]

[MJ]

The problem is that as soon as you put a formula in a cell, it is no longer blank. It doesn't matter if the result is an empty string, charts will still interpret that as 0. My solution solves this by not having a formula take care of the cells sourced by the chart, but a VBA event handler, which explicetly can blank cells.

[Mark W.]

=IF(ISBLANK(E2),#N/A,VLOOKUP(E2,R2:S12,2,FALSE))

[Aladin Akyurek]

On 2002-02-26 07:04, Mark W. wrote:
=IF(ISBLANK(E2),#N/A,VLOOKUP(E2,R2:S12,2,FALSE))

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

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

suffice?

[Mark W.]

On 2002-02-26 07:14, Aladin Akyurek 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?