Variable Cell Range Reference

[Guest]

How do I refer to a range of values when one component is a variable? For
instance, lets say I want the MAX value of the cells from cell A5 to A(6+x)
where x is a variable ... if x=4 then I would be finding the MAX value in the
cells from A5 to A10. How do I structure this formula in Excel?

 

[Guest]

one way:

=MAX(OFFSET($A$5,0,0,$B$1,1))

B1 contains number of rows to be included: for your example it would be 6
(A5 to A10 inclusive)

 

[Rick Rothstein \(MVP - VB\)]

How do I refer to a range of values when one component is a variable? For

instance, lets say I want the MAX value of the cells from cell A5 to
A(6+x)
where x is a variable ... if x=4 then I would be finding the MAX value in
the
cells from A5 to A10. How do I structure this formula in Excel?

This should work...

=MAX(INDIRECT("A5:A"&(6+X1)))

where I stored your "variable" in cell X1.

Rick

 

[T. Valko]

A non-volatile approach.

from cell A5 to A(6+x)

If you want to use that logic:

=MAX(A5:INDEX(A:A,6+B1))

Where B1 = your variable

Or, you could change this logic A(6+x) and instead use the actual row number
you're interested in:

So, if B1 = 4, then 6+B1 = 10

Instead, make B1 = 10

=MAX(A5:INDEX(A:A,B1))

Note that using this approach if B1 is empty the formula will return the max
from the entire column A.