Difference (either positive or negative) between two numbers

Question

I am a teacher who is creating a Google sheet for students. I will be recording monthly scores in individual cells. I need a formula that would show either a gain or loss when a student's score changes from one month to the next.

For example, if a score in cell C14 on 10/1 was 720 and a score in cell D15 on 11/1 was 700, I would need a separate cell (that shows gains and losses) to say "-20" because this student's score decreased by 20 points.

However, if a score in cell C14 on 10/1 was 720 and and a score in cell D15 on 11/1 was 750, I would need the cell (that shows gains and loses) to say either "30" or "+30"

Can anyone help?

Answer 1

All you need to do is use simple subtraction with the proper points of reference. For the following table the letters represent:

• X, Y, Z are Weeks
• A, B are students
• F is an abstraction of the formula
• DA, DB is the difference for the previous week for each user.

Table example:

   X   Y    Z
A  10  10   20
B  10  20   10
---------------
F  0   Y-X  Z-Y
DA 0   0     10
DB 0   10   -10

All you need to do is deduct last weeks score from the previous weeks score to get the difference of the previous week. In other words it's Week (N) - Week (N-1) to get the score change Week (N).