A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

select max value from row of data and column containing max value



 
 
Thread Tools Display Modes
  #1  
Old June 29th 09, 06:14 PM posted to microsoft.public.excel.worksheet.functions
Jesse
external usenet poster
 
Posts: 35
Default 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
Ads
  #2  
Old June 29th 09, 06:26 PM posted to microsoft.public.excel.worksheet.functions
NBVC[_56_]
external usenet poster
 
Posts: 1
Default 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  
Old June 29th 09, 07:00 PM posted to microsoft.public.excel.worksheet.functions
Shane Devenshire[_2_]
external usenet poster
 
Posts: 3,346
Default 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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Select last data in column for a graph ASTDan Charts and Charting in Excel 3 January 9th 09 03:16 AM
How do you select all text/data within all the cells of a column? VisionsIC Excel Discussion (Misc queries) 3 December 20th 08 02:36 AM
how do I select different data in a filtered column in excel 2003? Manisha Excel Discussion (Misc queries) 1 May 2nd 08 01:55 PM
Shortcut to select column with data in previous column TJAC Excel Discussion (Misc queries) 1 July 10th 07 06:12 PM
In Excel: select the last 20 rows of data in a column Tad Blanchard Excel Worksheet Functions 2 November 25th 05 09:35 AM


All times are GMT +1. The time now is 02:04 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.