MAX FUNCTION

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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,
 
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>
 
Thanks millions,



sebastienm said:
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>
Click to expand...
 
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
 
Back
Top