Trying to write "2nd highest" score in addition to MaxScore.

Re: Trying to write "2nd highest" score in addition to MaxScore.

Holy crap! It works! With 8 columns it adds the 1+5,2+6,3+7,4+8 then displays the 2nd largest of first largest, etc!!!!

Since you put some real effort in this is there any way you could spend just a few more minutes and add some comments 're: what does what?

This type of code I'm not so familiar with. Moreso than straight formula code though! lol. Formula code, to me, is along the lines of the two accounting classes I took at University. I could never pull better than a C+. And this coming from someone that scored the 2nd highest Calc Final one semester among a couple thousand students!

Quote from Krishnakumar;790628

Probably a UDF..

Hit Alt+F11 > goto Insert > Module and paste the following code there. Then hit Alt+Q to close the VBE window.

Now in the worksheet, use the formula as MAXNTH(A1:H1,2)

VB:
Option Explicit

Function MAXNTH(NumRange As Range, Optional Nth As Long = 1)

Dim k, kk, i As Long, c As Long, r As Long

c = NumRange.Columns.Count
r = NumRange.Rows.Count

If c > 1 And r > 1 Then
MAXNTH = CVErr(xlErrNum)
Exit Function
End If


If c > 1 Then
If Nth > c Then
MAXNTH = CVErr(xlErrNum)
Exit Function
End If
k = NumRange.Parent.Evaluate("transpose(transpose(" & NumRange.Address & "))")
ElseIf r > 1 Then
If Nth > r Then
MAXNTH = CVErr(xlErrNum)
Exit Function
End If
k = NumRange.Parent.Evaluate("transpose(" & NumRange.Address & ")")
End If

With CreateObject("scripting.dictionary")
.comparemode = 1
For i = 1 To UBound(k)
kk = Split(Replace(k(i), ")", vbNullString), "(")
.Item(kk(1)) = .Item(kk(1)) + CDbl(kk(0))
Next
c = .Count
If c Then
k = .keys: kk = .items
If Nth > c Then
MAXNTH = CVErr(xlErrNum)
Exit Function
End If
c = Application.Large(kk, Nth)
r = Application.Match(c, kk, 0)

MAXNTH = c & "(" & k(r - 1) & ")"
End If
End With

End Function

Display More

Re: Trying to write "2nd highest" score in addition to MaxScore.

Thank you so much!

I don't know if you'll see this but I forgot to consider a scenerio and I'm not sure how to make your code work. Thank you, again, by the way on adding comments! That does help.

Does this change the game or concept completely?

I need to be able to (consider) the code in the calculations and not be dependent on AN, AF, AP, SP in their own columns.

I want to end up with the AVE or TOTAL of each code occurrence...
(0s are not considered)

Here's another example of source data and columns:

Columns AI AJ

82(SP) 53(AN)
112(AN) 24(AF)
125(AF) 9(AN)
139(AN) 0
108(AN) 67(AF)

--------------
Columns AU AV

82(SP) 13(AN)
96(AN) 0
85(AF) 9(AN)
99(AN) 0
67(AN) 27(AF)

--------------
Columns BA BB

100(SP) 0
99(AN) 0
50(SP) 34(AP)
96(AN) 5(AP)
99(AN) 0
--------------

Here's the result or target values:

AN Fullname AF Fullname AP Fullname SP Fullname
33(AN) 0(AF) 0(AP) 88(SP)
102(AN) 24(AF) 0(AP) 0(SP)
9(AN) 105(AF) 34(AP) 50(SP)
111(AN) 0(AF) 5(AP) 0(SP)
91(AN) 47(AF) 0(AP) 0(SP)