Max Min 2

[feajob]

Hi,

I have the following list:

Event Signed VM
------------------
443080 48.8
444084 47.5
512164 505.7
513164 503.3
514164 500.6
522167 -428.0
523167 -429.3
524167 -430.8
532164 348.4
533164 326.5

I can find out easily max and min of the above list with max/min functions, as follows:

Max. 505.7
Min. -430.8

But, I would like to determine which events cause max or min condition? So, I am looking for the following events:

Event related to Max. 512164
Event related to Min. 524167

I am looking for an excel command (function). I know that I can make a VB macro for this purpose, but I think that there is way to perform this task easily. In order to use vlookup, I need to have second column first and this is not my case.

I appreciate to know if you know how to determine these events easily.

Regards,
A.A.Y.







I would like to do the following process:

 

[rb1957]

i use a dumb approach ... in the next column use an if to compare the row value to the maximum, return the row value if true, and blank if false (and turn off zero values, under options)

 

[MintJulep]

Using named ranges for Events and Values

Min: =INDEX(event,MATCH(MIN(value),value,0),1)

Max: =INDEX(event,MATCH(MAX(value),value,0),1)

 

[Onda]

Here your numbers (I have moved the first column to 2th and the 2th to first)

B C
2 48.8 443080
3 47.5 444084
4 505.7 512164
5 503.3 513164
6 500.6 514164
7 -428 522167
8 -429.3 523167
9 -430.8 524167
10 348.4 532164
11 26.5 533164

In cell B13 there is MIN(B2:B11)
To get the value corrispondig to min:
=VLOOKUP(B13;B2:C11;2;FALSE)

Onda

 

[FOURe]

How about using the Data / Sort option from the Excel Main Menu ...

http://www.engineering-4e.com

 

[feajob]

Thank you for all replies. I think that "match" command proposed by MintJulep is the best match for me. It works fine and it is short and easy.

A.A.Y.

 

[dik]

And with the sort, I often create a single column that I call NDX and have the numbers 1, 2, 3, ... n. by using the preceding cell + 1 and then convert to real numbers by special paste - value. After they are sorted, the NDX column can be used to put them back into their original sequence if necessary.

Dik

 

[FOURe]

Dik:

When working with ORACLE and/or SQL, I do the same thing so that the original order of input data is always preserved -- it is good to be able to go back and forth all the time ...

http://www.engineering-4e.com