In: Statistics and Probability
What is the excel function/equation to add every ten numbers. I have a list of 100 numbers or so in a row. I want to add the first ten numbers and put the value in the row below the list. So add numbers 1 to 10, 2 to 11, 3 to 12, and so on. There has to be an easier way to do this than manually right?
We use the OFFSET Function in excel for increament.
1) if we have data in the one column, then to add the first 10 or n rows use formula.
=SUM(OFFSET($A$1,(ROW()-ROW($A$1))*10,0,10,1))
where A1 is starting cell.
10 is increament number.
if you want to result displayed at 10 th cell of column then use formula:
=IF(MOD(ROW(),10),"",SUM(OFFSET(B1,,,-10)))
(paste formula in first row & drag it.)
2) If we have data in the one rows then use function
=SUM(OFFSET($A$1,,(COLUMNS($A$3:A3)-1)*10,,10)) (A1 is the cell reference which your first value located, A3 stands for the cell where you put this formula in, and 10 is the incremental column numbers ), and then drag the fill handle over to the right cells until 0 is displayed)