Somando uma coluna na mesma coluna sem uma referência circular

Question

No Excel, você pode somar uma coluna inteira com =SUM(B:B) . Mas é claro que você não pode usar essa fórmula na coluna que deseja somar, porque você obterá uma referência circular.

Existe uma maneira fácil de somar todos os valores na coluna abaixo da célula atual, além de hacks como =SUM(B2:B65536) ?

Para referência, aqui está alguém fazendo a mesma pergunta em ask.metafilter: link

A sugestão nesse tópico de =SUM(B:B-B1) é essencialmente o que eu estou procurando, se realmente funcionasse!

    

Answer 1

No mesmo segmento que você apontou como referência, existe um usuário propondo uma UDF para resolver o problema. Pesquise SUMRANGEWITHEXCEPTION no texto.

Com cuidado, você também pode seguir a ajuda do Excel 2007:

If you want to keep the circular reference, you can enable iterative calculations but you must determine how many times the formula should recalculate. When you turn on iterative calculations without changing the values for maximum iterations or maximum change, Office Excel stops calculating after 100 iterations or after all values in the circular reference change by less than 0.001 between iterations, whichever comes first. However, you can control the maximum number of iterations and the amount of acceptable change.

1. Click the Microsoft Office Button , click Excel Options, and then click the Formulas category.
2. In the Calculation options section, select the Enable iterative calculation check box.
3. To set the maximum number of times that Office Excel will recalculate, type the number of iterations in the Maximum Iterations box. The higher the number of iterations, the more time that Excel needs to calculate a worksheet.
4. To set the maximum amount of change you will accept between calculation results, type the amount in the Maximum Change box. The smaller the number, the more accurate the result and the more time that Excel needs to calculate a worksheet.

    

Answer 2

Isso funcionará em qualquer célula da coluna B sem modificação:

=SUM(IF(ROW()=1,0,INDIRECT("$B$1:$B$"&ROW()-1,1)),
      IF(ROW()=65536,0,INDIRECT("$B$"&ROW()+1&":$B$65536")))

Nota: Isso é para versões do Excel anteriores a 2007.