Hi,
I'm trying to define a range to calculate a MIN function using a different cell value. Here's where I'm at:
In excel, the function would be: =MIN(A1:INDIRECT(CONCATENATE("A",B2))), but in numbers, I can't seem to get the range to start in the right place.
In the example above, the result in C2 should be 5, but it is starting the range at B1 instead of B2 (the result of E2 when changed to either 1 or 2 both = 9)
Can anyone help?
Many thanks in advance.
One way is to use OFFSET, like this:
In D2:
=SUM(OFFSET(B$1,0,0,C2+1,1))
Substitute ; for , in the formula if your region uses , as a decimal separator.
The basic form for OFFSET is this:
=OFFSET(base,row-offset,column-offset,rows,columns)
It starts at B1 as the base, moves down 0 rows, moves over 0 columns, includes C2+1 rows, and 1 column)
You could also use:
=SUM(OFFSET(B$1,1,0,C2,1))
SG
One way is to use OFFSET, like this:
In D2:
=SUM(OFFSET(B$1,0,0,C2+1,1))
Substitute ; for , in the formula if your region uses , as a decimal separator.
The basic form for OFFSET is this:
=OFFSET(base,row-offset,column-offset,rows,columns)
It starts at B1 as the base, moves down 0 rows, moves over 0 columns, includes C2+1 rows, and 1 column)
You could also use:
=SUM(OFFSET(B$1,1,0,C2,1))
SG
Thanks for your reply.
I want the range of cells calculated to change based on E2, ie., if E2 is 5, the range of cells calculated will be B2-B6. If E2 is 4, the range of cells calculated will be B2-B5.
What is it exactly that you are trying to do? Surely there is a simpler way than a complicated formula like that.
SG
Thanks so much!
Define a range based on cell value
