Values in tables

  • Thread starter Thread starter rneale
  • Start date Start date
R

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
 
Hi
try the following array formula (CTRL+SHIFT+ENTER)
=INDEX($A$1:$A$10,MAX(IF(B1:D10=MAX(B1:D10),ROW(D1:D10),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
 
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
 
I get error in value and when I do calculation steps it shows the B1:D1
as evaluating to an error.

Explain this CTL+SHIFT+ENTER bit
 
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
 
OK, done that but my table is actually in row 36. given your warnin
about subtract -1 etc, what should the formula look like
 
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
 
Yes.. If your wb is set to use automatic calculation.
Try by pressing F9 (for the whole wb Ctrl + Alt & F9)
 
OK, done that but my table is actually in row 36. given your warning
about subtract -1 etc, what should the formula look like?
Click to expand...

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

frank
 
Back
Top