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 Moderator Last seen December 27, 2022 - Jun 18, 2019 at 11:57 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 18, 2019 at 11:57 AM
Related:
- Getting multiple largest values with specified limits
- Allow multiple downloads chrome - Guide
- How to delete multiple files at once - Guide
- Photoshop multiple selections - Guide
- Which key is used to make multiple line in a single cell - Guide
- Share multiple contacts android - Guide
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
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
