How to determine the value - 18 Dec?

[Eric]

Does anyone have any suggestions on how to determine the value?
There is a list of value under column A
36,36,42,42,42,48,48,48,47,47,25,25,25,25
I would like to determine the largest value under the lists, which is 48 in
cell B1
I would like to determine the second largest value under the lists, which is
not equal to 48. It should return 47 in cell B2
I would like to determine the thrid largest value under the lists, which is
not equal to largest and second largest. It should return 42 in cell B3
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Max]

Source numbers in A1 down
In B1: =IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",A1))
In C1: =IF(ROW()>COUNT(B:B),"",LARGE(B:B,ROW()))
Copy B1:C1 down to cover the max expected extent of source data. Minimize
col B. Col C will return exactly what you seek.

P/s: Don't you think its high time you changed the subject line to better
reflect each of your queries. As-is its becoming a bit of a monotone.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik

 

[Socko]

You can simply use the following function
for highest number =LARGE(A:A,1)
for second highest =LARGE(A:A,2)
and so on

LARGE Worksheet Function
To return the n th largest value in a set, use worksheet function
LARGE to return 1st highest, 2nd, 3rd, ... and so on.

Syntax
LARGE(address of array,n )
n= the position (from the largest) in the array or cell range of data
to return.

I hope this helps.

Selva V Pasupathy
For more on Excel, VBA, & other resources
Please visit http://socko.wordpress.com/

 

[Max]

You can simply use the following function

for highest number =LARGE(A:A,1)
for second highest =LARGE(A:A,2)

I doubt the above applies here. Re-read the OP's post carefully. What's key
here are the multiple ties in the various source nums, and OP wants to
extract only the uniques in descending order. The simple use of LARGE as you
suggest simply fails.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---

 

[Ron Rosenfeld]

Does anyone have any suggestions on how to determine the value?
There is a list of value under column A
36,36,42,42,42,48,48,48,47,47,25,25,25,25
I would like to determine the largest value under the lists, which is 48 in
cell B1
I would like to determine the second largest value under the lists, which is
not equal to 48. It should return 47 in cell B2
I would like to determine the thrid largest value under the lists, which is
not equal to largest and second largest. It should return 42 in cell B3
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

Enter this formula in B1, and fill down as far as required.

Vals is a defined name with this formula:

=OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1!$A:$A))

(or it could be an absolute reference to the range that contains values)

=LARGE(IF(FREQUENCY(Vals,Vals)>0,Vals),ROWS($1:1))

So as not to return errors, you could use one of these formulas:

Excel 2007:

=IFERROR(LARGE(IF(FREQUENCY(Vals,Vals)>0,Vals),ROWS($1:1)),"")

Prior versions:

=IF(ISERR(LARGE(IF(FREQUENCY(Vals,Vals)>0,Vals),ROWS($1:1))),"",
LARGE(IF(FREQUENCY(Vals,Vals)>0,Vals),ROWS($1:1)))
--ron

 

[T. Valko]

Try these:

Enter this formula in B1:

=MAX(A1:A14)

Enter this array formula** in B2 and copy down until you get blanks:

=IF(OR(B1=MIN(A$1:A$14),B1=""),"",MAX(IF(A$1:A$14<B1,A$1:A$14)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)