Calculating Trends

[Orangepie]

Hi

I have a problem. I have 10 different grades (1-10) that can be given to my students. Each person is given a 3 grades and I need to find the trend. For eg, a student given 1, 2, 3 I need excel to return grade 2. I have different combinations and is there a way for excel to do so?

[rwgrietveld]

OrangePie,

There are general formulas for that result in a linear function y=mX+b where m is the slope and b id the offset.

I've added a picture for the formula's to find m and b.

I've made names, 3 values in A1:A3:
n=COUNTA(Sheet1!$A$1:$A$3)
sumD=SUM(Sheet1!$A$1:$A$3)
sumi=SUMPRODUCT(ROW(Sheet1!$A$1:$A$3))
sumi_2=SUMPRODUCT(ROW(Sheet1!$A$1:$A$3))^2
sumi2=SUMPRODUCT(ROW(Sheet1!$A$1:$A$3),ROW(Sheet1!$A$1:$A$3))
sumiD=SUMPRODUCT(ROW(Sheet1!$A$1:$A$3),Sheet1!$A$1:$A$3)

m=(n*sumiD-sumi*sumD)/(n*sumi2-sumi_2)
b=(sumD*sumi2-sumi*sumiD)/(n*sumi2-sumi_2)

Hope this helps

The simpe way is making a graph and let excel print the trend in the graph. Add Trendline, Options

[Orangepie]

Thanks rwgrietveld!!! Not too sure if I understand the formulas completed. Tried it out and realised that mine is not exactly a linear relationship. For example, if the student gets grade 1,3,3 the return should be 2. Another example, if the student gets 4, 2, 3 the return should be 3. I guess my question really is if there is a set of criteria to judge the 3 grades, how then can it be computed into excel. Tried nested if, but that one only allows 7 combinations.... any advice? Thanks!

[rwgrietveld]

OrangePie,

I think you are wrong about this:

if the student gets 4, 2, 3 the return should be 3.

This is a negative trend. I've added the sheet to show you. These "trend" formula's are universal. You are calculating the average. Please try some other values in my sheet.