Sum Array calculation error

**AaronMS** · 05-07-2010, 11:31 AM

I'm teaching myself arrays, and I've come across a strange phenomena when using a =sum(row array.

Assume that the numbers 1-10 are entered into cells A1:A10. If I write an array =sum(row(A1:A10)) then the formula returns 55. However, if I enter that same data into cells A2:A11 and adjust the array accordingly, the answer returned is 65. It seems that every row that I move the numbers down adds 10 to the value.

**daddylonglegs** · 05-07-2010, 11:39 AM

That formula isn't summing the cell contents, it's summing the numbers generated by the row function so for

=sum(row(A1:A10))

you are summing

{1;2;3;4;5;6;7;8;9;10} = 55

but for

=sum(row(A2:A11))

you are summing

{2;3;4;5;6;7;8;9;10;11}=65

the contents of those cells is irrelevant

**day92** · 05-07-2010, 11:39 AM

Im not seeing that when I do it. Do you have an example?

**Dulanic** · 05-07-2010, 11:41 AM

Press F1 when using the ROW function to see how ROW works. He is correct, ROW will simply give the value of the row. Try changing the values in the cells in A1:A10 and you will see your formula will not provide any different results.

**contaminated** · 05-07-2010, 11:43 AM

In {=SUM(ROW(A1:A10))} ROW function returns row number for each selected cell ie this array
={SUM({1,2,3,4,5,6,7,8,9,10})}
SUM of these formula would be 55.

When you enter same data dor A2:A11 ROW function creates belowstated array within SUM.
={SUM({2,3,4,5,6,7,8,9,10,11})}
SUM of these formula would be 55.

For the future, try to Evaluate formula. It helps to understand it more easily (Tools>Formula Auditing> Evaluate Formula.)

HTH

**AaronMS** · 05-07-2010, 11:48 AM

Very interesting. Thank you, that helps.

EDIT: I have another question though. This one is more complicated.

I am creating a formula that will find the average of a set of values in a column, where the specific values that I need are entered every 7 cells. I can use the OFFSET function to do this once with AVERAGE(OFFSET(A1,7,0,1,1)), but I need to have it go on continually in an automated fashion. Any suggestions?