MrExcel Publishing
Your One Stop for Excel Tips & Solutions

HELP WITH MAX VALUE AND SECOND MAX VALUE IN A NON ADJACENT LIST


Posted by Jiminey Dehey on November 06, 2001 10:00 AM

Help.......I am pulling my hair out trying to get a formula to tell me the max value and second max value in a group of non-adjacent cell on another sheet in the workbook. I tried =MAX(sheet2!al,sheet2!a3 etc.) and that tells me the max value but i need the second largest value also. I tried =LARGE(sheet2!al,sheet2!a3, etc.) but i get an error message........can someone help porfavor.
Jim


Posted by Juan Pablo on November 06, 2001 10:08 AM

If you have for example:

A1 = 1
A2 = 2
A4 = 3
A5 = 4

And you want the top two max you can use:

=LARGE((A1,A2,A4,A5),1) for the max
=LARGE((A1,A2,A4,A5),2) for the second one

Juan Pablo

Posted by Mark W. on November 06, 2001 10:08 AM

The LARGE worksheet function only accepts 2
arguments separated by a single comma. The 1st
is an array (or cell range) and the 2nd is the
kth largest value. Consider creating a defined
name for the discontigous range and using it as
the 1st argument.

Posted by Juan Pablo on November 06, 2001 10:19 AM

Mark, my suggestion works... try it (It's still two arguments) (NT)

Posted by Mark W. on November 06, 2001 10:45 AM

Yep, just providing alternatives... (nt)