reformulating question regarding maximum value

I had an hour ago put a question up named "find maximum", but I had not 
formulated my query fully, hence this query.

In each row, I want to find the second highest value in that row, and then 
as output give the value of that cell in the first row.
For example:

1 2 6 8 6 5 4 3
2 4 5 6 3 2 3 8
6 4 5 3 7 2 3 1

The second largest value in the second row is nr 6. It is in the fourht cell 
of that row. The fourth cell of the first row is the value 8. This should be 
the output for the second row.
The second largest value in the third row is the number 6. It is in the 
first cell of that row. The first cell of the first row has the value 1. This 
should be the output for the third row.

I assume that such a thing needs to be set up with conditional formatting. 
Does anyone have an idea how to do this?

Regards,


0
FrankDrost (11)
1/18/2006 2:25:02 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
617 Views

Similar Articles

[PageSpeed] 8

Hi,

Lets say your range is between cells A1 to H3,
Use

=INDEX($A$1:$H$3,1,MATCH(LARGE($A$2:$H$2,2),$A$2:$H$2,0)) to get the 
value of second largest value in Row 2 and to return the corresponding 
vlaue in Row 1

use

=INDEX($A$1:$H$3,1,MATCH(LARGE($A$3:$H$3,2),$A$3:$H$3,0))

to get the value of second largest value in Row 3 and to return the 
corresponding value in Row 1

Regards

Govind.

Frank Drost wrote:

> I had an hour ago put a question up named "find maximum", but I had not 
> formulated my query fully, hence this query.
> 
> In each row, I want to find the second highest value in that row, and then 
> as output give the value of that cell in the first row.
> For example:
> 
> 1 2 6 8 6 5 4 3
> 2 4 5 6 3 2 3 8
> 6 4 5 3 7 2 3 1
> 
> The second largest value in the second row is nr 6. It is in the fourht cell 
> of that row. The fourth cell of the first row is the value 8. This should be 
> the output for the second row.
> The second largest value in the third row is the number 6. It is in the 
> first cell of that row. The first cell of the first row has the value 1. This 
> should be the output for the third row.
> 
> I assume that such a thing needs to be set up with conditional formatting. 
> Does anyone have an idea how to do this?
> 
> Regards,
> 
> 
0
adgraj1 (46)
1/18/2006 2:39:51 AM
If your numbers run from Column A to H,
Try this formula in I2:

=INDEX($A$1:$H$1,MATCH(LARGE($A2:$H2,2),$A2:$H2,0))

And copy down as needed.

In case of ties, this will return the location of the *first* second largest
number.
-- 
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Frank Drost" <FrankDrost@discussions.microsoft.com> wrote in message
news:D58D7940-E6F8-45A7-A80D-F8A2C5DDFD55@microsoft.com...
> I had an hour ago put a question up named "find maximum", but I had not
> formulated my query fully, hence this query.
>
> In each row, I want to find the second highest value in that row, and then
> as output give the value of that cell in the first row.
> For example:
>
> 1 2 6 8 6 5 4 3
> 2 4 5 6 3 2 3 8
> 6 4 5 3 7 2 3 1
>
> The second largest value in the second row is nr 6. It is in the fourht
cell
> of that row. The fourth cell of the first row is the value 8. This should
be
> the output for the second row.
> The second largest value in the third row is the number 6. It is in the
> first cell of that row. The first cell of the first row has the value 1.
This
> should be the output for the third row.
>
> I assume that such a thing needs to be set up with conditional formatting.
> Does anyone have an idea how to do this?
>
> Regards,
>
>

0
ragdyer1 (4060)
1/18/2006 2:41:56 AM
Reply:

Similar Artilces:

Setting checkbox value in subform
Hi All, I am wanting to set the value of a checkbox on each line of a subform based on a value on the main form and a value on the subform. the code I hoped would work is below. Clearly it doesn't as the checkbox I'm wanting to set is always set to True irrespective of the value of the other subform checkbox. Private Sub Form_Current() If Forms![Events]![EventTypeID].Value = 1 And Forms![Events]![Events Subform].Form![L2 Attended].Value = True Then Forms![Events]![Events Subform].Form![Attended].Value = True ElseIf Forms![Events]![EventTypeID].Value = 2 And F...

Need Help on Matching a value from a list of values
This is my formula: =IF(Data!B2:B21=K54,"ZONE2","ZONE1") and k54=PINE AND PINEL is found IN THE RANGE B2:B21 ;however, this fromula return ZONE1 Can some one help me? Thank -- Message posted from http://www.ExcelForum.com One way: =IF(ISNA(MATCH(K54,Data!B2:B21,FALSE)),"ZONE1","ZONE2") another: =IF(COUNTIF(Data!B2:B21,K54),"ZONE2","ZONE1") In article <nespa.19412i@excelforum-nospam.com>, nespa <<nespa.19412i@excelforum-nospam.com>> wrote: > This is my formula: =IF(Data!B2:B21=K54,"ZONE2&qu...

MS Money choices for security question don't match bank's choices
My bank recently updated my account for extra security using selectable challenge questions. I select 3 personal questions from a list ("what is your first dog's name", "where did you graduate from", etc...) and then I type in my answers for those three questions. Problem is that when I go into MS Money 2006 and try to update my login info for that account, Money's list of questions is missing one of the questions I picked. And now Money can't retrieve my account activities from the bank because "my sign-in info doesn't match the bank's&quo...

How can I filter ofr odd and even values
Hi, Can I filter Odd and even values in Excel.Is there any formula, function or filter? Thanks and Merry Christmas to all Easiest is to use a helper column with a formula like =MOD(A2,2) HTH. Best wishes Harald "Diana" <dianamrcoach@gmail.com> wrote in message news:e4f3aff2-4245-4fa5-b091-226518bfda76@b2g2000yqi.googlegroups.com... > Hi, > > Can I filter Odd and even values in Excel.Is there any formula, > function or filter? > > Thanks and Merry Christmas to all Thank you very much. Great solution! ...

How To maintain ListControl Values
Hi, I had ListControl,its contains looks like " Index IPAddress ID Pwd" fields . I want to edit the box and give the input to those editbox values at the run time.After that if user click close the Listcontrol dialog application those values, will maintain the listcontrol.How to maintain the listcontrol values. In the OnInitDialog() function i called: m_lstCtrl.SetExtendedStyle(m_lstCtrl.GetExtendedStyle()| LVS_EX_GRIDLINES|LVS_EX_FULLROWSELECT); I set the properties in the ListControl Appearance EditLabels i set TRUE View i set REPORT and then Single Selecti...

Generating PO question
I'd like to use our own rules for generating PO's What I'm looking for - is what fields I can use in GP to achieve this. Little more details. Let's say I have 10 available of Item "A" My logic decides that I need 5 more. What I tried to do is I update field in Item Planning screen and used "Order Point" and "Order Up To Point" In my mind - setting order point to 11 and order up to point to 15 should make system generate PO for qty of 5. It doesn' happens though. I get order for 11.. If I change option - I get order for 15. But I can't ma...

How can I Convert Links into Values for only Visible Cells?
How can i convert links into values for only visible cells for selected range(s)? Regards, Murtaza Hi Murtaza Push F5 then click Special, then "Visible cells" then ok. Now do copy - paste special- values *****Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum & Business Software***** Thanks for your reply but I got error "That command cannot be used on multiple selections." Thing is that I have a series of rows, say from A1-A5 (visible & links) then A6 (hide & formula). Now again from A8-A13(visible & links) a...

Is there a newsgroup for Blackberry questions?
Hello! Anyone know of a group for Blackberry support? Gregg Hill Gregg Hill <bogus@nowhere.com> wrote: > Anyone know of a group for Blackberry support? comp.dcom.telecom seems to have discussions on that. -- Brian Tillman Thank you, Brian! "Brian Tillman" <tillman1952@yahoo.com> wrote in message news:uxoIuvkqEHA.2732@TK2MSFTNGP09.phx.gbl... > Gregg Hill <bogus@nowhere.com> wrote: > >> Anyone know of a group for Blackberry support? > > comp.dcom.telecom seems to have discussions on that. > -- > Brian Tillman ...

