0

I have a data set in Excel with the following columns. I am adding an example of data:

User ID / Reputation Points / Linguistic Score

1 / 5 / 3

1 / 2 / 7

1 / 3 / 2

2 / 4 / 5

2 / 4 / 7

Each line represents a answer to a question on Stack Exchange. For each answer, I have the user ID, the number of points the answer got, and a score based on some linguistic analysis I performed. My theory is that the sum of past reputation points influences current linguistic scores. What I mean is: When a user posts an answer, the sum of all past reputation points that this user had for previous answers will have some effect on his/her linguistic score for that specific answer. In order to test this hypothesis. I need to transform the data in the following way:

User ID / Reputation Points / Linguistic Score

1 / 0 / 3

1 / 5 / 7

1 / 7 / 2

2 / 0 / 5

2 / 4 / 7

In the first answer, reputation equals zero because there was no answer before. In the second answer, reputation equals 5 because that person only sent one answer before that got 5 points. On the 3rd answer, reputation equals 7 because the 1st answer got 2 points and the 2nd got 5 points. And so on for each user.

How would you advise me to do this? I have a list of 100,000 answers.

Improve this question
3
  • I did not read the q carefully but maybe sumif is what you want
    – Johan Larsson
    Commented Jun 12, 2013 at 21:53
  • I think sumif could be involved, but I am not sure how.
    – user1029296
    Commented Jun 12, 2013 at 21:55
  • If the data is sorted by userid and then by date perhaps you can sum the row above and the score on the current row if id is same for the two rows. Getting late here will try to remember to write a better answer for you tomorrow if no one has answered.
    – Johan Larsson
    Commented Jun 12, 2013 at 22:07

1 Answer 1

Reset to default
1

Assuming that User ID is in column A and Reputation Points is in column B, add another column (I'll use column D in my example). Then put the following formula into D2:

=IF(A2<>A1,0,D1+B1)

Copy this formula and paste it into all rows that contain data.

This will only work if you can ensure that the data you analyze is sorted by user and date (ealiest post of the first user in the first row, second-earliest post of the first user in the second row, ..., latest post of the first user in the nth row, earliest post of the second user in the n+1th row, ...)

Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .