Thread: Zero Values on Charts

I am currently developing a spreadsheet which will allow the Actuals, Forecast and Budgets of a financial year to be viewed.

The data will spread across financial periods i.e. P1 - P12 and by their nature the forecast and budget data will be filled in for all periods.

My problem is that I intend to add the actuals at the end of each month, so that they can be viewed against budget and forecast. However, I don't want to have the line returning to 0 when the actuals for certain periods haven't been entered.

Does anyone know how you can have a line graph that looks at a range for actuals (the cells contain formulas to automatically update) without showing the 0 values? I haven't found a way to stop the graph returning the 0 values.

Any help would be greatly appreciated.

Apparently you're using formulas to pull in the data. Use something like
this:

=IF(<link or calculation>=0,NA(),<link or calculation>)

The NA() produces an ugly #N/A error in the table, which you can hide using
conditional formatting. No point is plotted for #N/A, but a line will
connect the points on either side of it. There is no line for any
consecutive #N/A values at the start or end of a range.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


"SkyBluesFan" <SkyBluesFan.22mcfn_1138911907.9487@excelforum-nospam.com>
wrote in message
news:SkyBluesFan.22mcfn_1138911907.9487@excelforum-nospam.com...
>
> I am currently developing a spreadsheet which will allow the Actuals,
> Forecast and Budgets of a financial year to be viewed.
>
> The data will spread across financial periods i.e. P1 - P12 and by
> their nature the forecast and budget data will be filled in for all
> periods.
>
> My problem is that I intend to add the actuals at the end of each
> month, so that they can be viewed against budget and forecast. However,
> I don't want to have the line returning to 0 when the actuals for
> certain periods haven't been entered.
>
> Does anyone know how you can have a line graph that looks at a range
> for actuals (the cells contain formulas to automatically update)
> without showing the 0 values? I haven't found a way to stop the graph
> returning the 0 values.
>
> Any help would be greatly appreciated.
>
>
> --
> SkyBluesFan

Thank you for your swift and helpful response.

The solution was just what I needed.

Thank you for your swift and helpful response.

The solution was just what I needed.