How to insert a blank value with a function
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: How to insert a blank value with a function

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,752
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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 ]

  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,752
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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?

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-02-26 07:14, Aladin Akyurek wrote:
    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?
    Not necessarily... what if R2:R12 contains a 0?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com