Converting Polynomial equation

[jmenche]

Hi,

I fit a polynomial line to a chart and got the following equation: -5E+06x^2 + 7E+07x + 1E+08

How do I use this as a formula in a cell?

Thanks
Jeff

 

[jmenche]

I actually need help fixing a forecast of mine (or explaining) but just realized that you cannot attach a file.

Can I email someone a file?

 

[shg]

You could put a workbook on dropbox or box.net and post a link.

Using the coefficients from a trendline formula on a graph is a bad idea -- there is insufficient precision shown by default, and if you fix that by formatting, it is an error-prone, manual mess to copy the coefficients.

The alternative is to use LINEST to get the coefficients on the worksheet, which is easy, but forecasting with a polynomial is a bad idea -- they will fit anything, but the fit doesn't have much meaning. A linear (constant change YoY) or exponential (constant mutiple YoY) trendline makes more sense. You can also get those coefficients with LINEST.

 

[jmenche]

Thanks for the reply. Here is what I am trying to do. I have two years of sales by month and would like to forecast the next 12 months. What would be the best way to do that?


https://www.dropbox.com/s/di9zk4uh2u28wzy/Mr Excel.xlsx?dl=0

 

[shg]

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

3​

nPds​

4​

26​

5​

Period​

"Month"​

Sales​

Centered Moving Average​

2. Deseas Sales​

"Month"​

Average​

1. Seasonal Index​

Period​

"Month"​

Deseas Forecast​

4. Seasonalized Forecast​

6​

1​

1​

203,005,432​

268,417,835​

1​

0.756​

0.756​

27​

296,631,702​

224,343,686.35​

7​

2​

2​

226,512,619​

269,786,418​

2​

0.840​

0.840​

28​

297,772,764​

250,009,948.34​

8​

3​

3​

256,928,553​

270,973,922​

3​

0.948​

0.948​

29​

298,918,215​

283,424,411.86​

9​

4​

4​

273,629,408​

272,008,999​

4​

1.006​

1.006​

30​

300,068,073​

301,855,634.94​

10​

5​

5​

313,696,609​

272,901,824​

5​

1.149​

1.149​

31​

301,222,354​

346,250,639.64​

11​

6​

6​

370,954,905​

273,651,321​

6​

1.355​

1.356​

32​

302,381,075​

409,900,243.99​

12​

7​

7​

386,627,902​

1.409​

274,321,813​

7​

1.409​

1.409​

33​

303,544,254​

427,813,875.77​

13​

8​

8​

355,867,698​

1.293​

275,143,614​

8​

1.293​

1.293​

34​

304,711,907​

394,110,999.26​

14​

9​

9​

308,782,450​

1.118​

276,060,576​

9​

1.118​

1.119​

35​

305,884,051​

342,140,946.72​

15​

10​

10​

269,147,199​

0.971​

277,100,667​

10​

0.971​

0.971​

36​

307,060,705​

298,247,310.52​

16​

11​

11​

232,158,090​

0.834​

278,208,366​

11​

0.834​

0.834​

37​

308,241,884​

257,220,328.41​

17​

12​

12​

194,888,882​

0.697​

279,478,264​

12​

0.697​

0.697​

38​

309,427,608​

215,773,490.71​

18​

13​

13​

174,345,766​

0.620​

280,979,953​

13​

0.620​

0.620​

39​

310,617,892​

192,735,865.20​

19​

14​

1​

213,689,928​

0.756​

282,545,090​

Totals​

13 ​

13.000 ​

40​

311,812,756​

235,825,175.51​

20​

15​

2​

238,434,335​

0.840​

283,985,703​

41​

313,012,215​

262,804,988.65​

21​

16​

3​

270,451,109​

0.948​

285,235,708​

LOGEST Regression​

m​

1.0038​

42​

314,216,289​

297,929,541.76​

22​

17​

4​

288,030,955​

1.006​

286,325,262​

​

b​

267,422,242​

43​

315,424,994​

317,304,040.27​

23​

18​

5​

330,206,957​

1.149​

287,265,078​

44​

316,638,349​

363,971,098.05​

24​

19​

6​

390,478,847​

1.355​

288,054,022​

​

3. Monthly Growth Rate​

0.38%​

45​

317,856,372​

430,878,169.80​

25​

20​

7​

406,976,739​

1.409​

288,759,803​

46​

319,079,079​

449,708,587.66​

26​

21​

8​

374,597,576​

289,624,857​

47​

320,306,491​

414,280,861.13​

27​

22​

9​

325,034,158​

290,590,080​

48​

321,538,623​

359,651,078.75​

28​

23​

10​

283,312,841​

291,684,913​

49​

322,775,496​

313,511,048.57​

29​

24​

11​

244,376,937​

292,850,912​

50​

324,017,126​

270,384,382.45​

30​

25​

12​

205,146,192​

294,187,646​

51​

325,263,533​

226,816,373.32​

31​

26​

13​

183,521,859​

295,768,372​

52​

326,514,734​

202,599,725.35​

See the last tab of the workbook at https://app.box.com/shared/5gi9g8x23p

 

[jmenche]

You rock!

 

[jmenche]

Do I need to mark this thread done?

 

[shg]

You're welcome.