Minimum Value & Cell Cross Referencing

MS EXCEL

    Next

  • 1. Adding (creating) worksheet
    Hi, I have create a .XLS file that control some data on a daily basis, what I would like to do now is to create a new worksheet with everything that is already existing (calculation, lookuptable, etc....) for each new day. Since most of the users of the program don't know much else than how to input the data, I would like to automate the creation of a new sheet everytime that the this .XLS is open I have looked around and I can't find how to do this Any help would be appreciated -- Thank you in Advance Merci a l'Avance Martin
  • 2. Excel footnotes?
    Can you insert footnotes in Excel?
  • 3. How do I reference cells in multiple tabs via a formula
    In a workeet with multiple tabs, how do you reference a tab via formula. Ex. with typed array=index(tab1,2,3) Column A(array or tab) Column B tab2 =index(a2,2,3) tab3 =index(a3,2,3) tab4 =index(a4,2,3) and so on I'm geeting a formula error for this. Please help!
  • 4. How to find and sum top n from each columns?
    I have score of each game (12 games) for each player. I need to sum only top 10 (best 10) from each player how can I do it? e.g Game# Player1 Player2 Player3 Player4 1 85 97 99 91 2 102 90 110 95 3 72 95 112 93 4 90 93 105 99 Total 192 192 222 194 (top 2 games) Hope it makes clear. Thanks.
  • 5. font color in formulae
    When rounding to -3 the cell font color went red. How do i change t ot black?

Minimum Value & Cell Cross Referencing

Postby Johnny » Wed, 17 Aug 2005 07:28:21 GMT

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

Re: Minimum Value & Cell Cross Referencing

Postby Vasant Nanavati » Wed, 17 Aug 2005 09:44:23 GMT

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



Re: Minimum Value & Cell Cross Referencing

Postby Biff » Wed, 17 Aug 2005 09:51:16 GMT

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



Re: Minimum Value & Cell Cross Referencing

Postby Aladin Akyurek » Wed, 17 Aug 2005 16:29:44 GMT

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!

Minimum Value & Cell Cross Referencing

Postby Johnny » Wed, 17 Aug 2005 17:12:12 GMT

Biff Wrote: 

Superb. My thanks to you both!


-- 
Johnny

Similar Threads:

1.Should allow: "Value (X) axis crosses at *-MINIMUM-* value

2.Need PivotChart x axis to cross Y axis at minimum Y axis value

I see an option to have x cross y at max y, but how can I say min y?

3.Using a cell reference as the axis value for Horizontal axis crosses

Is there any way of referencing a cell as the value in the "axis
value" area of the "Format Axis dialog box. I am currently entering a
number i.e. 2.5. I would like to be able to have this number change
when a user enterer a value in a cell?  Would it have to be VBA code
or is there another way?

4.Excel-value in a range of cells for two cross references

Sirs,
Is there an easy way to find a corresponding value from a range of 
cells("m"rows &"n" columns) by looking to one value in a cell from a row & 
another value in another cell from a column.
Example:
Soppose, cells b1:m1 contains 12 values; cells a2:a13 contains 12 values.I 
want to find a value from b2:m13(144 cells) corresponding to a value from 
b1:m1 & another value from a2:a13.I have found out a round about method .Is 
there any short formula using ' if,lookup,....' for this.
Thanks & regards.

5.Minimum and maximum value of Axis set by user by reference to form

Excel seems to have 2 alternatives for defining the min/ max value of an 
axis: either automatically or preset by the user.

But is there any way that the minimum and maximum value that the axis is 
defined by the user based on the values of the spreadsheet (i.e., halfway 
between the above two apparent options provided by microsoft)?

6. help on circular reference and MINIMUM value to be found

7. How do I find a cross reference value

8. Show cross-referenced value from 2 combo boxes



Return to MS EXCEL

 

Who is online

Users browsing this forum: No registered users and 80 guest