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

C

cdbacon

New Member
Joined
May 2, 2013
Messages
10
I have the following dataset:

[TABLE="width: 384"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]2008
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]2009
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]2010
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]2011
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]2012
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]2013
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]2014
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]49
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]44
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]38
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]33
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]42
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]32
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]245
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]269
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]253
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]323
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]374
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]335
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]122
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]157
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]120
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]175
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]190
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]174
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]D
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]202
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]227
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]174
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]271
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]282
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]246
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]E
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]219
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]224
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]191
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]214
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]250
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]135
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]F
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]99
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]95
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]94
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]98
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]103
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]G
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]92
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]89
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]103
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]121
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]142
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]136
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]H
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]11
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]22
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]17
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]55
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]62
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]114
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Total
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1027
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1131
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]991
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1286
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1440
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1275
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]


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,
 
Sort by date Sort by votes
Code: 
[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.
 
Upvote 0
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...
 
Upvote 0
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...
 
Last edited:
Upvote 0
cdbacon said:
I have the following dataset:

[TABLE="width: 384"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]2008[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]2009[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]2010[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]2011[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]2012[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]2013[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]2014[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]49[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]44[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]38[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]33[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]42[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]32[/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]245[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]269[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]253[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]323[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]374[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]335[/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]122[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]157[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]120[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]175[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]190[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]174[/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]D[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]202[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]227[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]174[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]271[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]282[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]246[/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]E[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]219[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]224[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]191[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]214[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]250[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]135[/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]F[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]87[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]99[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]95[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]94[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]98[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]103[/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]G[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]92[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]89[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]103[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]121[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]142[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]136[/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]H[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]55[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]62[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]114[/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Total[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1027[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1131[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]991[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1286[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1440[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1275[/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
</tbody>[/TABLE]


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

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:

[TABLE="class: grid, width: 64"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2014
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]32[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]387[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]194[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]284[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]167[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]101[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]158
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]123[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]1446[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
NeonRedSharpie said:
Is it possible your data is text? But even that shouldn't mess anything up.
Click to expand...

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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
joeu2004 said:
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.
Click to expand...

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.
 
Upvote 0
cdbacon said:
Apologies for not including the context of the formula. =FORECAST($J$9,E10:J10,$E$9:$J$9)
Click to expand...

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.

cdbacon said:
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.
Click to expand...

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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
how to random all numbers
Replies
7
Views
304
Fluff
Fluff
T
  • Solved
Help on putting a number into a range
2 3
Replies
22
Views
330
Peter_SSs
Peter_SSs
E
  • Solved
Returning specific texts within a wide range of data coming from the Web
Replies
8
Views
344
Peter_SSs
Peter_SSs
W
  • Solved
How many rows between each data point in a column
Replies
3
Views
681
Fluff
Fluff
T
  • Question Question
INDEX and MATCH formula for rainfall cessation
Replies
6
Views
330
Eric W
Eric W

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top