dynamic offsets

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(A3OFFSET($A3,0,MAX(A4:IV4)))))



which appears to be automatically converted to





=(MIN(A3OFFSET($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(A3OFFSET($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!

In article <[email protected]>,
"lost" <[email protected]> wrote:

> 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(A3OFFSET($A3,0,MAX(A4:IV4)))))
>
>
>
> which appears to be automatically converted to
>
>
>
>
>
> =(MIN(A3OFFSET($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(A3OFFSET($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

thanks , but how does that solve my problem ?? can you please explain

"Domenic" <[email protected]> wrote in message
news:[email protected]...
> 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!
>
> In article <[email protected]>,
> "lost" <[email protected]> wrote:
>
>> 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(A3OFFSET($A3,0,MAX(A4:IV4)))))
>>
>>
>>
>> which appears to be automatically converted to
>>
>>
>>
>>
>>
>> =(MIN(A3OFFSET($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(A3OFFSET($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

"lost" <[email protected]> wrote...
....
>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

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(A3OFFSET($A3,0,MAX(A4:IV4)))))
>
>which appears to be automatically converted to
>
>=(MIN(A3OFFSET($A3,0,MAX(4:4)))))

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)

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(A3OFFSET($A3,0,MAX(4:4))))),-1,0)
....

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.