Looking up corresponding entry in table?

I am wondering if I can combine the MIN()/MAX() and *LOOKUP() functions
to perform the following task.  Suppose I have a table of data (2D) and
want to use the MIN()/MAX() functions to pick out the min and max
values in the Y-column respectively.  Furthermore, suppose I want to
pick out which X-value produced those values.  Can this be done?

Mike

0
mfeher (21)
6/30/2006 5:22:53 PM
excel 39879 articles. 2 followers. Follow

2 Replies
438 Views

Similar Articles

[PageSpeed] 55

In A1:A6 (my x-values) I have some numbers; likewise in B1:B6 (my y-values)
The lowest B value is 3 and it occurs in B4, next to this in A4 is the 
number 13
The formula =MIN(B1:B6) returns the value 3 (lowest value in the range)
The formula =MATCH(MIN(B1:B6),B1:B6,0) returns the value 4 since the 3 value 
was in the fourth cell of the range
Finally =INDEX(A1:A6,MATCH(MIN(B1:B6),B1:B6,0)), locates the forth entry in 
the x-range and returns its value - in my case 13.
best wishes
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Zerex71" <mfeher@stny.rr.com> wrote in message 
news:1151688173.531044.239750@i40g2000cwc.googlegroups.com...
>I am wondering if I can combine the MIN()/MAX() and *LOOKUP() functions
> to perform the following task.  Suppose I have a table of data (2D) and
> want to use the MIN()/MAX() functions to pick out the min and max
> values in the Y-column respectively.  Furthermore, suppose I want to
> pick out which X-value produced those values.  Can this be done?
>
> Mike
> 


0
bliengme5824 (3040)
6/30/2006 7:02:30 PM
Thank you very much!

Bernard Liengme wrote:
> In A1:A6 (my x-values) I have some numbers; likewise in B1:B6 (my y-values)
> The lowest B value is 3 and it occurs in B4, next to this in A4 is the
> number 13
> The formula =MIN(B1:B6) returns the value 3 (lowest value in the range)
> The formula =MATCH(MIN(B1:B6),B1:B6,0) returns the value 4 since the 3 value
> was in the fourth cell of the range
> Finally =INDEX(A1:A6,MATCH(MIN(B1:B6),B1:B6,0)), locates the forth entry in
> the x-range and returns its value - in my case 13.
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Zerex71" <mfeher@stny.rr.com> wrote in message
> news:1151688173.531044.239750@i40g2000cwc.googlegroups.com...
> >I am wondering if I can combine the MIN()/MAX() and *LOOKUP() functions
> > to perform the following task.  Suppose I have a table of data (2D) and
> > want to use the MIN()/MAX() functions to pick out the min and max
> > values in the Y-column respectively.  Furthermore, suppose I want to
> > pick out which X-value produced those values.  Can this be done?
> >
> > Mike
> >

0
mfeher (21)
6/30/2006 7:25:19 PM
Reply:

Similar Artilces:

pick up particular number from a table
Hi, I have below table: A B C D E 1 04/10 04/10 04/10 04/10 2 AB YZ AB YZ 3 MARY 45 56 16 62 4 PETER 50 48 84 45 5 JOHN 60 78 65 50 How can i get number 45 at B3 when I have reference 04/10, AB, and Mary in another sheet? thanks a lot! -- Lowan sorry, D1 and E1 should be 05/10 -- Lowan "Lowan Chan" wrote: > Hi, > > I have below table: > > A B C D E > 1 04/10 04/10 04/10 04/10 > 2 AB YZ AB YZ > 3 MARY 45 56 16 62 > 4 PETER 50 48 84 45 > 5 JOHN 60 78 65 50 > > How can i get number 45 at B3 wh...

Previous versions looking at old DPM server
We have recently moved to a new DPM 2007 server and everything is working correctly except the end user restore feature. The previous versions tab only shows recovery points from the old DPM server. I have checked the “enable end user recovery” box and run the DPMADSchemaExtension.exe program. I also verified that the new DPM server has the same permissions as the old server for MS-ShareMapConfiguration in active directory. Hi Weiman, When you moved to new DPM server how did you configure the agent on the production server? Did you use SetDPMServer.exe on the Production...

how do you set up a table of winners vs. losers with total as %?
...

Credit Card entries
I have read some of the posts after I Searched this topic, but I am still too dense to understand how to do credit cards. I have read how to do it in the Money Help, but when I actually do it I always end up looking as if I owe bazillions. Here are the factors that complicate their simple directions: *I like to input my data each week, not monthly when I get my statement. I think if I set it up correctly that I could still do this, but durned if I know how. *Each month I pay off my credit card, usually not just the balance shown but however much I've accrued by the date that I ...

