Sum Indirect Volatile error

[Grimm127]

Hi All,

I am having trouble with the following formula. I keep getting "Volatile" when I look at the fx box.
Cell A103 = to 51

=SUM(INDIRECT("$x$8:$x$"&A103))

So my result should sum range (X8:X51) which currently has a total of 35.

Is there any way to fix this?

Thanks!

 

[Special-K99]

What result are you getting?

 

[Andrew Poulsom]

INDIRECT is a volatile function. For a non-volatile version:

=SUM($X$8:INDEX(X:X,A103))

 

[Rick Rothstein]

I am having trouble with the following formula. I keep getting "Volatile" when I look at the fx box.
Cell A103 = to 51

=SUM(INDIRECT("$x$8:$x$"&A103))

So my result should sum range (X8:X51) which currently has a total of 35.

This version of your formula would not be Volatile...

=SUM(INDEX(X:X,8):INDEX(X:X,A103))

 

[Grimm127]

This version of your formula would not be Volatile...

=SUM(INDEX(X:X,8):INDEX(X:X,A103))

Works Perfectly! I really appreciate </SPAN></SPAN>your reply. Thank you!

 

[Rick Rothstein]

Works Perfectly! I really appreciate your reply. Thank you!

Andrews formula would also work for you and is slightly simpler having one less function call. The difference between our formulas would show up if you decided to insert any rows before Row 8... if you did that, the $X$8 in Andrews formula would update to $X$9 where as the Index(X:X,8) in my formula (which is the equivalent to his $X$8) would not change... it would depend on why you inserted the row and what you planned to do next which formula you should use. If you do not plan on inserting any rows before Row 8, then I would probably use Andrew's formula instead of mine.