Search code examples
excelexcel-formulanumbersexcel-2010excel-2007

How to make sums from a cell in another column?

I'm looking for a issue to make automatically sums of 10 cells from the raw of the high value of another column . I explain the job :

I have to enter data in a table.

2 columns and 113 rows.

the A column is data export. And B is sum of 10 cells from the high A column value. The sum is every 10 cells.

On the screenshots the yellow cell is the high value. I have to make sums every 10 cells (grey cells) from the yellow one.

enter image description here

enter image description here

7 sums are report on another table :

enter image description here

For the moment I have to report all values manually. I'm looking for make and report sums automatically. Is it possible?

The problem is, according to the data report in the A column, the high value (yellow cell) is not at the same place. So the values for the sums are not at the same place too...

Do you have an idea?

Thank you for your help !

Solution

  • REVISED:

    Let me know if this is something you are looking for:

    The updated formula you enter from cell B2 is:

    =IF(OR(ROW(E4)<MATCH(LARGE($E$4:$E$57,1),$E$4:$E$57,0)+ROW($F$3)+10,MOD(ROW(E4)‌​-(MATCH(LARGE($E$4:$E$57,1),$E$4:$E$57,0)+ROW($F$3)+10),10)>0),"",SUM(OFFSET($E$3‌​,(MATCH(LARGE($E$4:$E$57,1),$E$4:$E$57,0)+1)+((ROW(E4)-(MATCH(LARGE($E$4:$E$57,1)‌​,$E$4:$E$57,0)+ROW($F$3)+10))),,10)))

    This should work even you add/remove your top rows. Also, you just need to change that 10 to any number sums you want to do and it should still work.

    Basically I have to first use LARGE and MATCH to find out the largest number in the column, and then use OFFSET to find every 10 rows to sum up. Let me know if this is what you are looking for.