Getting multiple largest values with specified limits
Closed
M
-
15 Jun 2019 à 10:26
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 18 Jun 2019 à 11:57
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 18 Jun 2019 à 11:57
1 response
TrowaD
Posts
2921
Registration date
Sunday 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
18 Jun 2019 à 11:57
18 Jun 2019 à 11:57
Hi M,
Excel doesn't have a built in function for that, so let's use a user defined one:
Paste the code in a standard module, then in Excel use it like this:
=GetResult(myRange, vLow)
=GetResult(your range, from the lowest value)
Formula for D2:
=GetResult($A$1:$A$6,C2)
Best regards,
Trowa
Excel doesn't have a built in function for that, so let's use a user defined one:
Function GetResult(myRange As Range, vLow, vHigh As Long)
Dim x As Integer, mValue, cDupes As Long
mrows = myRange.Rows.Count
For x = 1 To mrows
mValue = Application.WorksheetFunction.Large(myRange, x)
If mValue >= vLow And mValue < vHigh And mValue <> cDupes Then
cDupes = mValue
GetResult = GetResult & mValue & ","
End If
Next x
GetResult = Left(GetResult, Len(GetResult) - 1)
End Function
Paste the code in a standard module, then in Excel use it like this:
=GetResult(myRange, vLow)
=GetResult(your range, from the lowest value)
Formula for D2:
=GetResult($A$1:$A$6,C2)
Best regards,
Trowa
