Excel Problem
Budget: $10 – $30 USD
Is it possible to dynamically define a column range without using “Excel Tables” or VBA script and only using a standard function?
Example; I want the min(A:A) but only want to calculate over cells that have values which is “A2:A50”, however, if 5 rows of data is appended to Row A, the minimum function should now use “A2:A55” as its range.
A function such as “=LOOKUP(2,1/(A:A<>""),ROW(A:A))” OR “=MAX((A:A<>"")*(ROW(A:A)))” will index the last row with data but I’m unable to define that last row into the min function example above.
The formula would be equivalent to “=MIN( “A2:A” & function)” with “function” being defined as above.
This can easily be done with VBA “RANGE” function but unsure if it can be done otherwise, thank you.
Example; I want the min(A:A) but only want to calculate over cells that have values which is “A2:A50”, however, if 5 rows of data is appended to Row A, the minimum function should now use “A2:A55” as its range.
A function such as “=LOOKUP(2,1/(A:A<>""),ROW(A:A))” OR “=MAX((A:A<>"")*(ROW(A:A)))” will index the last row with data but I’m unable to define that last row into the min function example above.
The formula would be equivalent to “=MIN( “A2:A” & function)” with “function” being defined as above.
This can easily be done with VBA “RANGE” function but unsure if it can be done otherwise, thank you.