1
votes

i'm struggling with an excel formula:

NEED: I need a formula to sum every 5 rows AND place the value on the respective row. (see table below)

Here's what i have so far but it isn't quite there:

=SUM(OFFSET($A$2,(ROW()-ROW($B$2))*5,,5,))

    | A   |  B | C
-----------------------
1   | 5   | 37 | 37  
2   | 10  | 24 | 37
3   | 7   | 17 | 37
4   | 7   | 0  | 37
5   | 8   | 0  | 37  
6   | 1   | 0  | 24
7   | 3   | 0  | 24  
8   | 5   | 0  | 24  
9   | 5   | 0  | 24
10  | 10  | 0  | 24
11  | 5   | 0  | 17
12  | 1   | 0  | 17
13  | 8   | 0  | 17
14  | 2   | 0  | 17
15  | 1   | 0  | 17

COLUMN "A" = VALUES

COLUMN "B" = CURRENT OUTPUT (w/Formula Above)

COLUMN "C" = DESIRED OUTPUT

Thanks for your assistance.

3

3 Answers

4
votes

Enter this formula in B1:

 =SUM(OFFSET($A$1,5*INT((ROW()-1)/5),,5))

and drag/copy it down.

Tested on ~10K rows of random numbers, it works reasonable.

enter image description here

2
votes

Another way using INDEX instead of OFFSET

=SUM(INDEX(A:A,FLOOR(ROW()-1,5)+1):INDEX(A:A,FLOOR(ROW()-1,5)+5))

enter image description here

Explanation

THE FLOOR function takes the value of the first argument and rounds it down to the nearest multiple of the second argument. In the first INDEX statement, for rows 1-5, in the FLOOR statement one is subtracted from the row number to give 0-4. The FLOOR statement rounds all these values down to zero, so after adding one you get a value of 1 for each of the first 5 rows.

The second INDEX and FLOOR statements work the same way, except that you add 5 at the end to give a value of 5 for the first 5 rows.

INDEX returns a reference, so it is valid to put INDEX(A:A,1):INDEX(A:A,5) to get the first 5 cells of column A, then you can just SUM them.

For rows 6-10, the FLOOR statement yields 5 in the same way so after adding 1 and 5 respectively you get cells 6-10 of column A and so on.

Row()     Row()-1    FLOOR(Row()-1)   FLOOR(Row()-1)+1   Floor(Row()-1)+5
1         0          0                1                  5
2         1          0                1                  5
3         2          0                1                  5
4         3          0                1                  5
5         4          0                1                  5
6         5          5                6                  10
7         6          5                6                  10
8         7          5                6                  10
9         8          5                6                  10
10        9          5                6                  10
1
votes

This is cheating, but in B5 enter:

=IF(MOD(ROW(),5)=0,SUM(A1:A5),B6)

then copy this cell both upwards and downwards:

enter image description here

The formulas in B1 through B4 are invalid, but yield the correct result.