Minimum Value & Cell Cross Referencing
 

Hope someone can help: I’ve searched but can’t find a similar question so apologies in advance for repetition. This seems REALLY simple but I’m failing miserably!

Basically I’m designing a spreadsheet to track the performance of a customer’s network against a service level agreement. I’m trialling it with 50 sites although it’ll be over 500 in total…. In column A (A2:A52) the names of the sites are listed. In column D (D2:D52) the site availability is listed.

I can find the lowest number fine by using =MIN(D2:D52) but can I add something into a cell which will report the site from column A that the lowest number corresponds to?

At a very basic level it’ll look something like this (but with straight columns!):

¦ A ¦ D ¦
1¦ Site ¦ Availability ¦
2¦ London ¦ 99.50 ¦
3¦ Paris ¦ 99.80 ¦
4¦ New York ¦ 99.90 ¦
5¦ Lowest Avail ¦ 99.50 ¦ - Calculated using =MIN(D2:D4)
6¦ Lowest Site ¦ London ¦ - What would I use to get this automatically?

Any help would be appreciated!

Try:

=INDEX(A2:A52,MATCH(MIN(D2:D52),D2:D52,0))

--

Vasant


"Johnny" wrote in message
...

Hope someone can help: I've searched but can't find a similar question
so apologies in advance for repetition. This seems REALLY simple but
I'm failing miserably!

Basically I'm designing a spreadsheet to track the performance of a
customer's network against a service level agreement. I'm trialling it
with 50 sites although it'll be over 500 in total.. In column A
(A2:A52) the names of the sites are listed. In column D (D2:D52) the
site availability is listed.

I can find the lowest number fine by using =MIN(D2:D52) but can I add
something into a cell which will report the site from column A that the
lowest number corresponds to?

At a very basic level it'll look something like this (but with straight
columns!):

¦ A ¦ D ¦
1¦ Site ¦ Availability ¦
2¦ London ¦ 99.50 ¦
3¦ Paris ¦ 99.80 ¦
4¦ New York ¦ 99.90 ¦
5¦ Lowest Avail ¦ 99.50 ¦ - Calculated using
=MIN(D2:D4)
6¦ Lowest Site ¦ London ¦ - What would I use to get
this automatically?

Any help would be appreciated!


--
Johnny

Hi!

=INDEX(A2:A52,MATCH(MIN(D2:D52),D2:D52,0))

Is there a possibility of duplicate minimums?

The above formula will always find the first instance.

Biff

"Johnny" wrote in message
...

Hope someone can help: I've searched but can't find a similar question
so apologies in advance for repetition. This seems REALLY simple but
I'm failing miserably!

Basically I'm designing a spreadsheet to track the performance of a
customer's network against a service level agreement. I'm trialling it
with 50 sites although it'll be over 500 in total.. In column A
(A2:A52) the names of the sites are listed. In column D (D2:D52) the
site availability is listed.

I can find the lowest number fine by using =MIN(D2:D52) but can I add
something into a cell which will report the site from column A that the
lowest number corresponds to?

At a very basic level it'll look something like this (but with straight
columns!):

¦ A ¦ D ¦
1¦ Site ¦ Availability ¦
2¦ London ¦ 99.50 ¦
3¦ Paris ¦ 99.80 ¦
4¦ New York ¦ 99.90 ¦
5¦ Lowest Avail ¦ 99.50 ¦ - Calculated using
=MIN(D2:D4)
6¦ Lowest Site ¦ London ¦ - What would I use to get
this automatically?

Any help would be appreciated!


--
Johnny

Let A2:A6 house the sites and D2:D6 their availability scores.

E2, copied down:

=RANK(D2,$D$2:$D$6,1)+COUNTIF($D$2:D2,D2)-1

F2: 1

which means lowest one.

F3:

=MAX(IF(INDEX(D2:D6,MATCH(F2,E2:E6,0))=D2:D6,E2:E6 ))-F2

which must be confirmed with control+shift+enter, not just with enter.

G2, copied down:

=IF(ROW()-ROW($G$2)+1<=$F$2+$F$3,INDEX($A$2:$A$6,MATCH(ROW()-ROW($G$2)+1,$E$2:$E$6,0)),"")

The foregoing would yield the list:

London
Athens

when A2:A6 houses:

London
Paris
New York
Istanbul
Athens

with as availability scores in D2:D6...

99.5
99.8
99.9
99.67
99.5

Johnny wrote:
Hope someone can help: I’ve searched but can’t find a similar question
so apologies in advance for repetition. This seems REALLY simple but
I’m failing miserably!

Basically I’m designing a spreadsheet to track the performance of a
customer’s network against a service level agreement. I’m trialling it
with 50 sites although it’ll be over 500 in total…. In column A
(A2:A52) the names of the sites are listed. In column D (D2:D52) the
site availability is listed.

I can find the lowest number fine by using =MIN(D2:D52) but can I add
something into a cell which will report the site from column A that the
lowest number corresponds to?

At a very basic level it’ll look something like this (but with straight
columns!):

¦ A ¦ D ¦
1¦ Site ¦ Availability ¦
2¦ London ¦ 99.50 ¦
3¦ Paris ¦ 99.80 ¦
4¦ New York ¦ 99.90 ¦
5¦ Lowest Avail ¦ 99.50 ¦ - Calculated using
=MIN(D2:D4)
6¦ Lowest Site ¦ London ¦ - What would I use to get
this automatically?

Any help would be appreciated!

Superb. My thanks to you both!