• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Line Graph by Date Selection: Ignore Blanks & Zeros

J

jdppep

Member
I am trying to plot a dynamic line graph without zeros or blanks. The line graph updates by selection of a start and end date in dropdown lists.

I have a named range for each chart series, ex: EODTasks_Graph
=OFFSET(EODTasks,MATCH(EndDate,DateList,0)-1,0,EndDate-StartDate+1)

How do I make make the chart ignore zeros, blanks and errors?

Thanks!
 
Hi, jdppep!
Change the data to NA, which is omitted in the chart.
=IF(<value_ok>,<value_ok>,NA())
Regards!
 
SirJB7,

Thanks for the reply!

When I try this:
=IF(OFFSET(EODTasks,MATCH(EndDate,DateList,0)-1,0,EndDate-StartDate+1),(OFFSET(EODTasks,MATCH(EndDate,DateList,0)-1,0,EndDate-StartDate+1)),NA())

The chart continues to plot these data points as zeros. The formula itself returns #Value when I attempt it in an open cell on the spreadsheet.
 
Hi, jdppep!
Give a look at the uploaded file. 1st chart for column A with zero, 2nd chart for column B with NA.
Regards!
 

Attachments

  • Dynamic Line Graph by Date Selection_ Ignore Blanks & Zeros (for jdppep at chandoo.org).xlsx
    12.9 KB · Views: 13
Thank you! I needed to add the formula to the data table. Simply adding it to the named ranged was unsuccessful.
 
Hi, jdppep!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
PS: As it's a classic recipe I went directly to the sample file without checking your poster formula, but giving a look at it, shouldn't it be like this?
=IF(OFFSET(EODTasks,MATCH(EndDate,DateList,0)-1,0,EndDate-StartDate+1))<>0,OFFSET(EODTasks,MATCH(EndDate,DateList,0)-1,0,EndDate-StartDate+1),NA())
 
Hey guys,

at the moment I am trying to implement a dynamic line chart for an abc analysis. I am struggling with the horizontal axis. I have a range with values and want to show the abc curve in the line chart. I have a fixed range of numbers with only I certain number of values. When I update the chart next time there could be more products in the list. That is why I filled the remaining cells with NV. But the chart includes the NV values on the horizontal axis. I want the chart only to count 8 products on the horizontal axis. Is this possible?

I attached the file as well to clarify.

Thanks,
Axel
 

Attachments

  • Dynamic Line Graph by Date Selection.xlsx
    12 KB · Views: 8
Hi,

thanks for the file but that was not what I was looking for. I do not want to adjust the range manually all the time. As I said the number of products do vary depending on the category I am analysing. So I want to keep the range fixed but still only show the items with values.

Regards,
Axel
 
Woow, did not notice that. That is awesome and exactly what I was looking for.

Thanks a lot and best regards,
Axel
 
You must log in or register to reply here.
Back
Top