calculate trend as a value?

alexllap

New Member
Joined
Jan 29, 2014
Messages
11
Hello everyone,

I'm trying to calculate the "trend" in student GPAs as a value of some sort, indicating if the overall trend is negative, positive, or flat. I've done some digging but can seem to figure out what this value would even be called. Possibly r-squared when you add a trendline to a scatter plot, but I don't know how to get that just as a formula calculation with the data I have. Here's an example of what I have:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]gpa1[/TD]
[TD]gpa2[/TD]
[TD]gpa3[/TD]
[TD]gpa4[/TD]
[TD]gpa5[/TD]
[TD]gpa6[/TD]
[/TR]
[TR]
[TD]student1[/TD]
[TD]3.8[/TD]
[TD]3.0[/TD]
[TD]3.4[/TD]
[TD]3.2[/TD]
[TD]3.0[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]student2[/TD]
[TD]3.0[/TD]
[TD]3.1[/TD]
[TD]2.9[/TD]
[TD]3.0[/TD]
[TD]3.0[/TD]
[TD]3.0[/TD]
[/TR]
</tbody>[/TABLE]

So for example, student2's GPA remains relatively flat, so I would think a value of their trend would be close to 0. Whereas a student showing upward trend would have a value that's positive, and for a downward trend, a negative value.

Another tricky thing about this is that the student's most recent quarterly GPA is the "GPA1" column. If there's a way to account for this, that'd be awesome, but I can obviously reorder these so that the most recent GPA is in the furthest right-hand column. Any help is appreciated!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
I'm trying to calculate [....] if the overall trend is negative, positive, or flat. [....T]he student's most recent quarterly GPA is the "GPA1" column

What you describe can be determined by the sign of the slope, if you assume a linear trendline.

For student2, you might use the formula =SIGN(SLOPE(B3:G3,{6,5,4,3,2,1})). But note that even student2 is trending up (positive), unless you choose to round the slope to 1 or fewer decimal places.

For student1, you need to avoid referencing the #N/A error. You might use the formula =SIGN(SLOPE(B2:F2,{6,5,4,3,2})).

Those formulas can be generalized and made to ignore #N/A errors. But first we must learn to walk before we can run.

FYI, R^2 tells you how well the data fits the linear trendline, not the direction of the trendline. In fact, R^2 is always non-negative.
 
Upvote 0
Excellent! So if I also wanted the degree to which a trend was either positive or negative, could I just use =SLOPE(B2:F2,{6,5,4,3,2}) ?

Is there an easy way to avoid referencing the #N/A rather than manually adjusting each formula? I have about 200 rows and each one can be different in terms of having #N/A in any of these columns.
 
Upvote 0
It looks like if I just remove all the instances of "#N/A" it seems to work with blank cells. Thank you for your help!
 
Upvote 0
So if I also wanted the degree to which a trend was either positive or negative, could I just use =SLOPE(B2:F2,{6,5,4,3,2}) ?

Yes, I would. The slope is the rate of change per unit distance.

Is there an easy way to avoid referencing the #N/A

Even though you solved the problem by eliminating the #N/A error (good choice, IMHO), the following is a more general formula that adapts to any number of GPAs. Array-enter (press ctrl+shift+Enter instead of just Enter) the following:

=SLOPE(IF(ISNUMBER(B2:G2), B2:G2), COLUMN(G2)-COLUMN(B2:G2))

The second parameter is equivalent to {5,4,3,2,1,0}, not {6,5,4,3,2,1}. For the latter, add "+1". But that should not make a difference for a linear trendline.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

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