Aggregating a large number of observations

**ssotirov** · 02-20-2015, 03:40 PM

Hello, everyone!

I have around 250 000 observations and I need to sum them up so that in the first cell I have the sum of the first 390, in the second cell I have the sum of 391-781 and so on. This does not work just by writing SUM(H2:H392) and dragging because the second cell will be SUM(H3:H393), the third SUM(H4:H394) and so on, while I want them to be SUM(H391:H781), SUM(H782:H1172) and so on.

If I have to do it manually for all 250 000 cells it will take me forever. So is there a shortcut?

Thank you!

**Pepe Le Mokko** · 02-21-2015, 10:22 AM

Hi
Have you tried a Pivot Table, grouping by ranges ?

**Tony Valko** · 02-21-2015, 11:11 AM

One way...

Entered in J2 and copied down as needed:

=SUM(INDEX(H$2:H$250000,ROWS(J$2:J2)*390-389):INDEX(H$2:H$250000,ROWS(J$2:J2)*390))

**Czeslaw** · 02-21-2015, 04:21 PM

Please Login or Register  to view this content.

**ssotirov** · 02-21-2015, 04:46 PM

I used the pivot table method and it worked perfectly. Thanks!

Aggregating a large number of observations

LinkBack

Thread Tools

Rate This Thread

Display

Aggregating a large number of observations

Re: Aggregating a large number of observations

Re: Aggregating a large number of observations

Re: Aggregating a large number of observations

Re: Aggregating a large number of observations

Thread Information

Users Browsing this Thread

Similar Threads

Aggregating numbers against account number

Scoring a model on 10k observations

Formula to count a data point in the last nth number of observations in a column

Large Form Help: Handling large number of text boxes and labels

[SOLVED] How to find the top 5% observations

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions