+ Reply to Thread
Results 1 to 18 of 18

i dont want blank data to appear as 0 on my line graph when using hlookup formula

  1. #1
    Forum Contributor
    Join Date
    06-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2019
    Posts
    151

    i dont want blank data to appear as 0 on my line graph when using hlookup formula

    Hi.

    I have a table which is connected to a line graph.

    The table is formulated using data collected using the hlookup. When a cell is blank in the table, the data appears as blank and not 0. However, when i put this table into a line graph, the blank data appears as 0 on the line graph (producing a marker at 0).

    How do i change the hlookup formula to make a blank cell appear blank on the line graph?

    The example of a hlookup formula i have used is - =hlookup($o$21,$bc$6:$bl$56,$bb14+1,0)

    i have tried if(hlookup($o$21,$bc$6:$bl$56,$bb14+1,0))="",NA(),hlookup($o$21,$bc$6:$bl$56,$bb14+1,0)) but that didnt seem to work.

    Does anyone have any ideas?

    Please help!

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: i dont want blank data to appear as 0 on my line graph when using hlookup formula

    HKOOKUP returning a blank cell's value should equal "". You may have stray space characters in cells which appear blank but actually aren't.

    That said, you only want to chart numbers, and there's an easy way to handle that.

    =IFERROR(LOOKUP(1E300,HLOOKUP($O$21,$BC$6:$BL$56,$BB14+1,0)),#N/A)

    If HLOOKUP returns a number, it'd almost certainly be less than 1E300, so LOOKUP would return HLOOKUP's result, and so would IFERROR. If HLOOKUP's result is text, LOOKUP would return #N/A, and IFERROR would also return #N/A. If HLOOKUP refers to a blank cell, through the mysteries of Excel function semantics, LOOKUP would return #VALUE!, and IFERROR would change that to #N/A.

  3. #3
    Forum Contributor
    Join Date
    06-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2019
    Posts
    151

    Re: i dont want blank data to appear as 0 on my line graph when using hlookup formula

    Hi.

    I cant seem to get that working? It comes up with N/A but still shows a marker and line at 0 value on the line graph.

    Thanks.

  4. #4
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: i dont want blank data to appear as 0 on my line graph when using hlookup formula

    Quote Originally Posted by ACrossley1 View Post
    . . . I cant seem to get that working? It comes up with N/A but still shows a marker and line at 0 value on the line graph. . . .
    It works for me. Here's a link to a workbook on OneDrive showing how it works with VLOOKUP rather than HLOOKUP. Using raw data with blanks and "" for y values, I get a garbage chart. Using the formula I suggested in my previous response, I get the intended scatter chart.

  5. #5
    Forum Contributor
    Join Date
    06-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2019
    Posts
    151

    Re: i dont want blank data to appear as 0 on my line graph when using hlookup formula

    Hi.

    Im still struggling to be honest as i only know how to set up the graph using hlookup. (I have attached an example).

    On the example it shows my tables to do all the workings out and the bar and line graph which is connected to the hlookup.

    Basically it shows the % sold of an item (the column), and then the rolling average (as a line). However, when the data is blank, it still shows the line graph as a 0, whereas i want it to be blank if there is no data there.

    Please help?

    Thanks.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: i dont want blank data to appear as 0 on my line graph when using hlookup formula

    Quote Originally Posted by ACrossley1 View Post
    . . . Im still struggling to be honest as i only know how to set up the graph using hlookup. (I have attached an example). . . .
    In the SHOP worksheet, I replaced the formulas in BQ7:BR30 as follows.

    BQ7: =IFERROR(LOOKUP(1E+300,HLOOKUP($O$20,$AG$6:$AZ$56,$AG7+1,0)),#N/A)
    BR7: =IFERROR(LOOKUP(1E+300,HLOOKUP($O$20,$BC$6:$BL$56,$BB7+1,0)),#N/A)

    I selected BQ7:BR7 and filled down into BQ8:BR30. All cells in BQ9:BR30 show #N/A. The chart on top of SHOP!I21:P41 then displays like the following.

    deleteme3.pngAttachment 659670
    Last edited by hrlngrv; 01-26-2020 at 02:52 PM.

  7. #7
    Forum Contributor
    Join Date
    06-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2019
    Posts
    151

    Re: i dont want blank data to appear as 0 on my line graph when using hlookup formula

    Hi.

    Ok thank you.

    Unofrtunately it still seems to still present 0 on my graph. so even though theres a #N/A, there is still a marker and line even though the data from the table is blank

    This is an issue because when the data still represents 0 on the graph, it looks like the rolling average is now zero, when it isnt.

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: i dont want blank data to appear as 0 on my line graph when using hlookup formula

    Your profile says that you are using 2007. I know that when I have done this (following Jon Peltier's tutorial (https://peltiertech.com/mind-the-gap...g-empty-cells/ ), I have never used the #N/A syntax for returning the N/A error. I have always used the NA() function. Perhaps our older version of Excel does not support #N/A syntax for returning the N/A error, but can only use the NA() function. Try replacing #N/A in these formulas with NA().
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  9. #9
    Forum Contributor
    Join Date
    06-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2019
    Posts
    151

    Re: i dont want blank data to appear as 0 on my line graph when using hlookup formula

    Thanks for the reply.

    When i put NA(), it didnt seem to recognise the hlookup as it put a 0 when it should have had a value.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: i dont want blank data to appear as 0 on my line graph when using hlookup formula

    Attach the version of the workbook where you have tried this.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  11. #11
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: i dont want blank data to appear as 0 on my line graph when using hlookup formula

    Quote Originally Posted by MrShorty View Post
    Your profile says that you are using 2007. . . . never used the #N/A syntax for returning the N/A error. I have always used the NA() function. Perhaps our older version of Excel does not support #N/A syntax for returning the N/A error . . .
    The 2K I include in my Excel versions is no joke. I have it installed under Linux (retail license, BTW) and it runs under wine. Excel 2K and Excel in Office 365 both support #N/A and all the other error constants. If you don't believe that Excel 2007 supports this, try a simple test: A1: =NA(); copy A1; paste special as values in B1. What are the contents of B1? Then enter C1: =ISNA(A1) and D1: =ISNA(B1). What do C1 and D1 return?

  12. #12
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: i dont want blank data to appear as 0 on my line graph when using hlookup formula

    Quote Originally Posted by ACrossley1 View Post
    . . .
    Unofrtunately it still seems to still present 0 on my graph. so even though theres a #N/A, there is still a marker and line even though the data from the table is blank . . .
    Using your example.xlsm, here's what I have in SHOP!BQ7:BR30.

    Attachment 659672deleteme4.png

    FWIW, using Excel Online, the chart still shows the zeros despite the #N/A values. OTOH, in Excel from Office 365, it produces the chart in my previous response.

  13. #13
    Forum Contributor
    Join Date
    06-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2019
    Posts
    151

    Re: i dont want blank data to appear as 0 on my line graph when using hlookup formula

    Hi.

    I have attached the document again with the updated formulas. Its still not worked for me unfortunately.

    I have attached a picture of the chart.Screen Shot 2020-01-26 at 20.43.01.png

    Hopefully you will be able to help.

    Thanks.
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    06-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2019
    Posts
    151

    Re: i dont want blank data to appear as 0 on my line graph when using hlookup formula

    also i think my excel is 2019 - 16.33

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: i dont want blank data to appear as 0 on my line graph when using hlookup formula

    Please update your forum profile to reflect this.

  16. #16
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: i dont want blank data to appear as 0 on my line graph when using hlookup formula

    Quote Originally Posted by ACrossley1 View Post
    . . . I have attached a picture of the chart. . . .
    This time I got the all the zeros in the chart's rolling average series too. After some experimentation, looks like you need to change the Chart Type for the ROLLING AVG series from Stacked Line with Markers

    deleteme5.pngAttachment 659691

    to Line with Markers

    deleteme6.pngAttachment 659692

    I am so glad I only use scatter charts myself.

  17. #17
    Forum Contributor
    Join Date
    06-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2019
    Posts
    151

    Re: i dont want blank data to appear as 0 on my line graph when using hlookup formula

    Oh thank you so much! That is really appreciated!

    Thank you for your help, that is great!

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: i dont want blank data to appear as 0 on my line graph when using hlookup formula

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Hide legend items in a line graph when associated value = blank
    By fpunzi in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-13-2020, 11:48 PM
  2. trend line equations dont match data
    By RobH1961 in forum Excel General
    Replies: 6
    Last Post: 11-07-2015, 11:51 PM
  3. Hide legend items in a LINE graph when associated value = zero or blank
    By juriemagic in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-11-2014, 10:06 AM
  4. [SOLVED] Generate a line (X/Y) graph from a single formula instead of a data set
    By Jose Macieira in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-06-2013, 09:06 AM
  5. [SOLVED] dont use formula if cell range is blank
    By bez in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-07-2012, 10:06 AM
  6. [SOLVED] [SOLVED] Have a formula yield a true blank that disconnects graph line
    By Mr. Owl in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-12-2006, 08:35 AM
  7. x-axis dates dont match up with the nodes on the line graph
    By changetires in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-11-2006, 08:50 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1