Excel Functions

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!

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))
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"LNS" wrote:

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!

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.

"Sheeloo" wrote:

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))
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"LNS" wrote:

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!

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???




"LNS" wrote:

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.

"Sheeloo" wrote:

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))
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"LNS" wrote:

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!

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
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"LNS" wrote:

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???




"LNS" wrote:

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.

"Sheeloo" wrote:

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))
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"LNS" wrote:

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!

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?



"Sheeloo" wrote:

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
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"LNS" wrote:

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???




"LNS" wrote:

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.

"Sheeloo" wrote:

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))
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"LNS" wrote:

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!

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

--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"LNS" wrote:

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?



"Sheeloo" wrote:

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
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"LNS" wrote:

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???




"LNS" wrote:

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.

"Sheeloo" wrote:

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))
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"LNS" wrote:

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!

For the smallest:

=SMALL(C3:E3,n)

For the largest:

=LARGE(C3:E3,n)

Where n = the nth smallest/largest number that you want.

SMALL(C3:E3,1) and LARGE(C3:E3,1) are the same as:

MIN(C3:E3) and MAX(C3:E3)

*except* for when the range does not contain any numbers. MIN/MAX will
return 0 while SMALL/LARGE will return a #NUM! error.

--
Biff
Microsoft Excel MVP


"LNS" wrote in message
...
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?



"Sheeloo" wrote:

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
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"LNS" wrote:

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???




"LNS" wrote:

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.

"Sheeloo" wrote:

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))
--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.



"LNS" wrote:

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!