Excel Functions

L

LNS

hi,

i have a worksheet as similar to the one below and i am trying to calculate
the minimum function and retrieve the corresponding column title:

A1 B1 C1 D1 E1 F1 G1
A2 COUNTRY A B C MIN OPERATOR
A3 UK 24 91 38 24 ??
A4 USA 36 65 39 36 ??
A5 INDIA 54 76 22 22 ??

What function can i use to find that for UK minimum is 24 and operator will
be A???

please assist!
 
S

Sheeloo

You can use this in E2 and copy down
=INDEX($C$1:$E$1,MATCH(MIN(C2:E2),C2:E2,0))
without having a separate column for MIN

or this in F2 with MIN in E2
=INDEX($C$1:$E$1,MATCH(F2,C2:E2,0))
 
L

LNS

thanks i tried both functions with and without the need of a separate column
for MIN but only function with MIn worked, the other just shows the min value
and not the title.
also could you please assist on which function to use to calculate the 2nd
lowest,3rd lowest (and vice versa 2nd highest, 3rd highest) without having to
sort and arrange data in ascending/descending order.
 
L

LNS

yes, my apologies, both functions work fine!!!
thanks for assistance but plz assist with the other query as to how to
calculate 2nd & 3rd min/max???
 
S

Sheeloo

Try
=RANK(A1,$A$1:$A$6,0)
this will give you the rank of A1 in A1:A6, rank 1 for highest

=RANK(A1,$A$1:$A$6,1) will give you rank 1 for the smallest number
 
L

LNS

No, what i meant is when i want to find minimum in F3, then i use the function
=MIN(C3:E3) and for maximum i use =MAX(C3:E3), but is there any possible
function that i can use to calculate 2nd lowest/highest and 3rd
lowest/highest?
 
S

Sheeloo

You an combine Index and Match just like you did for MIN/MAX

For numbers in A1:A6
enter this in B1 and copy to B6

=RANK(A1,$A$1:$A$6,0)

and this in C1
=INDEX($A$1:$A$6,MATCH(3,$B$1:$B$6,0))
will give you the third highest number
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

column to row draging 1
Need help with range value calculation 5
//autoaverage. 2
How to get the array like this? 6
Record Click on Error Indicator 2
Please help.... 12
Excel formula help 1
Transpose Macro 0

Top