Predict Future Values

P

pagrender

Well-known Member
Joined
Sep 3, 2008
Messages
652
Hello,

Okay, I'll start off by saying I know very little about statistics, so I should apologize if this is a silly question.

How can I predict future values? In the example below, I have birth data from 2001 to 2010. I would like Excel to project the birth rates for 2011 to 2014. I used the average function in this example. Should I use a different function?

Thanks!
Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 53px"><COL style="WIDTH: 38px"><COL style="WIDTH: 38px"><COL style="WIDTH: 38px"><COL style="WIDTH: 38px"><COL style="WIDTH: 38px"><COL style="WIDTH: 38px"><COL style="WIDTH: 38px"><COL style="WIDTH: 38px"><COL style="WIDTH: 38px"><COL style="WIDTH: 38px"><COL style="WIDTH: 38px"><COL style="WIDTH: 38px"><COL style="WIDTH: 38px"><COL style="WIDTH: 38px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Year </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2001</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2002</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2003</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2004</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2005</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2006</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2007</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2008</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2009</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2010</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2011</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2012</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2013</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2014</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">Births </TD><TD style="TEXT-ALIGN: center"> 281 </TD><TD style="TEXT-ALIGN: center"> 309 </TD><TD style="TEXT-ALIGN: center"> 274 </TD><TD style="TEXT-ALIGN: center"> 275 </TD><TD style="TEXT-ALIGN: center"> 290 </TD><TD style="TEXT-ALIGN: center"> 340 </TD><TD style="TEXT-ALIGN: center"> 327 </TD><TD style="TEXT-ALIGN: center"> 330 </TD><TD style="TEXT-ALIGN: center"> 324 </TD><TD style="TEXT-ALIGN: center"> 354 </TD><TD style="TEXT-ALIGN: right"> 310 </TD><TD style="TEXT-ALIGN: right"> 313 </TD><TD style="TEXT-ALIGN: right"> 314 </TD><TD style="TEXT-ALIGN: right"> 318 </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>L2</TD><TD>=AVERAGE(B2:K2)</TD></TR><TR><TD>M2</TD><TD>=AVERAGE(C2:L2)</TD></TR><TR><TD>N2</TD><TD>=AVERAGE(D2:M2)</TD></TR><TR><TD>O2</TD><TD>=AVERAGE(E2:N2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Actually, I think I got it... It looks like it's either the GROWTH or TREND functions.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 43px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"><COL style="WIDTH: 35px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Year</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2001</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2002</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2003</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2004</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2005</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2006</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2007</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2008</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2009</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2010</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2011</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2012</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">XXX</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2014</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Births</TD><TD style="TEXT-ALIGN: right">281</TD><TD style="TEXT-ALIGN: right">309</TD><TD style="TEXT-ALIGN: right">274</TD><TD style="TEXT-ALIGN: right">275</TD><TD style="TEXT-ALIGN: right">290</TD><TD style="TEXT-ALIGN: right">340</TD><TD style="TEXT-ALIGN: right">327</TD><TD style="TEXT-ALIGN: right">330</TD><TD style="TEXT-ALIGN: right">324</TD><TD style="TEXT-ALIGN: right">354</TD><TD style="TEXT-ALIGN: right">354</TD><TD style="TEXT-ALIGN: right">362</TD><TD style="TEXT-ALIGN: right">371</TD><TD style="TEXT-ALIGN: right">371</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>L2</TD><TD>=GROWTH($B$2:$K$2,$B$1:$K1,L$1:$N$1)</TD></TR><TR><TD>M2</TD><TD>=GROWTH($B$2:$K$2,$B$1:$K1,M$1:$N$1)</TD></TR><TR><TD>N2</TD><TD>=GROWTH($B$2:$K$2,$B$1:$K1,N$1:$N$1)</TD></TR><TR><TD>O2</TD><TD>=GROWTH($B$2:$K$2,$B$1:$K1,$N$1:O$1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 
Upvote 0
You would be better advised to graph the data and look to see what trend (if any is) a good fit.

Trend assumes a linear relationship and Growth exponential.

Neither of these two equations have high R2 values (when charted), mainly as your data seems really to have two relatively tight clusters with a steep upwards jump in 2006

Without knowing what the contibuting factors may be driving the birth rates an average of the last 5 years seems to be as good a guesstimate as any.

Cheers

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
V
  • Question Question
Issue with scraping with VBA
Replies
1
Views
692
John_w
J

Forum statistics

Threads
1,224,592
Messages
6,179,789
Members
452,942
Latest member
VijayNewtoExcel

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