5 Replies Latest reply: Oct 13, 2015 11:48 AM by Jessica Tung RSS

    rangemax function - 2nd highest value

      I'm trying to sort a bunch of fields to create a stat-line of sorts from a basketball boxscore. Basically what I want it to do is return the best and 2nd best stat from a range of 4 stats. So if the 4 stats are PTS, REB, AST, STL, my formula to get the best stat would be:


      rangemax(PTS+.1,REB+.01,AST+.001,STL+.0001) --> the .1, .01 etc. is there to make sure I never have the same value in multiple fields


      What I need now is a formula to return the 2nd best stat. There must be a way, either with this function or another similar function, to put in all the fields and put ", 2" at the end as I would with a max function.


      Any suggestions?

        • Re: rangemax function - 2nd highest value
          Jessica Tung

          I am looking for such function too...Has there been a solution? Thanks!

            • Re: rangemax function - 2nd highest value

              Didn't find a function that would do it, but I managed to get it to work by nesting rangemax functions. It works well for what I'm doing, taking the 2nd best of 4 stats, but if the nesting would increase exponentially with each additional stat. But it's great for 2-4 stats. I can send my expression if you'd find it helpful.

                • Re: rangemax function - 2nd highest value
                  Jessica Tung

                  Yes thank you very much! That would be very helpful! I have 4 expressions to compare as well.

                    • Re: rangemax function - 2nd highest value

                      This is how I did it. For reference, TR, A, STL, BLK are all fields. ASSTS, REBS, STLS, BLKS are just text strings used to form a text string like "4 REBS, 3 ASSTS".


                      So basically the way it works is it looks to see if TR is the largest. If it is, then it ignores TR because I don't want the largest of the 4, I want the 2nd largest. So now that it knows TR is the largest, it finds the largest between the remaining 3 stats with nested rangemax and if/then functions. So that's the first section of the expression. The next section looks to see if A is the largest, and if it is, finds the largest of TR, STL, BLK. So it runs through all the possibilities. As I said, you could do this with more than 4 stats but each time you add one the expression grows exponentially larger. The full expression is below. Hope that's helpful!




                      if(rangemax(TR+.1,A+.01,STL+.001,BLK+.0001)=TR+.1,if(rangemax(A+.01,STL+.001,BLK+.0001)=A+.01,if(A=0,'',if(A=1,', ASST',', '&A&' ASSTS')),if(rangemax(A+.01,STL+.001,BLK+.0001)=STL+.001,if(STL=0,'',if(STL=1,', STL',', '&STL&' STLS')),if(BLK=0,'',if(BLK=1,', BLK',', '&BLK&' BLKS')))),


                      if(rangemax(TR+.1,A+.01,STL+.001,BLK+.0001)=A+.01,if(rangemax(TR+.1,STL+.001,BLK+.0001)=TR+.1,if(TR=0,'',if(TR=1,', REB',', '&TR&' REBS')),if(rangemax(TR+.1,STL+.001,BLK+.0001)=STL+.001,if(STL=0,'',if(STL=1,', STL',', '&STL&' STLS')),if(BLK=0,'',if(BLK=1,', BLK',', '&BLK&' BLKS')))),


                      if(rangemax(TR+.1,A+.01,STL+.001,BLK+.0001)=STL+.001,if(rangemax(TR+.1,A+.01,BLK+.0001)=TR+.1,if(TR=0,'',if(TR=1,', REB',', '&TR&' REBS')),if(rangemax(TR+.1,A+.01,BLK+.0001)=A+.01,if(A=0,'',if(A=1,', ASST',', '&A&' ASSTS')),if(BLK=0,'',if(BLK=1,', BLK',', '&BLK&' BLKS')))),


                      if(rangemax(TR+.1,A+.01,STL+.001,BLK+.0001)=BLK+.0001,if(rangemax(TR+.1,A+.01,STL+.001)=TR+.1,if(TR=0,'',if(TR=1,', REB',', '&TR&' REBS')),if(rangemax(TR+.1,A+.01,STL+.001)=A+.01,if(A=0,'',if(A=1,', ASST',', '&A&' ASSTS')),if(STL=0,'',if(STL=1,', STL',', '&STL&' STLS')))))