Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates

Hi folks,

I have 2 rows of numbers,

top row 100 110 120 110 120
2nd row 6 5 5 6 6

i need to find the highest number i the top row, which is easily done with
=max(a1:e1)

that would go into a cell where i want the results displayed. I then need to
check the cells and see which one had the high number and then get the
number directly below it. eg i would want 120 in my first results cell and
then 6 in the 2nd results cell. As you can see i have 2 lots of 120 in row
1, but have 2 different numbers in row 2, and need the highest of the 2
possibles displayed. So Ultimately i want it to look like this.

row 1 | 100 | 110 | 120 | 110 | 120
row 2 | 6 | 5 | 5 | 6 | 6

best | 120 | 6

i think a hlookup may do the job but my grasp of working out the formulas is
as good as my selecting the right lotto numbers.
Thanks for any help :)


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates

Hi!

Suppose you have your max formula in G1:

G1: =MAX(A1:E1)

To get the max of the max use this formula entered as an array using the key
combination of CTRL,SHIFT,ENTER:

=MAX(IF(A1:E1=G1,A2:E2))

Biff

"John" wrote in message
...
Hi folks,

I have 2 rows of numbers,

top row 100 110 120 110 120
2nd row 6 5 5 6 6

i need to find the highest number i the top row, which is easily done with
=max(a1:e1)

that would go into a cell where i want the results displayed. I then need
to check the cells and see which one had the high number and then get the
number directly below it. eg i would want 120 in my first results cell and
then 6 in the 2nd results cell. As you can see i have 2 lots of 120 in row
1, but have 2 different numbers in row 2, and need the highest of the 2
possibles displayed. So Ultimately i want it to look like this.

row 1 | 100 | 110 | 120 | 110 | 120
row 2 | 6 | 5 | 5 | 6 | 6

best | 120 | 6

i think a hlookup may do the job but my grasp of working out the formulas
is as good as my selecting the right lotto numbers.
Thanks for any help :)



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates

Biff.

Thanks mate, works an absolute treat. Much appreciated.

John


"Biff" wrote in message
...
Hi!

Suppose you have your max formula in G1:

G1: =MAX(A1:E1)

To get the max of the max use this formula entered as an array using the
key combination of CTRL,SHIFT,ENTER:

=MAX(IF(A1:E1=G1,A2:E2))

Biff

"John" wrote in message
...
Hi folks,

I have 2 rows of numbers,

top row 100 110 120 110 120
2nd row 6 5 5 6 6

i need to find the highest number i the top row, which is easily done
with =max(a1:e1)

that would go into a cell where i want the results displayed. I then need
to check the cells and see which one had the high number and then get the
number directly below it. eg i would want 120 in my first results cell
and then 6 in the 2nd results cell. As you can see i have 2 lots of 120
in row 1, but have 2 different numbers in row 2, and need the highest of
the 2 possibles displayed. So Ultimately i want it to look like this.

row 1 | 100 | 110 | 120 | 110 | 120
row 2 | 6 | 5 | 5 | 6 | 6

best | 120 | 6

i think a hlookup may do the job but my grasp of working out the formulas
is as good as my selecting the right lotto numbers.
Thanks for any help :)





  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates

You're welcome. Thanks for the feedback!

Biff

"John" wrote in message
...
Biff.

Thanks mate, works an absolute treat. Much appreciated.

John


"Biff" wrote in message
...
Hi!

Suppose you have your max formula in G1:

G1: =MAX(A1:E1)

To get the max of the max use this formula entered as an array using the
key combination of CTRL,SHIFT,ENTER:

=MAX(IF(A1:E1=G1,A2:E2))

Biff

"John" wrote in message
...
Hi folks,

I have 2 rows of numbers,

top row 100 110 120 110 120
2nd row 6 5 5 6 6

i need to find the highest number i the top row, which is easily done
with =max(a1:e1)

that would go into a cell where i want the results displayed. I then
need to check the cells and see which one had the high number and then
get the number directly below it. eg i would want 120 in my first
results cell and then 6 in the 2nd results cell. As you can see i have 2
lots of 120 in row 1, but have 2 different numbers in row 2, and need
the highest of the 2 possibles displayed. So Ultimately i want it to
look like this.

row 1 | 100 | 110 | 120 | 110 | 120
row 2 | 6 | 5 | 5 | 6 | 6

best | 120 | 6

i think a hlookup may do the job but my grasp of working out the
formulas is as good as my selecting the right lotto numbers.
Thanks for any help :)







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
repeat a formula same column same number of rows apart steveo Excel Discussion (Misc queries) 2 July 9th 06 04:34 AM
Conditional Format as a MACRO Gunjani Excel Worksheet Functions 3 March 29th 06 05:22 PM
Add total number of rows (text) in a column rostroncarlyle Excel Worksheet Functions 1 December 15th 05 07:25 AM
creating a bar graph Johnfli Excel Discussion (Misc queries) 0 October 26th 05 08:16 PM
Running total w/2 columns - Excel Anna / Ideal Excel Worksheet Functions 14 August 10th 05 04:28 PM


All times are GMT +1. The time now is 09:32 AM.

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

About Us

"It's about Microsoft Excel"