Trying to work around blank cells posting as 0's in chart for moving forecast

[mythos2112]

Hi all,

I am building a forecast - and my super wants it to continue, in the chart, from the actual data, except with dotted lines. I am pulling the data from a cube, updated frequently, so I need the table and chart to self-maintain (i.e. I don't want to come in each day and update it, other than running the data source update).

Because the way the forecast function works - it won't work if any of the data in the "Actual" column is "#N/A", I'm using an IFERROR to blank the cell (""). However, where the chart ignores #N/A, it interprets the blank cell as a 0, which makes the chart look aesthetically displeasing. Here's what I mean:

Here's some sample data (I am using fake data, and in the "Actual" column, that data would be pulled by cube functions) that I used to generate the above image:

Excel 2012

	A	B	C	D
1	Month Date	Month Num	Actuals	Forecast
2	1-Jan	1	24	#N/A
3	1-Feb	2	35	#N/A
4	1-Mar	3	53	#N/A
5	1-Apr	4	73	#N/A
6	1-May	5	67	#N/A
7	1-Jun	6	78	#N/A
8	1-Jul	7	89	#N/A
9	1-Aug	8	105	105
10	1-Sep	9		114.5018
11	1-Oct	10		125.1931
12	1-Nov	11		136.2408
13	1-Dec	12		146.9321

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 =IF(B2=MONTH(TODAY())-1,C2,IF(C2="",FORECAST(A2,$C$2:$C2,$A$2:$A2),NA())) D3 =IF(B3=MONTH(TODAY())-1,C3,IF(C3="",FORECAST(A3,$C$2:$C3,$A$2:$A3),NA())) D4 =IF(B4=MONTH(TODAY())-1,C4,IF(C4="",FORECAST(A4,$C$2:$C4,$A$2:$A4),NA())) D5 =IF(B5=MONTH(TODAY())-1,C5,IF(C5="",FORECAST(A5,$C$2:$C5,$A$2:$A5),NA())) D6 =IF(B6=MONTH(TODAY())-1,C6,IF(C6="",FORECAST(A6,$C$2:$C6,$A$2:$A6),NA())) D7 =IF(B7=MONTH(TODAY())-1,C7,IF(C7="",FORECAST(A7,$C$2:$C7,$A$2:$A7),NA())) D8 =IF(B8=MONTH(TODAY())-1,C8,IF(C8="",FORECAST(A8,$C$2:$C8,$A$2:$A8),NA())) D9 =IF(B9=MONTH(TODAY())-1,C9,IF(C9="",FORECAST(A9,$C$2:$C9,$A$2:$A9),NA())) C10 =IFERROR(IF(MONTH(A10)<MONTH(TODAY()),100,""),"") D10 =IF(B10=MONTH(TODAY())-1,C10,IF(C10="",FORECAST(A10,$C$2:$C10,$A$2:$A10),NA())) C11 =IFERROR(IF(MONTH(A11)<MONTH(TODAY()),100,""),"") D11 =IF(B11=MONTH(TODAY())-1,C11,IF(C11="",FORECAST(A11,$C$2:$C11,$A$2:$A11),NA())) C12 =IFERROR(IF(MONTH(A12)<MONTH(TODAY()),100,""),"") D12 =IF(B12=MONTH(TODAY())-1,C12,IF(C12="",FORECAST(A12,$C$2:$C12,$A$2:$A12),NA())) C13 =IFERROR(IF(MONTH(A13)<MONTH(TODAY()),100,""),"") D13 =IF(B13=MONTH(TODAY())-1,C13,IF(C13="",FORECAST(A13,$C$2:$C13,$A$2:$A13),NA())) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Any ideas of how I can make this work? Either serve the rest of the Actual column using #N/A, without breaking the forecast, or any other workaround that will give me both a clean chart and updating data? Thank you all!

 

[circledchicken]

Hi and welcome to the forum,

One option might be to add a new dummy Actuals column, with a formula like this:

=IF(C2 = "", NA(), C2)

Then plot this dummy column instead for the Actuals.

I haven't looked through your formulas in detail, but in your actual spreadsheet you should try to factor out repeated calculations, especially references to volatile functions like TODAY() (see here for more details - Volatile Excel Functions -Decision Models).

You can calculate the repeating parts once in a separate cell and just refer back to that stored value in your formulas to avoid unnecessary calculation.

 

[mythos2112]

Thank you very much! I'll try that right now. As for the volatile functions, I appreciate the heads-up; I'll make a single Today cell and use that as suggested. I appreciate the help!

 

[circledchicken]

You're welcome - thanks for the feedback.