Re: How to make line chart that excludes entries that are "0"
You can do this without named formulas and without VBA. You have to have a range of cells for the chart data that is distinct, or separate, from the original data. I've posted an example worksheet you can download at:
https://www.dropbox.com/s/7vkyubj997ol3la/dynamic_charts_by_worksheet_formulas.xlsx?dl=0
I used three scenarios:
<tbody>
[TD="class: xl67"]Alpha scenario[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]Beta scenario[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]Gamma scenario[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"]Alpha[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"]Beta[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl65"]Gamma[/TD]
[TD="class: xl66, align: right"]2017-06-30[/TD]
[TD="class: xl67, align: right"]100[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2017-06-30[/TD]
[TD="class: xl67, align: right"]100[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2017-06-30[/TD]
[TD="class: xl67, align: right"]100[/TD]
[TD="class: xl66, align: right"]2018-06-30[/TD]
[TD="class: xl67, align: right"]75[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2018-06-30[/TD]
[TD="class: xl67, align: right"]125[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2018-06-30[/TD]
[TD="class: xl67, align: right"]100[/TD]
[TD="class: xl66, align: right"]2019-06-30[/TD]
[TD="class: xl67, align: right"]50[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2019-06-30[/TD]
[TD="class: xl67, align: right"]150[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2019-06-30[/TD]
[TD="class: xl67, align: right"]100[/TD]
[TD="class: xl66, align: right"]2020-06-30[/TD]
[TD="class: xl67, align: right"]25[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2020-06-30[/TD]
[TD="class: xl67, align: right"]175[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2020-06-30[/TD]
[TD="class: xl67, align: right"]100[/TD]
[TD="class: xl66, align: right"]2021-06-30[/TD]
[TD="class: xl67, align: right"]0[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2021-06-30[/TD]
[TD="class: xl67, align: right"]200[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2021-06-30[/TD]
[TD="class: xl67, align: right"]100[/TD]
[TD="class: xl66, align: right"]2022-06-30[/TD]
[TD="class: xl67, align: right"]0[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2022-06-30[/TD]
[TD="class: xl67, align: right"]225[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2022-06-30[/TD]
[TD="class: xl67, align: right"]100[/TD]
[TD="class: xl66, align: right"]2023-06-30[/TD]
[TD="class: xl67, align: right"]0[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2023-06-30[/TD]
[TD="class: xl67, align: right"]250[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2023-06-30[/TD]
[TD="class: xl67, align: right"]0[/TD]
[TD="class: xl66, align: right"]2024-06-30[/TD]
[TD="class: xl67, align: right"]0[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2024-06-30[/TD]
[TD="class: xl67, align: right"]275[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66, align: right"]2024-06-30[/TD]
[TD="class: xl67, align: right"]0[/TD]
</tbody>
You can select the scenario to graph using a data validation dropdown, cell C5 in my example. The data for the chosen scenario appears in cells B8:C16.
I used a helper cell, M5, to count the non-zero cells in C9:C16, the y-values for the line chart.
=COUNTIF($C$9:$C$16, "<>0")
The first chart uses two multi-cell INDEX+MATCH array formulas to calculate the data to chart. INDEX+MATCH returns #N/A when it fails, and these values won't be plotted on the x-axis. Cells L8:M16 –
[TABLE="class: grid, width: 156"]
<tbody>[TR]
[TD][/TD]
[TD]Alpha[/TD]
[/TR]
[TR]
[TD="align: right"]2017-06-30[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]2018-06-30[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD="align: right"]2019-06-30[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2020-06-30[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]
Single multi-cell array formula (Ctrl+Shift+Enter) entered in L9:L16 –
=$B$9:INDEX($B$9:$B$16, $M$5)
Single multi-cell array formula (Ctrl+Shift+Enter) entered in M9:M16 –
=$C$9:INDEX($C$9:$C$16, $M$5)
An alternative is to use IF formulas to determine the points to chart. I show this for the second chart; the data is in cells L25:M32. The helper cell, M5, is used again.
In L25 and copied downward:
=IF(ROW()-ROW($L$24)<=$M$5, B9, NA())
In M25 and copied downward:
=IF(ROW()-ROW($M$24)<=$M$5, C9, NA())
IF formulas and array formulas can cause slow worksheet calculation when used with large data sets. This may or may not be noticeable with your data. The helper cell was used, rather than incorporating the non-zero count formula into the plotted data formulas, to reduce the total number of calculations Excel has to perform.
I used linked cells to change the chart titles as the chosen data set changes.