excel chart 1

[Mark2Aus]

Hi everyone.

I have Sheet1 containing a Chart linked to another sheet (Sheet 2). Each cell in Sheet2 has a formula linked to another sheet (DailyData) and as the day progresses, new data is entered in DailyData. The formulas in Sheet2 then returns a value for the corresponding cell.
But, my Chart keeps showing a line that drops to zero to all points where there is no calculated value until the formula in Sheet2 shows a value. Until then, it treats the cells with a formula but represented as blank in Sheet2 as zero and this affects the scale of the chart.
Is there a way out?
The problem is: Excel treats a blank cell containing a formula not as blank, but as zero. Is that right?
Thanks for any help.
Mark

 

[dhulbert]

If the cell has no value then by default it's going to be a zero, there is no other way Excel can graph it.
If you wanted you could put an if statment in the celsl of sheet2 that give the cell a default value if the DailyData cell is empty.

 

[SkipVought]

Hi,

In Tools/Options/Chart - there is a option button for plot empty cells as... and you can select not plotted

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[GlennUK]

If you have formulae that you want ignored during plotting, then have it surrounded by an IF function, where the non-plotting result should be set as NA() ... like this

=IF(A2>99,NA(),A2)



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.