Excel formula to not graph 0's

By Doug robertson ·
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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Request for Clarification

by Doug robertson In reply to Clarifications

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

Collapse -

Thinking out loud

by robo_dev In reply to Excel formula to not grap ...

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).

Collapse -

Excel formula that won't graph zeros

by Jon Peltier In reply to Excel formula to not grap ...

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.

Collapse -

Reponse To Answer

by jillita_angelfire In reply to Excel formula that won't ...

This helps a lot. Thanks.

Collapse -

I'm not sure I understand

by LocoLobo In reply to Excel formula to not grap ...

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.

Collapse -

No Blanks

by Jon Peltier In reply to I'm not sure I understand

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.

Collapse -

Reponse To Answer

by jillita_angelfire In reply to I'm not sure I understand

Thanks. This really helped me today.

Collapse -

that did it....Thank you ALL

by Doug robertson In reply to Excel formula to not grap ...

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

Related Discussions

Related Forums