showing the contents of dynamic cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lost - 02 Jan 2006 12:01 GMT

If I use the following spread sheet as an example

a b c

1 23 2 34

2 22 23 75

3 3 45 32

I am trying to find the value of rows 1 and 2 but only for the column where
c row 3 is the highest.

I.e. in this example as 3b is the highest how I can show the values of b1
and b2

How ever it is important to remember that all the 9 values in this spread
sheet are dynamic.

Finding the max number in row 3 is easy =Max(a3:c3)

Thanks

Reply to this Message

Max - 02 Jan 2006 12:27 GMT

One way ..

Source table assumed in A1:C3

Select a 2 cell range, say, E1:E2
Put in the formula bar, array-enter (press CTRL+SHIFT+ENTER):
=OFFSET(A1:A2,,MATCH(MAX(A3:C3),A3:C3,0)-1)
E1:E2 will return the required values

In the event of ties in the maximum value within A3:C3,
E1:E2 will return the values from the 1st matched col (from the left)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

> If I use the following spread sheet as an example
>
[quoted text clipped - 18 lines]
>
> Thanks

Reply to this Message

Max - 02 Jan 2006 12:32 GMT

> Select a 2 cell range, say, E1:E2

If instead of vertically within E1:E2,
we want the results horizontally, say, within F1:G1
Select F1:G1, put in the formula bar and array-enter:
=TRANSPOSE(OFFSET(A1:A2,,MATCH(MAX(A3:C3),A3:C3,0)-1))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Reply to this Message

Ron Rosenfeld - 02 Jan 2006 13:52 GMT

>If I use the following spread sheet as an example
>
[quoted text clipped - 18 lines]
>
>Thanks

=SUM(OFFSET(A1,0,MATCH(MAX(A3:C3),A3:C3,0)-1,2))

--ron

Reply to this Message