up vote 1 down vote favorite
share [g+] share [fb]

I have performance data for 3 years for about 50K employees. Each employee has 3 ratings associated with them. I would like to create an equation that will predict (or try to predict) the future value of that employee's rating given the historical ratings I have available.

My problem is that I am not terribly familiar with complex regression analysis and I'm not quite sure how to set this up in Excel.

The data are laid out as:

EE ID, Rating 1, Rating 2, Rating 3

The problem (as I see it) is that Excel expects a Y value as input and I technically don't have any valid Y values. My Y value equivalent would be the EE ID, which does not have any relationship to the X values.

Any idea how I can do this?

link|improve this question
0% accept rate
So if EEID1 got ratings of 100, 200, and 300 you'd want it to tell you the next most likely number by calculating the average slope of the increases? Your Ys would be the time periods, yearly – Raystafarian Feb 14 at 17:08
feedback

1 Answer

up vote 0 down vote

You could use =forecast by going =FORECAST(Y4,R1:R3,Y1:Y3) this is, you would assume that each of the ratings (R1:R3) are associated with each year (Y1:Y3) and you want the next Rating for the next Year (Y4)
you could do nearly the same thing for Linest, Trend, Slope, Steyx, Longest, and Growth.

EDIT

If this was your data

Year   2000 2001 2002
Rating 100  100  150

For each EEID and you wanted 2003's forecasted rating, your formula would be =forecast(2003,B2:B4,A2:A4)

link|improve this answer
Could you please elaborate a bit more on this answer? Would my setup be something like EE ID | 2012 | Rating 1 | Rating 2 | Rating 3 | 2009 | 2010 | 2011? – Jay Feb 15 at 19:43
@Jay added some explanation – Raystafarian Feb 15 at 19:49
Thank you - I will try this! – Jay Feb 15 at 19:51
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.