indexing in a table
Hi, Can anyone confirm (or deny) that when indexing in a table is set to 'Yes - No Duplicates' and then you try and append data from a sorted query into that table, DOES THE TABLE ALWAYS ACCEPT THE FIRST ROW (as sorted by the query) or is it more random than that. thanks S On Sun, 28 Feb 2010 14:19:01 -0800, sdg8481 <sdg8481@discussions.microsoft.com> wrote: SQL is not random. In fact it is firmly rooted in mathematical set theory, and all its operations are 100% predictable. If you meant to ask "I have another table that has data that would violate t...

Grand Average in Pivot Table?
Hello, I'm pretty much a Pivot Table idiot, so I might be overlooking something horribly obvious, but I'm trying to have a "Grand Average" column and row in a Pivot Table instead of a "Grand Total." I see the Grand Total checkboxes in the Options dialog box, but I've yet to stumble upon a way to specify a function other than Sum. Is this possible? If so, can someone let me know how? Excel2000. If I didn't explain that clearly enough, let me know and I can give an example. Thanks for the help. Mike When you drag the number header into the data ...

How to hide summary field in Pivot table
Hi I have Pivot table where I had grouped some fields, what I wish to do is to make one group to summarise and other not to. Becouse when I change properites I can only make changes to whole group. When I hide one others hide as well. You could manually hide some of the rows, but you can't change the pivot table to show only some of the subtotals for a field. To hide a row, right-click on a row button, and choose Hide Piotr wrote: > Hi I have Pivot table where I had grouped some fields, what I wish to > do is to make one group to summarise and other not to. > Becouse when I ch...

How to let pivot table chart's format stay after refreshing data.
I am using 2007 to do some pivot table pie charts. I selected a red and blue based format from DESIGN tool bar, and change blue to green. after I saved then refreshed data, the blue color comes back. How can I let the green color stay instead of blue? thanks. Hi, It appears that you are changing the color in the wrong place: 1. With a single slice selected choose PivotChart Tools, Format 2. Click Format Selection 3. Pick the Fill tab 4. Select Solid fill 5. Pick a Color and Close the dialog box. If this helps, please click the Yes button. Cheers, Shane Devenshire "JJ" wrote:...

combo box look up text box error
I used this code =DSum("Amount", "[July 09 - On Billings and Adjustments Data Query]", " [Customer No#] = " & cboCustomerNumber & " AND Format([Posting Date Period], ""yyyymm"") = '" & Format(cboPostingDate, "yyyymm") & "'") to put the values in my text box but i'm just getting #error in the text box i have Customer Number box pulling the Customer number from the customer listing table, and the rest coming from the July 09 - On Billings an...

Lookups Based on Table
I have a table called Products. For simplicity it contains 2 fields. Product and Size. Here is some sample data Coke 1L Coke 6x355ml Coke 2L Pepsi 1L Oreos 500g Here is what I want to do... On a form I want 2 combo Boxes, Product and Size. If I Select Coke from a unique list of Products then Size displays the unique sizes associated with Coke. If I Select 500g then I get the list of unique products that come in that size. Basically linking the combo boxes based on the valyue of the other... This should be easy but it has been too many years for me since I did t...

Where is tables in reports
Is there anyway to get tables into reports? If there is none then is there a work around. The forms I have to make use many block cells. In word tables makes things easy. ...

How do I insert the name of a table into my "master" table?
So I'm doing an append query, appending data from about 300 separate tables into one master table. I want to include the name of the originating table name as a field for each record that I append into the master table. Does anyone out there have any ideas/suggestions on how I would go about doing this, please? Responses are very much appreciated, thanks in advance! Adrienne Are you trying to set up a single append query that appends from ALL 300 tables at once? Are you confident that each of the 300 is "well-formed" and matches up properly to the destination? If creating ...

Top 10 Things To Look For In A Web Host
Top 10 Things To Look For In A Web Host http://hosting-top10.blogspot.com/2008/07/top-10-things-to-look-for-in-web-= host.html 5 Tips To Choose A Domain Name For An Internet Business Web Hosting Advice http://hosting-top10.blogspot.com/2008/07/web-hosting-advice.html http://hosting-top10.blogspot.com/2008/07/5-tips-to-choose-domain-name-for.= html Domain Name Registration - Investments That Reap Benefits (part 2) http://hosting-top10.blogspot.com/2008/07/domain-name-registration-investme= nts_28.html Getting One Free Domains - Free Stuff Sites http://hosting-top10.blogspot.com/2008/07/getting-...

Pivot table changes from 97 to 2000
In Excel 97 on a pivot table you could double click on a data cell in the pivot table and a new worksheet would be created that contains all the data that was being summarized on the pivot table for that particular cell. When I open a pivot table in 2000 that was created in 97, this functionality seems not to exist. Is there a way to enable this functionality in Excel 2000? Any assistance will be greatly appreciated. David Grimsled If you haven't changed any of the settings in the table, it should allow drilling down in Excel 2000, just as it does in Excel 97. To turn the fea...

