Google Spreadsheet formula to calculate score

Question:

( by Kenny.in.da.house )

Hi all,

Need help to write a formula for a badminton scoreboard.

Logic: [Lookup] player name on the data table + if match [countif] or [sumif] player's total won/lost + total points won/lost

Attached herewith my worksheet:

I have manually calculated the results from the data table on the left side. Highlighted in RED colour cell with my question in comment.

Appreciate if anyone could shed me some light on which syntax to use and how to write the appropriate formula.

Many thanks in advance.

Cheers,

Kenny

Solution:

You have to install the VMerge script that is available in the script gallery.

Or here is the code for that (copy paste it in "Tools" menu >> "Script editor..."):

function VMerge() { 
  var maxw=l=0;
  var minw=Number.MAX_VALUE;
  var al=arguments.length ;
  for( i=0 ; i<al ; i++){
    if( arguments[i].constructor == Array )l =arguments[i][0].length ;
    else if (arguments[i].length!=0) l = 1 ;  // literal values count as array with a width of one cell, empty cells are ignored!
    maxw=l>maxw?l:maxw;
    minw=l<minw?l:minw;
  }
  if( maxw==minw) { /* when largest width equals smallest width all are equal */
    var s = new Array();
    for( i=0 ; i<al ; i++){
      if( arguments[i].constructor == Array ) s = s.concat( arguments[i].slice() )
      else if (arguments[i].length!=0) s = s.concat( [[arguments[i]]] )  
    }
  if ( s.length == 0 ) return null ; else return s        //s     
  }
  else return "#N/A: All data ranges must be of equal width!"      
}

After copying the above script, lets now see the formulas:

Here is the screenshot of the Spreadsheet:

I have inserted the following formula in Cell R4, this formula will auto populate the Column R, S, T and U:

=arrayformula(if({{1},{0},{0},{0}}; if(J4:J="","",if(J4:J<L4:L,"0","1") ) ; if({{0},{1},{0},{0}}; if(L4:L="","",if(J4:J>L4:L,"0","1") ) ;if({{0},{0},{1},{0}}; if(N4:N="","",if(P4:P>N4:N,"0","1") ) ; if(P4:P="","",if(P4:P<N4:N,"0","1") ) ) ) ) )

And the following formula in Cell W4, this formula will auto populate the Column W, X and Y:

=ArrayFormula(IF({{1},{0},{0}};if(R4:R="";"";R4:R+T4:T);IF({{0},{1},{0}};if(R4:R="";"";"-");if(S4:S="";"";S4:S+U4:U))))

And the main formula is in the Cell AB4, which will automatically fill the whole range "AB4:AJ16":

=Query(ArrayFormula(If({{1},{0},{0},{0},{0},{0},{0},{0},{0}};VMerge(E4:E19;H4:H19;E4:E19;H4:H19);If({{0},{1},{0},{0},{0},{0},{0},{0},{0}};1;If({{0},{0},{1},{0},{0},{0},{0},{0},{0}};VMerge(E4:E19;H4:H19);If({{0},{0},{0},{1},{0},{0},{0},{0},{0}};IF(VMerge(W4:W19;Y4:Y19)>1;1;0);If({{0},{0},{0},{0},{1},{0},{0},{0},{0}};IF(VMerge(W4:W19;Y4:Y19)<1;1;0);If({{0},{0},{0},{0},{0},{1},{0},{0},{0}};IF(VMerge(W4:W19;Y4:Y19)=1;1;0);If({{0},{0},{0},{0},{0},{0},{1},{0},{0}};VMerge(J4:J19;L4:L19;N4:N19;P4:P19);If({{0},{0},{0},{0},{0},{0},{0},{1},{0}};VMerge(L4:L19;J4:J19;P4:P19;N4:N19);)))))))));"select Col1,(sum(Col2)/2),sum(Col2),sum(Col4), sum(Col5), sum(Col6), sum(Col7), sum(Col8), (sum(Col7)-sum(Col8)) group by Col1 order by sum(Col4) desc label Col1 '', (sum(Col2)/2) '', sum(Col2) '',sum(Col4) '', sum(Col5) '', sum(Col6) '', sum(Col7) '', sum(Col8) '', (sum(Col7)-sum(Col8)) '' ")

the above formula will use the VMerge script that you have inserted in script editor. 

If you want to have a look at the spreadsheet, then comment on this blog post regarding it and I will share this spreadsheet with you.



I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.in/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive