Getting multiple largest values with specified limits
Closed
M
-
Updated on Jun 20, 2019 at 11:53 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 - Jun 18, 2019 at 11:57 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 - Jun 18, 2019 at 11:57 AM
Related:
- Getting multiple largest values with specified limits
- Based on the values in cells b77 ✓ - Excel Forum
- How to make multiple selections in photoshop - Guide
- Can you log into instagram on multiple devices - Instagram Forum
- Mpc hc multiple instances - Guide
- Based on the values in cells b77 b88 - Excel Forum
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
December 27, 2022
555
Jun 18, 2019 at 11:57 AM
Jun 18, 2019 at 11:57 AM
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
