I have a combination bar/line graph where operational data are inputted each week and it calculates a utilisation factor. This utilisation factor is a ling on the graph *the only plotted against the secondary axis). Because this value is calculated by a formula it returns a value of 0 for all weeks where data is yet to be inputted. On the graph this manifests as a line that sits on the x-axis for all future weeks.

instead of the line doing the above, I want the line to not exist on the graph at all until a value has been entered. I’ve tried having the formula return a blank value (that is the formula returns “”) and I’ve also tried an autoformatting but I can’t seem to make it plot as if it is blank.

Any ideas? and no I don’t want to have to copy the formula over every week

diddly-squat said:

I have a combination bar/line graph where operational data are inputted each week and it calculates a utilisation factor. This utilisation factor is a ling on the graph *the only plotted against the secondary axis). Because this value is calculated by a formula it returns a value of 0 for all weeks where data is yet to be inputted. On the graph this manifests as a line that sits on the x-axis for all future weeks.

instead of the line doing the above, I want the line to not exist on the graph at all until a value has been entered. I’ve tried having the formula return a blank value (that is the formula returns “”) and I’ve also tried an autoformatting but I can’t seem to make it plot as if it is blank.

Any ideas? and no I don’t want to have to copy the formula over every week

So I guess you need both the x and y values to be blank so you need a separate column of dates that is blank when the y value is blank? Or have I misunderstood?

https://stackoverflow.com/questions/1119614/return-empty-cell-from-formula-in-excel/39351425#39351425

Laugh Out Loud

dv said:

diddly-squat said:

I have a combination bar/line graph where operational data are inputted each week and it calculates a utilisation factor. This utilisation factor is a ling on the graph *the only plotted against the secondary axis). Because this value is calculated by a formula it returns a value of 0 for all weeks where data is yet to be inputted. On the graph this manifests as a line that sits on the x-axis for all future weeks.

instead of the line doing the above, I want the line to not exist on the graph at all until a value has been entered. I’ve tried having the formula return a blank value (that is the formula returns “”) and I’ve also tried an autoformatting but I can’t seem to make it plot as if it is blank.

Any ideas? and no I don’t want to have to copy the formula over every week

So I guess you need both the x and y values to be blank so you need a separate column of dates that is blank when the y value is blank? Or have I misunderstood?

I think you’ve misunderstood essentially what I want is a value of 0 to plot as if it’s an empty cell

diddly-squat said:

dv said:

diddly-squat said:

I have a combination bar/line graph where operational data are inputted each week and it calculates a utilisation factor. This utilisation factor is a ling on the graph *the only plotted against the secondary axis). Because this value is calculated by a formula it returns a value of 0 for all weeks where data is yet to be inputted. On the graph this manifests as a line that sits on the x-axis for all future weeks.

instead of the line doing the above, I want the line to not exist on the graph at all until a value has been entered. I’ve tried having the formula return a blank value (that is the formula returns “”) and I’ve also tried an autoformatting but I can’t seem to make it plot as if it is blank.

Any ideas? and no I don’t want to have to copy the formula over every week

So I guess you need both the x and y values to be blank so you need a separate column of dates that is blank when the y value is blank? Or have I misunderstood?

I think you’ve misunderstood essentially what I want is a value of 0 to plot as if it’s an empty cell

Right so you have a separate pair of columns that are are used for the graph. Use a formula to blank both of these, the X and the Y, when the value is 0.

Use the right pair of columns to make the graph.

dv said:

diddly-squat said:

dv said:

So I guess you need both the x and y values to be blank so you need a separate column of dates that is blank when the y value is blank? Or have I misunderstood?

I think you’ve misunderstood essentially what I want is a value of 0 to plot as if it’s an empty cell

Right so you have a separate pair of columns that are are used for the graph. Use a formula to blank both of these, the X and the Y, when the value is 0.

Use the right pair of columns to make the graph.

that won’t work.. the chart sees the column right as a 0 if they contain a formula..

diddly-squat said:

dv said:

diddly-squat said:

I think you’ve misunderstood essentially what I want is a value of 0 to plot as if it’s an empty cell

Right so you have a separate pair of columns that are are used for the graph. Use a formula to blank both of these, the X and the Y, when the value is 0.

Use the right pair of columns to make the graph.

that won’t work.. the chart sees the column right as a 0 if they contain a formula..

Okay, but it doesn’t have any date to place it against.

dv said:

diddly-squat said:

dv said:

Right so you have a separate pair of columns that are are used for the graph. Use a formula to blank both of these, the X and the Y, when the value is 0.

Use the right pair of columns to make the graph.

that won’t work.. the chart sees the column right as a 0 if they contain a formula..

Okay, but it doesn’t have any date to place it against.

as a label or as an ordinate

dv said:

diddly-squat said:

dv said:

Right so you have a separate pair of columns that are are used for the graph. Use a formula to blank both of these, the X and the Y, when the value is 0.

Use the right pair of columns to make the graph.

that won’t work.. the chart sees the column right as a 0 if they contain a formula..

Okay, but it doesn’t have any date to place it against.

I’ll have a play

I mean I’m using Libre so it might not be identical but the method I am using is working for me. You can see there’s no dot for the 8th.

dv said:

I mean I’m using Libre so it might not be identical but the method I am using is working for me. You can see there’s no dot for the 8th.

Assuming d-s doesn’t want to switch to Libre, one way around it would be to create an array formula for the values that only returns values when the date is empty or 0. A VBA function might be the easiest way to do that.

Also try returning #NA rather than zero. See the last two comments at:

https://newtonexcelbach.com/2012/09/05/when-is-an-xy-chart-not-an-xy-chart/

The Rev Dodgson said:

Also try returning #NA rather than zero. See the last two comments at:

https://newtonexcelbach.com/2012/09/05/when-is-an-xy-chart-not-an-xy-chart/

This…

> I want the line to not exist on the graph at all until a value has been entered.

Very difficult to do in Excel. It routinely changes blanks to zeros in all formulas.
This is a major bug in Excel.
I’ve given up and now only use the manual method of deleting cell contents one at a time when I want blanks.

Good luck.