+ Reply to Thread
Results 1 to 6 of 6

Chart empty values

  1. #1
    Registered User
    Join Date
    04-21-2019
    Location
    Denver
    MS-Off Ver
    Excel 2016
    Posts
    5

    Chart empty values

    I have a chart where some of the data series comes from cells that have formulas but no value. My chart lines all plunge to zero even though there is no value in the cells. Is there a way to prevent this?
    Capture.PNG

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,084

    Re: Chart empty values

    Are you saying you don't want to chart Apr - Sep as there is no data or simple you don't want a line dropping off to zero.

    If you don't want the points to show up if there isn't any data, then look into dynamic charts.

    If you are still having troubles, how about posting a small sample workbook so nobody has to recreate your data.

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    04-21-2019
    Location
    Denver
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Chart empty values

    I don't want the line to drop to zero. File Attached
    Attached Files Attached Files

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,084

    Re: Chart empty values

    You will have to setup dynamic ranges to do this. If there are no values the line will drop to zero but there isn't a way to keep that from happening with empty data.

    I left the one chart with the sample to follow.
    Attached Files Attached Files

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,084

    Re: Chart empty values

    Edit:

    Sorry, completely forgot that you can use NA() to not plot a line.

    Change E7 and to the right to >> =IF(E4="",NA(),(E6/E4))

    Do the same for row 19, row 31, and so on

    You will then have to update the formula in C7 to >> =AVERAGEIF(E7:P7,">0")

  6. #6
    Registered User
    Join Date
    04-21-2019
    Location
    Denver
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Chart empty values

    Thank you! The formula change worked perfectly.

+ 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. Paste Values empty cell, not empty
    By warston in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 12-23-2020, 08:16 PM
  2. [SOLVED] how to insert empty rows based on column B cell values and paste above values with macro?
    By genetist in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-11-2018, 11:47 AM
  3. Is chart empty
    By Vadimchik in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-25-2017, 10:51 AM
  4. Replies: 0
    Last Post: 07-14-2015, 05:10 PM
  5. Empty values in pivot chart changed to zero after copy/paste
    By krong in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-30-2014, 08:58 PM
  6. [SOLVED] VLOOKUP - US values brought back as empty UK values
    By AlanoR in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-08-2014, 12:56 PM
  7. Need to verify Bad Dates & Empty values & '0' Values in different columns
    By dmraokanuri in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-01-2014, 02:47 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