Formula Length

  • Thread starter Thread starter David Fixemer
  • Start date Start date
D

David Fixemer

I had a Grand total formula which read like:

cell.value = "=Sum(" & myrange & ")"

which generated the following cell text.

=Sum(A1,A11,A21,...)

However, this is very limited in the number of cells it
can contain.

I've now updated it to the following format.

=Sum(A1 + A11 + A21 + ....)

I believe this format is much larger than the previous
format, however, still does have a limit? Is this belief
correct?

Is there anyway around this limit other than to compute
the total in VBA and write the value to the cell?

David
 
Formulas are restricted to a length of 1024 characters when the formula is
expressed in R1C1 format.

(in otherwords, the length of the formula is as measured when it is
expressed in R1C1 format).

=Sum(a1+a2) is redundant.

either
=Sum(a1,A2)

or
=A1+A2

If there are no numbers in the contiguous range that you want to add, then
you could just use

=Sum(A1:A2000)

non numeric values will be ignored (except errors).

if you have a recuring interval such as you illustrate (1,11,21 . . .

you could use an array formula like this:

=SUM(IF(MOD(ROW(A1:A50000),10)=1,A1:A50000))

entered with ctrl+Shift+enter rather than enter
 
Back
Top