How to get LOGEST function to read dynamic data going up

[Mishtik]

Hi everyone,

This is my first ever post and probably not the last. I hope someone can help me with a dilemma I'm facing at the moment.

How do I get the LOGEST function (or any forecasting functions) to read dynamic data going up?
That is; the forecasting methods in general read the given data from top to bottom (given only known x's and y's for, e.g.):
09.Jan.2013 $22.29
08.Jan.2013 $22.07
07.Jan.2013 $20.12
06.Jan.2013 $21.95
05.Jan.2013 $21.66

Here I am looking to predict the next day's forecast - 10.Jan.2013 $??,... but when I enter a forecasting formula it gives me a figure that resembles more like a value for the 04.Jan.2013's value (after highlighting the cells for known y's - stock prices).

I'm using data from the stock market which has data descending according to date.
My question to you fine people is; how do I get the formula to read upwards instead of down for the next value?

[MrShorty]

What are you using for "known_x" in the regression formula?

If I read between the lines, it appears to me that you might be leaving this argument blank. If you leave it blank, Excel will assume count numbers (1,2,3,...) for known_x. You should be able to get it to extrapolate appropriately by recognizing this and using an appropriate value for new_x (using argument names like in the FORECAST() or TREND() functions) in your forecasting function. This could be a problem for logarithmic type regressions (like LOGEST() and GROWTH()), because, in this case, that would make 10 Jan correspond to a new_x value of 0, and logarithmic functions are going to fail when x=0.

I almost never leave known_x blank, because I don't like to leave this argument to Excel's whims. If you are not providing your regression a known_x argument, then I would come up with a meaningful known_x and use that. Then you will know what value to use for new_x to correctly extrapolate to 10 Jan

[Mishtik]

Thanks so much for your assistance MrShorty
So I can put the dates as above for known_x's, but how do I get it to forecast a value for 10 Jan, or even 15 Jan from the given values?

[MrShorty]

As explained here http://office.microsoft.com/en-us/ex...in=HA102752955 the LOGEST() function fits your data to an equation like y=bm^x. You give it known_y's and known_x's and it determines m and b. With values for m and b, you can now create a formula where you give it a new value for x and calculate a new value for y. =b*m^new_x where b, m, and new_x can be references to cells containing the appropriate value.

[Mishtik]

I understand now. Thanks so much MrShorty :D

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.