How can I generate an array of numbers?

**tommade** · 04-09-2016, 12:21 PM

I am trying to predict possible future exam/test scores based on past data.

The 2 items I would like to use as known variables are (1) past test scores and (2) trending direction of the past 3 scores.

So for example: if Sally has her past 10 scores as: 70, 75, 75, 75, 80, 70, 60, 70, 80, 85 ...... what would be her next 3 scores?
I would like to note that her last 3 scores are trending up, which I would like to factor in as a variable to predict future scores, as well as using all those past 10 scores (maybe average of them).

When I look up functions such as TREND (though I didn't try to use this myself), it looks as though it returns one specific number (for example in my case it may say the scores will be 81, 82 and 83 for her next 3 test scores). Is it possible, however, to return multiple arrays of test scores rather than everything averaged to return one number? I would rather see maybe the top 5 or 10 likely predicted scores for her 11th test.

Hopefully I am explaining this correctly. Is there a way I can calculate and predict the future like this?
Sally.xlsx

**mehmetcik** · 04-09-2016, 02:57 PM

There is a standard formula for a straight line between a list of data points.

You should be using that.

https://support.office.com/en-us/art...a-fa7abf772b6d

**tommade** · 04-09-2016, 03:04 PM

Originally Posted by mehmetcik

There is a standard formula for a straight line between a list of data points.

You should be using that.

https://support.office.com/en-us/art...a-fa7abf772b6d

Thank you, but will this not offer just 1 predicted test score for future tests? I was hoping to gather the array of numbers as potential scores, rather than the average of those array of numbers used in making a straight line.
is that possible?

**mehmetcik** · 04-10-2016, 05:00 PM

Ok I have created a sample for you

Right click on the "Comments" sheet name at the bottom of excel and select view code to edit the code

Please Login or Register  to view this content.

A2 and B2 contain an array formula that calculates the Gradiant and the Y intercept.

I have put a formula in cell E1 which is a predicted result based on the results in C1 and D1.

Type an actual result in cell E1 and see what happens.