Home 
Search 
Today's Posts 
#1




select max value from row of data and column containing max value
I'm using Office 2003. I have a large table and I'd like to evaluate the data
and get a summary of some metrics of the data:  the name (column) with the highest value  the highest value  this seems easy with =max()  name of the 2nd highest value  2nd higest value Here's a sample of my detail data. The 4 "name" columns are the data and the 4 columns on the right is the summary I want to create with formulas. name1 name2 name3 name4 highest  value  2nd highest  value metric1 230 214 199 157 name1 230 name2 214 metric2 119 89 138 45 name3 138 name1 119 It would be great if I could format the highest value of each row in the detail data...so the 230 for metric 1 and name1 would be bold.  Jesse 
#2




select max value from row of data and column containing max value
To get name associated with Max... =Index(A$1$1,Match(Max(A22),A22,0)) To get Max =Max(A22) To get 2nd Largest: =Large(A22,2) To get name associated with 2nd largest: =Index(A$1$1,Match(Large(A22,2),A22,0)) where A11 contain the name headers... A22 begin the metrics... All formulas can be copied down  NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com)  NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111092 
#3




select max value from row of data and column containing max value
Hi,
To get the name for the max I would use =INDEX(B$1:E$1,1,MATCH(F2,B2:E2,)) because you have already found the max in F2. And for the second largest =INDEX(B$1:E$1,1,MATCH(H2,B2:E2,)) ** I would also watch out for the absolute cell references because the formulas can't be copied down unless you have them. First, since there only appear to be names on row 1 making the name bold is questionable  Name1 might be bold for line 2's data but Name2 might be highest for line 3's data? However, you can apply conditional formatting to the values. Assume your data is in A1:I100 with titles on the first row and in the first column select the rangeB2:E100 and To conditionally format your cell(s): In 2003: 1. Select the cells you want to format 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =B2=$F2 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =B2=$F2 5. Click the Format button and choose a format. 6. Click OK twice  If this helps, please click the Yes button. Cheers, Shane Devenshire "Jesse" wrote: I'm using Office 2003. I have a large table and I'd like to evaluate the data and get a summary of some metrics of the data:  the name (column) with the highest value  the highest value  this seems easy with =max()  name of the 2nd highest value  2nd higest value Here's a sample of my detail data. The 4 "name" columns are the data and the 4 columns on the right is the summary I want to create with formulas. name1 name2 name3 name4 highest  value  2nd highest  value metric1 230 214 199 157 name1 230 name2 214 metric2 119 89 138 45 name3 138 name1 119 It would be great if I could format the highest value of each row in the detail data...so the 230 for metric 1 and name1 would be bold.  Jesse 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Select last data in column for a graph  Charts and Charting in Excel  
How do you select all text/data within all the cells of a column?  Excel Discussion (Misc queries)  
how do I select different data in a filtered column in excel 2003?  Excel Discussion (Misc queries)  
Shortcut to select column with data in previous column  Excel Discussion (Misc queries)  
In Excel: select the last 20 rows of data in a column  Excel Worksheet Functions 