Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jun 13 '13 at 18:23

This question came from our site for professional and enthusiast programmers.

    
I did not read the q carefully but maybe sumif is what you want –  Johan Larsson Jun 12 '13 at 21:53
    
I think sumif could be involved, but I am not sure how. –  user1029296 Jun 12 '13 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 Jun 12 '13 at 22:07

1 Answer 1

up vote 1 down vote accepted

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, ...)

share|improve this answer
    
Thank you SO MUCH. You rock. –  user1029296 Jun 13 '13 at 2:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.