Forecast returns Div/0 but my variance is not zero?

[cdbacon]

I have the following dataset:

	2008	2009	2010	2011	2012	2013	2014
A	49	44	38	33	42	32
B	245	269	253	323	374	335
C	122	157	120	175	190	174
D	202	227	174	271	282	246
E	219	224	191	214	250	135
F	87	99	95	94	98	103
G	92	89	103	121	142	136
H	11	22	17	55	62	114
Total	1027	1131	991	1286	1440	1275

<TBODY>
</TBODY>

I want to get a forecast for 2014 for each row. Using the Forecast function I get #DIV/0!. I manually calculated the variances and they are not zero.

Is there a formatting issue or something I could be missing? Something other than format I am missing?

Thanks,

 

[NeonRedSharpie]

[LIST]
[*]If x is nonnumeric, FORECAST returns the #VALUE! error value.
[*]If known_y's and known_x's are empty or contain a different number of data points, FORECAST returns the #N/A error value.
[*]If the variance of known_x's equals zero, then FORECAST returns the #DIV/0! error value.
[/LIST]

That's from the help, so it assumes the variance is 0. What is your known_x range? I've never used forecast() before so I'm not sure what your inputs are.

 

[cdbacon]

That's all I can find out. It assumes the variance is 0.
The variables are: X=2014, Known-Y= row values ie H would be 11 22 17 55 62 114, Known-X = 2008 trough 2013.

I've used Forecast before but for some reason or another it doesn't seem to like this data...

 

[NeonRedSharpie]

Using the inputs you have provided, I get the following:

A - 30
B - 383
C - 198
D - 282
E - 174
F - 104
G - 154
H - 114
Total - 1438

Is it possible your data is text? But even that shouldn't mess anything up...

 

[joeu2004]

I have the following dataset:

	2008	2009	2010	2011	2012	2013	2014
A	49	44	38	33	42	32
B	245	269	253	323	374	335
C	122	157	120	175	190	174
D	202	227	174	271	282	246
E	219	224	191	214	250	135
F	87	99	95	94	98	103
G	92	89	103	121	142	136
H	11	22	17	55	62	114
Total	1027	1131	991	1286	1440	1275

<tbody>
</tbody>

I want to get a forecast for 2014 for each row. Using the Forecast function I get #DIV/0!.

When you have a question about function usage, you should show us how you are using the function. Klunk!

If your table above has dates 2008 through 2013 in B1:G1, enter the following formula into H2 and copy down through H10:

=FORECAST($H$1,B2:G2,$B$1:$G$1)

The results should be about:

2014
32
387
194
284
167
101
158
123
1446

<tbody>
</tbody>

 

[joeu2004]

Is it possible your data is text? But even that shouldn't mess anything up.

Au contraire! When I change a row of data to (numeric) text, FORECAST does indeed return a #DIV/0 error.

@cdbacon, verify that at least some of =ISTEXT(B2) etc return TRUE.

To correct, first, be sure the cells are not formatted as Text. Then select each column and use Text To Column to effectively convert the content to numeric.

It is not sufficient to change the format alone from Text to a numeric format.

If there comments do not remedy your problem, I suggest that you upload an example Excel file to a file-sharing website and post the "shared" URL here.

 

[cdbacon]

Apologies for not including the context of the formula. =FORECAST($J$9,E10:J10,$E$9:$J$9)

That is exactly what I have been doing (aside from the cell location) and I get a return of #DIV/0!. Hence the post.

I copied the data from a pivot table, pasted the values, Added the 2014 row and formula (manually, not copied/pasted) to the end of the table and get the error. Yeah, I could have mentioned the pivot table part too. I am curious if there is a format issue or something I overlooked. Yes, I have gone and made sure the cells as in 'number' format, not text or anything else.

 

[NeonRedSharpie]

Au contraire! When I change a row of data to (numeric) text, FORECAST does indeed return a #DIV/0 error.

@cdbacon, verify that at least some of =ISTEXT(B2) etc return TRUE.

To correct, first, be sure the cells are not formatted as Text. Then select each column and use Text To Column to effectively convert the content to numeric.

It is not sufficient to change the format alone from Text to a numeric format.

If there comments do not remedy your problem, I suggest that you upload an example Excel file to a file-sharing website and post the "shared" URL here.

You're right. When I tested my sample data I on'y changed one of the values to text. Changing an entire row of values to text will assume all values are 0, thus, div/0. Silly me not testing all possible error methods.

 

[joeu2004]

Apologies for not including the context of the formula. =FORECAST($J$9,E10:J10,$E$9:$J$9)

No way to tell if your usage is "correct" (or at least what you intended), since you did not provide row and column numbers in your original table, nor an explanation of how the data aligns with rows and columns (as I did).

But if E9:J9 covers the years 2008 through 2013, I would expect 2014 to be in K9. So the first parameter should be K9, not J9.

That is not sufficient to cause FORECAST to return an error. Just an incorrect value.

I copied the data from a pivot table [...]. Yes, I have gone and made sure the cells as in 'number' format, not text or anything else.

As I noted, simply ensuring the cell format is numeric does not ensure that the content is not text. What does =ISTEXT(E9) return, for example? (Test all of the cells.)

Again, the quickest way to resolve such "inexplicable" problems is to upload an example Excel file that demonstrates the problem and post the "shared" URL here.

Often, the devil is in the details. It is difficult for us to anticipate all possible user errors at arm's distance. And it is difficult to lead you by the hand.

 

[cdbacon]

Thanks for all the help.

As noted in the original post, there was a formatting issue. I was unaware of the =istext function since I haven't needed it before.