Variable Cell Range Reference
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? -- Ken |
Variable Cell Range Reference
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) "Ken" wrote: 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? -- Ken |
Variable Cell Range Reference
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 |
Variable Cell Range Reference
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. -- Biff Microsoft Excel MVP "Ken" wrote in message ... 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? -- Ken |
All times are GMT +1. The time now is 03:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com