Chart - Do Not Show Drop Off

Hi

Creating a Burn Up Chart

In attached example - I do not want to see Gold Color "Drop Off" if week has not occurred yet

In my table, I also have function to not show "$0.00" or appear blank

Appears the chart still "sees" this as $0.00 and shows "Drop Off"

Thanks

Have you considered a dynamic range name that covers just weeks with any values and using the range name for the chart?

Richard

If I'm correct, using dynamic range name is still a manual range selection which will require me or user to adjust range on a week to week basis.

Additionally, the line still "drops off"

If not correct - Interested to learn how to use

Thanks

Hi Group

Saw this question asked in the past but don't recall when

I think it was a relatively simple solution


More importantly... Appreciate some assistance

Thanks

My first thought (because I am using LO Calc on this computer and not Excel) -- are you required to use Excel for this? I observe that, opened in LO Calc with no further edits, that there is no drop off for the gold series. LO Calc's chart engine ignores the empty string cells in charting the series.

I know Excel's chart engine has long treated any text (and all errors except N/A) as if they were the value 0. The Excel based solution to this problem is usually to replace any text (including empty string) with NA() so that Excel will ignore that data point in a line chart. More in depth discussion in this essay: https://peltiertech.com/mind-the-gap...g-empty-cells/

Hi MrShorty

Thanks for answering

Not sure what you mean by LO Calc

If modify my function to: =IF(B48>$B$43,NA(),G47+E48) - Get desired graphical results where "drop off" no longer appears

However, now have " #N/A " in table where I rather see blank content when week has not begun

Is there way to have blank data or not show $0.00 in table & still NOT have "Drop Off" (gold bar)?

LO Calc is Libre Office's spreadsheet program.

I see two, maybe three ways to do this:

1) As mentioned by others, use dynamic named ranges for the data series (https://chandoo.org/wp/dynamic-chart-data-series/ )
2) Use the N/A error, then use some kind of conditional formatting to "hide" the N/A text (conditional format using formula like =ISERROR(A1) and have the conditional format match font color to background color when true)
3) Have two copies of the table -- one where you have the formula return "" that you and others will look at. Have a second copy that only the chart can see where the "" are replaced with N/A.

Or, as I've also suggested, use a spreadsheet program whose chart engine knows how to ignore text rather than chart as 0.

Originally Posted by mycon73

Richard

If I'm correct, using dynamic range name is still a manual range selection which will require me or user to adjust range on a week to week basis.

Additionally, the line still "drops off"

If not correct - Interested to learn how to use

Thanks

I seem to remember that for a client a few years ago I incorporated a simple VBA event macro that recreated the chart ranges, probably the sheet activate event for the sheet containing the chart.

Hi MrShorty, Richard Buttrey and hansolu

Thanks for the great feedback & tips

hansolu

Trying to use, =IF(B48>$B$43,NA(),G47+E48)

What and Why is B] used? Am I supposed to replace this with something?

You can always use conditional formatting to hide the error values if required.