Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 1 down vote favorite

In cell A1 I can't write =SUM(A:A) (Circular reference). If I write =SUM(A2:A15) when I insert A16 the result won't be affected. How to do this?

share|improve this question

2 Answers

up vote 3 down vote accepted

To sum up all cells in the entire column except the one cell in the same column that you put the formula in (namely A1), use

SUM(A2:A1048576)

for MS Office 2007 and later, or use

SUM(A2:A65536)

for MS Office 2003 and earlier.

share|improve this answer
Will that work for MEAN too? Will it consider the blank lines? – Jader Dias Jul 29 '11 at 13:29
1  
I think you mean AVERAGE() function. Yes, it works for it as well. It does not evaluate the empty cells. – Mehper C. Palavuzlar Jul 29 '11 at 13:39
Yes, Average. Mean is the Octave name for the same method. – Jader Dias Jul 29 '11 at 13:53
up vote 3 down vote

You could move the =SUM(A:A) to another column or you could set A1 as =SUM(A2:A20000).

share|improve this answer
+1. Note that it is considered bad form to have the sum of an entire column in that same column, so the first solution (move the sum to another column) would be your best bet. – Breakthrough Jul 29 '11 at 13:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.