Forecast using last two values in a column D and E

U

ubulib

New Member
Joined
Mar 5, 2017
Messages
5
am using forecast to tell me a value for a trend of two values.Forecast(x,known y,known x) x is the current date on column A,Known y should be an array of last two values in column F from current date(what am looking for)known x is all the date in column A matching known y.
to illustrate this is where the excel data is coming from column A2:A66 are the dates on x-axis,B2:B66 are high points. Column D identifies points D57,D47.D40,D35,D32,D27 AND D23.if trend lines are drawn between two last points as shown, column F should calculate the points on this lines i.e. F58,F56,F44,F24.what formulate can be insert in column F to pick last two values in column D and "draw a trendline" on these two points thus forecast values with corresponding blank cells on column D?
Please help
[TABLE="width: 433"]
<tbody>[TR]
[TD]Date[/TD]
[TD]High[/TD]
[TD]Low[/TD]
[TD]Hh[/TD]
[TD]Ll[/TD]
[TD]FH[/TD]
[TD]FL[/TD]
[/TR]
[TR]
[TD]12/1/2016[/TD]
[TD="align: right"]1.0668[/TD]
[TD="align: right"]1.0584[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/2/2016[/TD]
[TD="align: right"]1.069[/TD]
[TD="align: right"]1.0625[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/5/2016[/TD]
[TD="align: right"]1.0796[/TD]
[TD="align: right"]1.0505[/TD]
[TD="align: right"]1.0796[/TD]
[TD="align: right"]1.0505[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/6/2016[/TD]
[TD="align: right"]1.0785[/TD]
[TD="align: right"]1.0698[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/7/2016[/TD]
[TD="align: right"]1.0768[/TD]
[TD="align: right"]1.071[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/8/2016[/TD]
[TD="align: right"]1.0861[/TD]
[TD="align: right"]1.0597[/TD]
[TD="align: right"]1.0861[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/9/2016[/TD]
[TD="align: right"]1.063[/TD]
[TD="align: right"]1.0531[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/12/2016[/TD]
[TD="align: right"]1.0652[/TD]
[TD="align: right"]1.0525[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/13/2016[/TD]
[TD="align: right"]1.0667[/TD]
[TD="align: right"]1.0603[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/14/2016[/TD]
[TD="align: right"]1.067[/TD]
[TD="align: right"]1.0497[/TD]
[TD="align: right"]1.067[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/15/2016[/TD]
[TD="align: right"]1.0539[/TD]
[TD="align: right"]1.0366[/TD]
[TD][/TD]
[TD="align: right"]1.0366[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/16/2016[/TD]
[TD="align: right"]1.0473[/TD]
[TD="align: right"]1.0401[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/19/2016[/TD]
[TD="align: right"]1.0479[/TD]
[TD="align: right"]1.0393[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/20/2016[/TD]
[TD="align: right"]1.0417[/TD]
[TD="align: right"]1.0352[/TD]
[TD][/TD]
[TD="align: right"]1.0352[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/21/2016[/TD]
[TD="align: right"]1.0451[/TD]
[TD="align: right"]1.0382[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/22/2016[/TD]
[TD="align: right"]1.0499[/TD]
[TD="align: right"]1.0423[/TD]
[TD="align: right"]1.0499[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/23/2016[/TD]
[TD="align: right"]1.0467[/TD]
[TD="align: right"]1.0427[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/26/2016[/TD]
[TD="align: right"]1.0468[/TD]
[TD="align: right"]1.044[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/27/2016[/TD]
[TD="align: right"]1.0463[/TD]
[TD="align: right"]1.0432[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/28/2016[/TD]
[TD="align: right"]1.048[/TD]
[TD="align: right"]1.0372[/TD]
[TD][/TD]
[TD="align: right"]1.0372[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/29/2016[/TD]
[TD="align: right"]1.0493[/TD]
[TD="align: right"]1.0408[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/30/2016[/TD]
[TD="align: right"]1.0649[/TD]
[TD="align: right"]1.0484[/TD]
[TD="align: right"]1.0649[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/2/2017[/TD]
[TD="align: right"]1.0526[/TD]
[TD="align: right"]1.045[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/3/2017[/TD]
[TD="align: right"]1.049[/TD]
[TD="align: right"]1.034[/TD]
[TD][/TD]
[TD="align: right"]1.034[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/4/2017[/TD]
[TD="align: right"]1.0499[/TD]
[TD="align: right"]1.039[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/5/2017[/TD]
[TD="align: right"]1.0614[/TD]
[TD="align: right"]1.0481[/TD]
[TD="align: right"]1.0614[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/6/2017[/TD]
[TD="align: right"]1.0612[/TD]
[TD="align: right"]1.0524[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/9/2017[/TD]
[TD="align: right"]1.0582[/TD]
[TD="align: right"]1.0511[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/10/2017[/TD]
[TD="align: right"]1.0627[/TD]
[TD="align: right"]1.0551[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/11/2017[/TD]
[TD="align: right"]1.0622[/TD]
[TD="align: right"]1.0454[/TD]
[TD][/TD]
[TD="align: right"]1.0454[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/12/2017[/TD]
[TD="align: right"]1.0684[/TD]
[TD="align: right"]1.0572[/TD]
[TD="align: right"]1.0684[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/13/2017[/TD]
[TD="align: right"]1.0671[/TD]
[TD="align: right"]1.0596[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/16/2017[/TD]
[TD="align: right"]1.0636[/TD]
[TD="align: right"]1.058[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/17/2017[/TD]
[TD="align: right"]1.0719[/TD]
[TD="align: right"]1.0595[/TD]
[TD="align: right"]1.0719[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/18/2017[/TD]
[TD="align: right"]1.0714[/TD]
[TD="align: right"]1.0628[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/19/2017[/TD]
[TD="align: right"]1.0676[/TD]
[TD="align: right"]1.0589[/TD]
[TD][/TD]
[TD="align: right"]1.0589[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/20/2017[/TD]
[TD="align: right"]1.0709[/TD]
[TD="align: right"]1.0625[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/23/2017[/TD]
[TD="align: right"]1.0769[/TD]
[TD="align: right"]1.0694[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/24/2017[/TD]
[TD="align: right"]1.0774[/TD]
[TD="align: right"]1.072[/TD]
[TD="align: right"]1.0774[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/25/2017[/TD]
[TD="align: right"]1.0769[/TD]
[TD="align: right"]1.0711[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/26/2017[/TD]
[TD="align: right"]1.0765[/TD]
[TD="align: right"]1.0658[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/27/2017[/TD]
[TD="align: right"]1.0725[/TD]
[TD="align: right"]1.0658[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/30/2017[/TD]
[TD="align: right"]1.074[/TD]
[TD="align: right"]1.062[/TD]
[TD][/TD]
[TD="align: right"]1.062[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/31/2017[/TD]
[TD="align: right"]1.0812[/TD]
[TD="align: right"]1.0684[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/1/2017[/TD]
[TD="align: right"]1.0807[/TD]
[TD="align: right"]1.0732[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/2/2017[/TD]
[TD="align: right"]1.0828[/TD]
[TD="align: right"]1.0756[/TD]
[TD="align: right"]1.0828[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/3/2017[/TD]
[TD="align: right"]1.0797[/TD]
[TD="align: right"]1.0716[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/6/2017[/TD]
[TD="align: right"]1.0797[/TD]
[TD="align: right"]1.0706[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/7/2017[/TD]
[TD="align: right"]1.0749[/TD]
[TD="align: right"]1.0656[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/8/2017[/TD]
[TD="align: right"]1.0711[/TD]
[TD="align: right"]1.064[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/9/2017[/TD]
[TD="align: right"]1.0709[/TD]
[TD="align: right"]1.0651[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/10/2017[/TD]
[TD="align: right"]1.0667[/TD]
[TD="align: right"]1.0607[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/13/2017[/TD]
[TD="align: right"]1.0658[/TD]
[TD="align: right"]1.0592[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/14/2017[/TD]
[TD="align: right"]1.0633[/TD]
[TD="align: right"]1.0561[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/15/2017[/TD]
[TD="align: right"]1.0609[/TD]
[TD="align: right"]1.0521[/TD]
[TD][/TD]
[TD="align: right"]1.0521[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/16/2017[/TD]
[TD="align: right"]1.0679[/TD]
[TD="align: right"]1.059[/TD]
[TD="align: right"]1.0679[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/17/2017[/TD]
[TD="align: right"]1.0676[/TD]
[TD="align: right"]1.0605[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/20/2017[/TD]
[TD="align: right"]1.0633[/TD]
[TD="align: right"]1.0603[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/21/2017[/TD]
[TD="align: right"]1.0615[/TD]
[TD="align: right"]1.0526[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/22/2017[/TD]
[TD="align: right"]1.0574[/TD]
[TD="align: right"]1.0493[/TD]
[TD][/TD]
[TD="align: right"]1.0493[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/23/2017[/TD]
[TD="align: right"]1.0595[/TD]
[TD="align: right"]1.0537[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/24/2017[/TD]
[TD="align: right"]1.0618[/TD]
[TD="align: right"]1.0557[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/27/2017[/TD]
[TD="align: right"]1.063[/TD]
[TD="align: right"]1.0552[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/28/2017[/TD]
[TD="align: right"]1.063[/TD]
[TD="align: right"]1.0571[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/1/2017[/TD]
[TD="align: right"]1.0588[/TD]
[TD="align: right"]1.0515[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col span="5"></colgroup>[/TABLE]

illustrate-png.39226
 
Sort by date Sort by votes
Your original post was auto moderated, posting again will not release that, it requires a moderator and you should have received a system email to inform you of that
 
Upvote 0
You must log in or register to reply here.

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