Minimum Value & Cell Cross Referencing

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

Basically I designing a spreadsheet to track the performance of a
customer network against a service level agreement. I trialling it
with 50 sites although itl 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 itl 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

Try:

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

-- 

Vasant






> 1       Site              Availability >  
> 2     London                  99.5>     
> 3     Paris                    99>80      
> 4   New York               >99.90      
> 5  Lowest Avail             99.50      - >alculated usin>
> =MIN(D2:D4)
> 6  Lowest Site          London        - What>would I use to get
> >hi> automatically?
>
> Any help wo>ld>be>apprec>ated!
>
>
> -- 

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





> 1       Site              Availability >  
> 2     London                  99.5>     
> 3     Paris                    99>80      
> 4   New York               >99.90      
> 5  Lowest Avail             99.50      - >alculated usin>
> =MIN(D2:D4)
> 6  Lowest Site          London        - What>would I use to get
> >hi> automatically?
>
> Any help wo>ld>be>apprec>ated!
>
>
> -- 

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



> with 50 sites although itl 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 itl look something like this (but with straight
>> columns!):
>> 
>>         A                     D          >
> 1       Site              Availability>   
> 2     London                  99.>0     
> 3     Paris                    9>.80      
> 4   New York              > 99.90      
> 5  Lowest Avail             99.50      ->Calculated usi>g
> =MIN(D2:D4)
> 6  Lowest Site          London        - Wha> would I use to get
> >his>automatically? 
> 
> Any help w>uld>be appreciated!

Biff Wrote: 

Superb. My thanks to you both!


-- 
Johnny