PDA

View Full Version : [SOLVED] How to NOT Plot a Cell with Formula = Blank



Cyberdude
05-21-2005, 01:06 PM
For years I keep running into the same problem with charts: I construct two columns of data that I want to plot.
Column "A" contains the normal date that increases one day at a time for each row, so column "A" is essentially a calendar.
Column "B" contains a formula that displays a value for each date in column "A" IF the date in column "A" is equal to or less than today. If the date in column "A" is greater than today, then both columns display a blank. A typical formula might look like:

=If(Indirect("A"&Row())<=Today(),TemperatureVal,"")
The problem is that the chart that is plotting the temperature values drops to zero when it encounters a blank cell value (but a formula is in the cell) in column "B".
Is there a way to have the chart stop plotting when it encounters the blank value created by the formula in the cell for tomorrow, then resume when tomorrow's cell has a non-blank value? :bug:

Ivan F Moala
05-21-2005, 03:26 PM
Try


=IF(INDIRECT("A"&ROW())<=TODAY(),temperatureval,NA())

Jacob Hilderbrand
05-21-2005, 04:51 PM
Just to add a bit to why Ivan's solution works.

NA() will make the value a #N/A error. These errors are not plotted.

Cyberdude
05-22-2005, 12:12 PM
Migawd, to think that all these years I've never read that anywhere. Can't thank you guys enough. :friends: