dynamic offsets

  • Thread starter Thread starter lost
  • Start date Start date
L

lost

The data below is used in the descrption of the problem



A B C D E

1 34 7 95 0 0

2 5 56 7 94 0

3 342 66 67 219 0

4 1 2 3 4 0





A little explanation first

I am trying to create a totally dynamic table

Row 4 is automatically incremented by one each time the sum of the column is
above 0. This is the reason E4 is 0. I am using this to detect if data in
entered into the table. I.e the real data in this example is rows 1-3



I am trying to detect the lowest number in completed row 3 and then display
other cells in the row

i.e in the example the lowest number in row 3 is 66, so I want to be able to
show cells B2 & B1



I so far have the following



=(MIN(A3:(OFFSET($A3,0,MAX(A4:IV4)))))



which appears to be automatically converted to





=(MIN(A3:(OFFSET($A3,0,MAX(4:4)))))



This part work fine and shows the contents of B3 (in this example)



I tried the following which failed



=OFFSET((MIN(A3:(OFFSET($A3,0,MAX(4:4))))),-1,0)

Which I was hoping would show the contents of B2



I have also tried the MATCH function but again this only gives me an offset.

Is it possible to return a cell ID some how, or another way to get this to
work



Thanks in advance
 
Try...

=INDEX(A1:E1,MATCH(MIN(A3:E3),A3:E3,0))

and

=INDEX(A2:E2,MATCH(MIN(A3:E3),A3:E3,0))

Adjust the range accordingly.

Hope this helps!
 
lost said:
I am trying to detect the lowest number in completed row 3 and then display
other cells in the row

i.e in the example the lowest number in row 3 is 66, so I want to be able
to show cells B2 & B1
Click to expand...

That is, B3 is 66, the lowest value in row 3, so show the rows above it in
the same column.
I so far have the following

=(MIN(A3:(OFFSET($A3,0,MAX(A4:IV4)))))

which appears to be automatically converted to

=(MIN(A3:(OFFSET($A3,0,MAX(4:4)))))
Click to expand...

This isn't ideal. What you want is the range in row 3 from col A through the
rightmost column in which row 4 is positive. Don't use OFFSET. Use

=MIN(A3:INDEX(3:3,MAX(4:4)))
This part work fine and shows the contents of B3 (in this example)
Click to expand...

Wait until you have 256 columns, in which case IV4 would be 256, and your
OFFSET call would return #REF!. Your formula may work, but it's flawed.
I tried the following which failed

=OFFSET((MIN(A3:(OFFSET($A3,0,MAX(4:4))))),-1,0)
Click to expand...
....

This fails because the first argument to OFFSET must *ALWAYS* be a range
reference. You need to derive the column index using

MATCH(MIN(A3:INDEX(3:3,MAX(4:4))),3:3,0)

Then pull the portion of the column above it with

=INDEX($1:$65536,1,MATCH(MIN(A3:INDEX(3:3,MAX(4:4))),3:3,0))
:INDEX($1:$65536,2,MATCH(MIN(A3:INDEX(3:3,MAX(4:4))),3:3,0))

This uses no volatile functions. If you won't have many of these formulas,
then a few volatile functions like OFFSET won't be much of a performance
drag, so a briefer alternative would be

=OFFSET($A$1,0,MATCH(MIN(A3:INDEX(3:3,MAX(4:4))),3:3,0),2,1)

Both resolve to references to B1:B2.
 
Back
Top