Pivot table calculated field using MAX of another field
How do I get a calculated field to calculate using the MAX of another field? Say I have the following: SIZE COLOR Max of Qty Sum of Cost Sum of QtyXCost large blue 8 5 40 green 2 4 8 red 10 6 60 large Total 10 15 300* medium green 4 11 44 red 5 8 ...

Access 2003
I maintain an equipment inventory database in Access 2003 with just a handful of tables and several regular queries. Each piece of equipment has a unique 6-digit asset tag, and that field is the primary key. No two records can have the same asset tag. Periodically equipment gets replaced, and I am looking for a way to do the following when replacing multiple pieces of equipment. 1. Multiple asset tags would be entered into a form or table. Their corresponding records would be found in TABLE-1 2. Certain fields of the records found would be appended to TABLE-2 showing them as having been repla...

Unbound Data Entry Form
Bit of a "noob" question but here goes.... I am using an unbound form for data entry purposes. I am using an unbound form as I have some VBA code which checks that mandatory fields are filled in first. This works fine where all the fields are text boxes (i.e. not combos or list boxes) but the problem I'm having is that the parent table "tblContracts" has 4 lookup tables (which are used for combos with various fields on the form) and these are numeric fields which link between the tables (i.e. In the parent table the field would be SupplierID whick li...

Exchange 2003 constantly looking at one domain controller
I have an old 2000 Domain controller that needs to be removed from our Windows 2000 Domain and replaced by a new DC. We also have Exchange 2003 Enterprise setup as an Active/Passive cluster. However, whenever the old DC is taken off line Exchange seems to hang and not respond until this specific DC is back online. My Current DC infrastructure is as follows: 5 Domain Controllers, two which are old and will be removed from service and three new which will remain. The DC that will be removed is running AD, DNS and WINS while the other old DC is simply running AD. All my clients and servers loo...

Question for Peo-Pivot table
Hello Peo: Thanks for your reply, I have taken out the month as advised, but d you might going a little slower. When I right-clicked on my pivot table there is no 'select average option there for me to click on, also when I right-clicked on the bil day field in my pt and clicked on select group and show detail, then o select group, I get the message "Cannot group that selection". The PT is presently structured as: Bill Day in Row field, Analyst i column field and Current Due in Data field. Thank you very much for your patience. Kem -- Message posted from http://www.ExcelFor...

Pivot Table
I have a simple jobs sold list with dates and salesman. I would like to put the date on the page field and then filter by periods like month or quarters. But I dont see anyway for appling an advanced filter for a less than and greater than date. selecting individual date entries would be tedious. Thanks for any suggestions. -- Ingersoll If I understand your question correctly, you could try putting date data in the Row Field. Then, right-click on a date and choose Group and Show Detail. Click Group... Then, choose the Time Periods you want to group the dates by. Click Okay. You will k...

Stock count entry #4
Hi, We have GP 7.5 and we are about to do physical stock taking for the first time. We would be using scanners so the stock count results would be available in excel format (with item codes and matching counted quantity). We are familiar with creating a stock count in GP. Since we have nearly 15,000 item codes it wont be practical to type in the count quantity into the stock count entry screen. So we are trying to find out if the excel file with the count data can be uploaded into the stock count enntry screen - Does GP have a menu option for this or this can be done through a dat...

Merge two tables
Hi there I inherited information from a colleague who had created 2 tables instead of one table with all the information. How do I combine the information from the 2 separate tables into 1? Essentially the original 2 tables are two different halves of the same survey, Survey 1 and Survey 2. Thank you in advance. On Fri, 4 Dec 2009 20:01:01 -0800, forest8 <forest8@discussions.microsoft.com> wrote: >Hi there > >I inherited information from a colleague who had created 2 tables instead of >one table with all the information. > >How do I combine t...

Make table query 12-09-09
Can you set the datatype within a make table query. I want to add a new field which I want it to be yes/no but it comes out as number 0 in the table schema. I like it to be yes/no. Are you saying that the field that's created is a Numeric field, or that it's a Yes/No field, but it displays a number, not the words Yes or No? You have very little control over Make Table queries. That's why the recommended approach is usually to create the table first, and then append to it. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) ...

Linking Tables 01-14-08
I need some advice on basic table set-up / linking I have a student record table with a unique identifier (student ID) Contains student name and address I have an enrollment record table with the same unique identifier (student ID) Contains term and year attended Now I would like to create a third table to record transcript orders. The unique identifier would be student ID. However, I would also like the student name to appear in this table (it is stored in the student record table). How do I get the name to show up in this new table without duplicating the information? Create a q...

How do I set up a league table in Excel?
I want to set up a spreadsheet to chart NRL results, such that I can input the results of each game and it will sort teams by their current ranking round by round. Not sure if you received your answer, I assume you are looking for the sheet to automatically sort you data? "Steven Jones" wrote: > I want to set up a spreadsheet to chart NRL results, such that I can input > the results of each game and it will sort teams by their current ranking > round by round. > > . > ...