Large/small from a list

[learnex]

Hi,


I have a list like the one given below:

a1 a2 a3

a 37 53 57

b 38 42 75

c 68 31 19

d 89 22 48

e 69 23 46

a 84 25 85

b 13 37 40

c 54 39 42

d 21 61 83

e 68 89 17


I need to get the largest/smallest number from lets say col a2, which corrosponds to c.

with sumproduct, I get the sum of all of them (thanks to Chandoo.org). However, I am not able to solve this.

Any help will be appreciated.

 

[Hui]

Learnex


Firstly, Welcome to the Chandoo.org Forums


I am unclear of what youir asking here


Can you please explain what you mean a little better

Use the numbers and headers as examples

 

[NARAYANK991]

Hi ,


Try the following formula :


=MIN(IF(A10:A19="b",$B$10:$B$19,MAX($B$10:$B$19)))


entered as an array formula ( using CTRL SHIFT ENTER ).


I am assuming your data is in the range A10:D19 ; column A contains the categories "a" , "b" ,... B10:B19 contain the values for a1 , C10:C19 the values for a2 and D10:D19 the values for a3.


Since the above formula has the range $B$10:$B$19 , it will give the minimum value of a1 ; changing this to $C$10:$C$19 will give the minimum value of a2.


Changing the above formula to :


=MAX(IF(A10:A19="b",$B$10:$B$19,MIN($B$10:$B$19)))


again entered as an array formula ( using CTRL SHIFT ENTER ) , will give you the maximum value that satisfies the category criterion.


Narayan