Beginner Question
How do I give an MS Project schedule a name?? Any help is greatly appreciated.... -- TMI Contractors ------------------------------------------------------------------------ TMI Contractors's Profile: http://forums.techarena.in/members/216904.htm View this thread: http://forums.techarena.in/microsoft-project/1334108.htm http://forums.techarena.in Can you say more about what the problem is? What are you trying to do? Why is naming the schedule a problem? What have you tried to do and what were the symptoms of the failure? Or are you just having us on? --rms ...

Copy/Paste Values code
Is there a faster way to copy and paste one entire worksheets data into another existing worksheet? I do NOT want to rename the sheets. Code below is what I'm using now but it seems that there should be a simpler way. Thanks! Sub CopySheets() Sheets("Sheet2").Select Cells.Select Selection.Copy Sheets("Sheet1").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False End Sub Give this a try... Sub...

Not accept a value of 0 in a currency field.
In a cost savings field I want to not accept a savings of 0, but a cost of less than or greater than 0 is acceptable. In my projects database there is a cost saving field and the engineers are getting around it by putting in a 0 so I want to force them to put in an acceptable value. for example the savings might be $1.99 or ($1.99) but not $0.00. How can i do that. in the validation rule property of your field put <>0 "Leslie" wrote: > In a cost savings field I want to not accept a savings of 0, but a cost of > less than or greater than 0 is acceptable. In my ...

Copy Color Formats Based On Column Date Values
I currently have this code, where HMLoc is the starting point for a color paste at C6. Instead of having this a constant field, I need it to search for today's date in the cell above it (C5) (The dates are given in the range C5:BO5), and then color accordingly. I have struggled with this all morning and can't get it to work the way I want it. Please help me! Option Explicit Const LegendLoc = "BH3" Const HMLoc = "C6" Const None = 0 Sub ColorHM() Dim theRow As Integer Dim theCol As Integer Dim NumX As Single Dim Color1 As Integer Dim Color2 As Integer Dim Color3 A...

question driving me nuts
Okay...I never thought I would ever have to use excel again. Lo and behold, I am now the owner of a business and have forgotten how to do some things. I know how to create formaulas for adding and stuff. I forgot how to make my calculatons so that my deposit would be broken down. For example say I have 5 of each dollar denomination (1,2,5,10,20,50,100) and coin (1,5,10,25,50,$1). My deposit has to be 790 (5 of each denomination-150). What I want my sheet to do is tell me how much of each denomination I should pull out in terms of numbers instead of dollar amount. For exaple instead of ...

regarding read/write access issues
Hi all, Let me first expalin the scenario in Unix Environment: lets say there are 2 programmers( A & B). "A" opens a excel document xyz.xls and types some data and saves it but forgets to close excel document at the end of the day. Mean while "B" is online later that evening and trying to write something on xyz.xls but when he/she opens the excel sheet its read only( since A opened xyz.xls). Is there any way B can kill the document on "A"'s machine so he/she can gain write access. I am thinking there should be a way in unix to solve this.. Thanks alot,...

ms query question
win2kpro, office 2000 excel hangs when invoking 'run saved query', 'new database query' etc have uninstalled and reinstalled ms query from the office tools in the installation cd so i'm just trying to verify if i have good installation one news group said to check the registry for the folder: LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\MSQuery, and the folder should have the location of msqry32.exe, etc my registry doesn't have that folder however my access 2000 works fine with queries and i also have access 97 that works fine with queries any ideas what is wrong...

Macro to add value if cell has a color :-s
I don't know if this exists, but is it possible to check if a certain cell has a color? Normally a cell is blank, but someone asked me to see if it is possible to check if a cell is colored.. Any suggestions? Thanks!!! Is this sufficient? Sub ifcolor() If Range("b2").Interior.ColorIndex > 0 Then MsgBox "yep" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com <test@testnospam.nl> wrote in message news:obht5456aq6ur5gbqmasacqke9p2g13o7i@4ax.com... >I don't know if this exists, but is it possible to check if a cer...

