+ Reply to Thread
Results 1 to 8 of 8

Summing up every 125 rows

  1. #1
    Registered User
    Join Date
    10-30-2018
    Location
    Ramat Gan, Israel
    MS-Off Ver
    Office 365
    Posts
    15

    Cool Summing up every 125 rows

    Edit:
    I uploaded now my Excel sheet so that you won't have to trouble yourselves.

    - - - - - - -

    Hi there,

    I'm helping to run a study and my worksheet contains about 130,000 rows...it's huge (at least for me)!
    It's presented in increments of 8 milliseconds and I would like to sum up every 125 rows so that it presents a second, but I would like to do it for every subject (every subject is presented as his or her last 4 ID digits, so there is subject 6492 and there is subject 3729 for exmaple and so on). So as I said, I want to sum up every 125 rows of every subject, so that if a subject have, for example, 126 rows, excel won't include the last row for this subject but for the next one (to sum the rows up until the subject number changes).

    I did it for the first 125 rows but I don't know how to do it over and over taking into account that a single subject's data may not divide precisely in 125.

    Then I have to extract the first second of the all of the subjects to one sheet, the second one on another sheet and so on, but I guess there it gets more complicated so even if you help me find the first step I would be thrilled.

    Thanks a lot in advance!
    Last edited by Rakel; 10-30-2018 at 12:56 PM.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Summing up every 125 rows

    Not saying I'll answer this but posting a file is better than posting an image.
    An image cant be edited.
    EVERYONE who's willing to solve this for you has to duplicate the data, something none of us want to do.

    Post a spreadsheet not an image.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    10-30-2018
    Location
    Ramat Gan, Israel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Summing up every 125 rows

    Thanks a lot for your response!
    I uploaded my Excel sheet to the original post. This is not all of my data. I had to delete a lot of rows in order to upload it.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Summing up every 125 rows

    Where do you actually want the sums/totals to go?
    If posting code please use code tags, see here.

  5. #5
    Registered User
    Join Date
    10-30-2018
    Location
    Ramat Gan, Israel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Summing up every 125 rows

    It doesn't matter.
    I have to move the first second of all of the subjects to another sheet, she second to another sheet and so on.
    Thanks!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Summing up every 125 rows

    Perhaps the following proposal will be of help.
    A column^ of unique participants is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    A column^ calculating the number of seconds is populated using: =IF(J2="","",ROUNDUP(COUNTIFS(A$2:A$35000,J2)/125,0))
    A column^ calculating the cumulative number of seconds is populated using: =IF(J2="","",SUM(K$2:K2))
    A column displaying the participants is populated using: =INDEX(J$2:J$13,AGGREGATE(15,6,(ROW($2:$13)-1)/(L$2:L$13>=ROW(1:1)),1))
    A column^ calculating the starting reference for each second is populated using: =SUM(Q2,1)
    A column^ calculating the ending reference for each second is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The column that displays the "Sum of AOI hits in Seconds - Eyes" is populated using: =SUM(INDEX(I2:I35000,P2):INDEX(I2:I35000,Q2))
    The column that displays the "Sum of AOI hits in Seconds - Face" is populated using: =SUM(INDEX(H2:H35000,P2):INDEX(H2:H35000,Q2))
    ^Denotes columns which may be hidden for aesthetic purposes.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    10-30-2018
    Location
    Ramat Gan, Israel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Summing up every 125 rows

    Oh, so sorry, I've seen your reply only now!
    Thank you so much! I gave you a good rate ♥ ♥ ♥

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Summing up every 125 rows

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Summing every 2 rows in blocks
    By geeman86 in forum Excel General
    Replies: 4
    Last Post: 07-16-2017, 04:03 AM
  2. [SOLVED] Help with Summing Duplicates, deleting rows after summing and filling col. for unsummed
    By solidrock1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-04-2014, 09:11 AM
  3. VBA Summing across rows and columns
    By bkeeling in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-09-2012, 12:54 PM
  4. Inserting and summing rows
    By kortni in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-30-2010, 05:04 PM
  5. Removing Unwanted Rows and summing the duplicate Rows
    By Cena in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-08-2010, 07:00 AM
  6. [SOLVED] Summing different # of rows
    By new2macros in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-21-2005, 02:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1