I need retrieve the number of maximun values in my range.

For example the data like following:

20, 90, 40,80,95,100

I need the top 3 numbers are 100,95,90.

The MAX function only return the highest value.

Are there any functions available to retrieve more than one maximum?

Any information is great appreciated,

Hi,
=LARGE(A1:A12,1) returns the largest value within A1:A12
=LARGE(A1:A12,2) returns the second largest value within A1:A12
....
say the largest value is 20, the second largest value is 10 and say that 10
appears twice then LARGE( ...,3) will also return 10.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Souris" wrote:

I need retrieve the number of maximun values in my range.

For example the data like following:

20, 90, 40,80,95,100

I need the top 3 numbers are 100,95,90.

The MAX function only return the highest value.

Are there any functions available to retrieve more than one maximum?

Any information is great appreciated,

Thanks for the information,
I need write 3 times =LARGE(....), I f I need the largest 3 values.
How about I need the largest 10 values?
Are there any way to write one function to have more than one values returned?

Thanks again,

"sebastienm" wrote:

Hi,
=LARGE(A1:A12,1) returns the largest value within A1:A12
=LARGE(A1:A12,2) returns the second largest value within A1:A12
...
say the largest value is 20, the second largest value is 10 and say that 10
appears twice then LARGE( ...,3) will also return 10.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Souris" wrote:

I need retrieve the number of maximun values in my range.

For example the data like following:

20, 90, 40,80,95,100

I need the top 3 numbers are 100,95,90.

The MAX function only return the highest value.

Are there any functions available to retrieve more than one maximum?

Any information is great appreciated,

You could use something like the following. LargeK returns the array of
values from the largest to the k-th largest number within the range or array.
'----------------------------------------
Public Function LargeK(Arr As Variant, k As Long) As Variant
Dim v As Variant, i As Long
ReDim v(1 To k)

For i = 1 To k
v(i) = Application.WorksheetFunction.Large(Arr, i)
Next
LargeK = v
End Function
'----------------------------------------------------------

Regards,
Sébastien
<http://www.ondemandanalysis.com


"Souris" wrote:

Thanks for the information,
I need write 3 times =LARGE(....), I f I need the largest 3 values.
How about I need the largest 10 values?
Are there any way to write one function to have more than one values returned?

Thanks again,

Thanks millions,



"sebastienm" wrote:

You could use something like the following. LargeK returns the array of
values from the largest to the k-th largest number within the range or array.
'----------------------------------------
Public Function LargeK(Arr As Variant, k As Long) As Variant
Dim v As Variant, i As Long
ReDim v(1 To k)

For i = 1 To k
v(i) = Application.WorksheetFunction.Large(Arr, i)
Next
LargeK = v
End Function
'----------------------------------------------------------

Regards,
Sébastien
<http://www.ondemandanalysis.com


"Souris" wrote:

Thanks for the information,
I need write 3 times =LARGE(....), I f I need the largest 3 values.
How about I need the largest 10 values?
Are there any way to write one function to have more than one values returned?

Thanks again,

Souris -

Here is a short example that you can extend: To return the three largest
values, select three adjacent cells in the same row, type
=LARGE(A1:A12,{1,2,3}) and holding down the Control and Shift keys press
Enter, thus "array-entering" the function. If done correctly, the entire
entry will be surrounded by curly brackets in the formula bar.

You can extend the approach to return more values, and you can enter the
function in a column by using TRANSPOSE, e.g.,
=LARGE(C1:C20,TRANSPOSE({1,2,3}))

- Mike
www.mikemiddleton.com

"" wrote in message
...
Thanks for the information,
I need write 3 times =LARGE(....), I f I need the largest 3 values.
How about I need the largest 10 values?
Are there any way to write one function to have more than one values
returned?

Thanks again,

"sebastienm" wrote:

Hi,
=LARGE(A1:A12,1) returns the largest value within A1:A12
=LARGE(A1:A12,2) returns the second largest value within A1:A12
...
say the largest value is 20, the second largest value is 10 and say that
10
appears twice then LARGE( ...,3) will also return 10.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Souris" wrote:

I need retrieve the number of maximun values in my range.

For example the data like following:

20, 90, 40,80,95,100

I need the top 3 numbers are 100,95,90.

The MAX function only return the highest value.

Are there any functions available to retrieve more than one maximum?

Any information is great appreciated,