help needed to add values with conditions
Hi, I have some data N O P Q TD (Y/N) Value VAT Total Val Y £71.44 £10.72 £82.16 4 N £71.44 £10.72 £82.16 5 N £84.08 £12.61 £96.69 6 Y £84.08 £12.61 £96.69 N £102.85 £15.43 £118.28 N £34.22 £5.13 £39.35 N £0.00 £0.00 £0.00 N £0.00 £0.00 £0.00 N £0.00 £0.00 £0.00 N £84.08 £12.61 £96.69 N £84.08 £12.61 £96.69 Y £84.08 £...

Form Question 03-23-07
I recently download a template Access Database from Microsoft. I have made a few chnages to accomodate our business. On the forms I removed some fields and added the new ones that I created on the table. The problem that I am having is that when you tab from one field to another to enter information, it doesn't hit all fields, it goes by some of them and returns to previous fields. I am not real savy with access so any help would be greatly appreciated. Kat Kathy, With your form in design view, right click and select "Tab Order". Then place your controls in the des...

Auto-expiring excel cell values
Hi! Does anybody know how to make cell values "disapear" after a predetermined date? I would like to share an excel (I must send it by email), but only for a limited amount of time! This shouldn't be visible to the recipient of the worksheet! Thanks Hi Alberto! I don't think that there are any easy ways but you can make life difficult. You could use conditional format: Formula Is: =DATE(2003,12,30)+14<TODAY() Format the font color the same as the background color. Don't forget to use: Format > Cells > Protection Check Hide OK Tools > Protection > P...

coding question
I'm trying to write some code that locks several controls on a form... Some of the names of the controls are numbers or have numbers in the name. I'm getting an error in VBA at run time, related to these numbers. Do I have to change the names of the controls, or is there a work- around? thanks, alex Enclose the name in square brackets. For example,if the text box is named 5, use: Me.[5].Locked = False -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. &qu...

Model/View/Controller (MVC) questions and intergration with CWnd/MFC/ATL
Hi, I'm trying to understand the MVC architecture, but I have a hard time when it comes to the integration into a CWnd in a MFC Doc/View environment. I don't want to discuss, wether the MVC architecture is superior to the DV architecture or not. There are enough discussions out there about this issue. One good start is the following article http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=utf-8&selm=6isk35%24ttv%241%40nnrp1.dejanews.com The stingray toolkit seems to be the only framework that is activly using the MVC pattern and does a good integration into...

ComboBox
Hi. I'm an ASP.NET developer working on an Access application. In ASP.NET I'm used to a dropdownlist's DataTextField property (text that displays in the control) and DataValueField property (the value tied to each text item). In Access, how can I set both a text and value property to a combobox? Specifically I have a ComboBox control that I need to have display text field "UserName" and have a value named "UserID". Is this possible? I am using a combobox's rowsource to write the select statement which populates the combobox. Can I set both a text...

"empty" picklist value
Hi All, I'm trying to create a picklist which has an empty element. This could be used whether the user does not know which value fit to the data. Eg. I'd like to create a picklist with the values: " ",Black, White, Yellow. I'm not able to do this becasue a value should not be empty. Have anyone feced to the same problem? Is there a sophisticated solution? Thanks. Why not just use the value "unknown"? Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Wed, 6 Apr 2005 05:43:05 -0700, "...

Bubble Chart question
Is it possible to create a bubble chart with two series that share the same X and Y values. For example: X Y values1 values2 10 11 300 0 14 20 150 0 20 5 0 100 30 30 0 40 I obviously haven't had any success <grin>. I can create a bubble graph with the x y and value1. But I can't figureout how to get values2 with the same x and y. I can create an entirely seperate x y and values2 and add as a new series, but my data is not constructed that way and it would be a pain to re-arrange. Thanks for any suggestions...

Negative value in Excel
I would like value in a certain rage of cells to always be negativ without adding minus sign when enter the value. Does anyone know how t set this??? Many thanks -- Message posted from http://www.ExcelForum.com Adhanya, One solution is to put the values in straightaway (as positive), and simply make them look as though they're negative. Select the cells, Format, Cells, Number, Custom, and use something like: -General -0.00 Then in any formulas that refer to them, negate them so they will be treated as negative. Or an event macro can negate them as they're entered. -- Earl Kios...