Software

Ask something
Question Locked

Excel formula to not graph 0's

0 helpful
9 replies

by doug robertson

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a spread sheet that collects data all year.. A formula converts the operator input data weekly to a metric conversion. This formaula gives 0 if no data is entered and a range of 0-1000 once entered.
This is then moved to a graph that shows current demand compared to last years data. I need a way to not plot the 0 on the graph for the dates upcoming but still plot a 0 once data has been input.
thanks

9 replies

by doug robertson

What we have is data from last year. 52 weeks of weekly and a total for the year. THis is plotted on a graph. This year we are plotting the weekly total and the total to year on the same graph. Most plotted points are dirived via a formula to convert the readings to a standard format.
If i use the NA() option it works great for the current week but the following weeks it shows as a 0 and places it on the graph. We do not want to plot the future weeks. I need a formula that shows a <0 and a blank cell as not a 0 or text. Some weeks the plot may need to be 0.
thanks for any help given

by robo_dev

Perhaps your formula needs to return something like -1 when there is no data, or just leave it blank.

You could then have a condition where anything less than zero is treated a special way (like not getting plotted).

by jon peltier

Change the 0 in the formula that is returned if there is no value to NA(). This puts #N/A into the cell. Line and XY charts in Excel do not plot a point if its X or Y value is #N/A.

If you change the 0 to "" to make it look like a blank, Excel considers this a piece of text, and still plots it as a zero.

by locolobo

Does the formula rebuild the whole sheet each week? Or does it just go to the cell that corresponds to the current data and put in a value? If the latter, use John Peltiers suggestion and instead of zeroes change all your upcoming cells to #NA# or maybe a space character, something that an Excel chart won't plot.

OTOH if you have filled the formula and date columns down for the whole year add an if to your formula that returns a blank character if the current date is less than the date in the date column.

by jon peltier

If you add a blank character ("" or " " or any text at all) Excel will chart it as a zero.

Don't fill down for a whole year if you only have partial data. Instead convert your data into a list (Excel 2003) or table (2007 or 2010). Put the formula in the appropriate column. As data is added at the bottom of the list/table, the formula is copied down to the row with the new data. Also, a chart that is based on columns in this list/table will update as the length of the list/table changes.

by doug robertson

We got it a woking....Thank you flor the feed back....works great.

This discussion is closed to new replies.