Values in tables

[rneale]

I have a table of data on a worksheet like so
A 100 999 55
B 55 32 8
C 34 100 88

I can find the maximum value in the rightmost 3 columns of the tabl
with the MAX function (from the above it would be 999) but I want t
return the value of the leftmost column. So if the maximum was 999 th
value returned would be A.
I also want to find the minimum, which as you can see is 8 and retur
the value on the left, which would be B.

Any ideas

 

[Frank Kabel]

Hi
try the following array formula (CTRL+SHIFT+ENTER)
=INDEX($A$1:$A$10,MAX(IF(B110=MAX(B110),ROW(D110),0)),1)
If your range does not start in row 1 you have to subtract the
corrsponding starting row value-1 from the second INDEX parameter

HTH
Frank

 

[rneale]

No joy, the IF(B14 evaluatest to an error

 

[Frank Kabel]

Hi
the formula should work. What kind of error did you get? and did you
enter the formula as array formula (CTRL+SHIFT+ENTER)?.

Frank

 

[rneale]

I get error in value and when I do calculation steps it shows the B11
as evaluating to an error.

Explain this CTL+SHIFT+ENTER bit

 

[Frank Kabel]

hi
you have to enter this formula as an array formula. That is instead of
pasting/entering the formula into the cell and hitting ENTER only you
have to paste/enter the formula and finish this with hitting
CTRL+SHIFT+ENTER together.
If you look at the formula after this stept Excel should have added {}
brackets arround the formula. So entering this as an array formula
should fix your error

Frank

 

[rneale]

OK, done that but my table is actually in row 36. given your warnin
about subtract -1 etc, what should the formula look like

 

[rneale]

sOME SUCCESS, BUT IF THE VALUES IN THE TABLE CHANGE i FIND THE RESULT O
THE FORMULA STAYS THE SAME. DO ARRAY FUNCTIONS RECALCULATE AUTO

 

[Peo Sjoblom]

Yes.. If your wb is set to use automatic calculation.
Try by pressing F9 (for the whole wb Ctrl + Alt & F9)

 

[Frank Kabel]

OK, done that but my table is actually in row 36. given your warning

about subtract -1 etc, what should the formula look like?

Hi
try
=INDEX($A$36:$A$100,MAX(IF(B36100=MAX(B36100),ROW(B36100)-35,0)),
1)

frank