Nothing more annoying than when you are reporting on actuals throughout the year, than the chart looking like the one above. One solution is to manually update the actuals in the data source each month, but you want to set it up that it wil automatically pick up the actuals in the chart when they are there and leave future months blank instead of dropping to 0.
Using an IF function does’t work if you are using “” for value if true, but it does work if you use an error code for value if true!
So let’s say your actuals value is in cell A1, then create a formula like this to base your chart on, and voila!
=IF(A1=0;NA();A1)
The NA() formula works identical to using #N/A but prevents language issues!
