alert('Please use chrome browser on phone or pc. Doesn\'t work well on solo learn app'); alert('Top 5 solo learners in spread sheet 1.0'); alert('Features\n1.Calculate the total of sample data by the SUM formula e.g \' =SUM(A1:A5)\' similar to excel'+'\n2.Calculate sum of any two digits eg \'=SUM(B1,160)\' or \'=SUM(12,24)\' similar to excel' + '\n3.Get the answer for expressions in cells eg. \'23/45*67+(467+478)\''+ 'More features coming soon.For basic expressions cell border turns white, for summing up like in feature 1, cell border turns green. Use evaluate to compute. Leave a like if you like....'); var n; var total; var alphabet; $(function() { example(); $('#btn').click(function(){ size = parseInt( $('#size').val()); launch(); }); $('#eval').click(function(){ refresh();//refresh the input colors evaluate(); }); $('#clear').click(function(){ refresh(); clear(); }); }); function launch(){ size +=1; if(size > 10){ size = 11;//max is 10 } tableCreate(); labelColumns(); labelRows(); $('#btn').remove(); $('#size').remove(); } function tableCreate(){ var container = document.getElementById('container'); var tbl = document.createElement('table'); for(var i = 0; i < size; i++){ var tr = tbl.insertRow(); for(var j = 0; j < size; j++){ var td = tr.insertCell(); var input = document.createElement('input'); input.setAttribute('id',i+""+j);//give every cell a unique id td.append(input); } } container.append(tbl); } function labelRows(){ alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split(''); for(var i = 1; i < size; i ++){ $('#0'+i).val(''+ alphabet[i - 1]); $('#0'+i).css('color','black'); $('#0'+i).css('background','yellow'); $('#0'+i).attr('disabled',true); } } function labelColumns(){ $('#00').css('width','50px'); $('#00').css('background','black'); $('#00').css('border-color','black'); $('#00').attr('disabled',true); for(var i = 1; i < size; i ++){ $("#"+ i +"0").val(""+i); $("#"+ i +"0").css('width','50px'); $("#"+ i +"0").css('color','black'); $("#"+ i +"0").css('background','yellow'); $("#"+ i +"0").attr('disabled',true); } } function getTotal(){ total = 0; n = 0; for(var i = 1; i < size; i ++){ for(var j = 1; j < size; j++){ if (/^\d+$/.test($('#'+ i+""+j).val())) {//use regex to check if a cell contains a number total += parseInt(math.eval($('#'+ i+""+j).val())); n += 1; // Contain numbers only } else { // Contain other characters also if($('#'+ i+""+j).val().includes("=")){ //this is a SUM formulae try{ total += getFormulaeAnswer('#'+ i+""+j); n += 1; }catch(e){ alert("Error:"+ e); } } else if($('#'+ i+""+j).val().includes("+")|| $('#'+ i+""+j).val().includes("-") || $('#'+ i+""+j).val().includes("*")|| $('#'+ i+""+j).val().includes("/")){ try{ total += parseInt(math.eval($('#'+ i+""+j).val())); $('#'+ i+""+j).val( parseInt(math.eval($('#'+ i+""+j).val()))); $('#'+ i+""+j).css('border-color','#fff9c4'); $('#'+ i+""+j).css('color','#fff9c4'); n += 1; }catch(e){ alert("Error:"+ e); } } } } } $('#total').text("Total:"+total); } function getAverage(){ $('#average').text("Average:"+ (total/n).toFixed(2)); } function evaluate(){ getTotal(); getAverage(); } function getCell(cell){ var cell_name = cell.split(''); var i = alphabet.indexOf(cell_name[0].toUpperCase()); var j = cellname[1]; return "#"+i + ""+j; //return the id ready for working on in jquery :) } function getFormulaeAnswer(id){ if( $(id).val().toString().includes(',')){ var var1 ,var2; var func = $(id).val().toString(); var a = func.split('=SUM('); var b = a[1]; var c = b.split(","); var d = c[0];//this is A1 var e = c[1].split(")"); var f = e[0];//this is A5 var1 = getNum(c); var2 = getNum(e); var sum = parseInt(var1) + parseInt(var2); $(id).val(''+sum); $(id).css('border-color','#fff9c4'); $(id).css('color','#fff9c4'); return sum; }else if ( $(id).val().toString().includes(':')){ var total = 0; var func1 = $(id).val().toString(); var a1 = func1.split('=SUM('); var b1 = a1[1]; var c1 = b1.split(":");//This is the reason I had to copy paste this part from the top var d1 = c1[0];//this is A1 var e1 = c1[1].split(")"); var f1 = e1[0];//this is A5 var cell_id_1 = d1.split(''); var cell_id_2 = f1.split(''); if(cell_id_1[0] == cell_id_2[0]){ //this is vertical addition for(var i = getCellId(d1); i <= getCellId(f1); i += 10){//step it up by 10 total += parseFloat($('#'+i).val()); } $(id).val(total); $(id).css('border-color','#4ef442'); $(id).css('color','#4ef442'); n -= 1;//To reduce n by 1 becuase we don't want it affecting our averge return 0; }else{ //this is horizontal addition for(var i = getCellId(d1); i <= getCellId(f1); i++){//step it up by 1 total += parseFloat($('#'+i).val()); } $(id).val(total); $(id).css('border-color','#4ef442'); $(id).css('color','#4ef442'); n -= 1;//To reduce n by 1 becuase we don't want it affecting our averge return 0; } } } function getNum(arr){ if(/^\d+$/.test(arr[0]) ){//if it's only a number return arr[0]; }else{//if it contains a cell id var g = arr[0].split('');//eg A1 var g1 = g[0];//first part eg A var g2 = g[1];//second part eg 1 var g3 = alphabet.indexOf(g1.toString());//index of the alphabet var index = g2 + ''+ (parseInt(g3)+ 1) ; // console.log("cell id is "+ g + " first part is "+ g1 + " second part is " + g2 + " index is "+ index); return math.eval($('#'+index).val()); } } function getCellId(cell_name){ //for example A1 var cell = cell_name.split(''); var p1 = cell[0];//A var p2 = cell[1];//1 var index_p1 = alphabet.indexOf(p1.toString());//returns 0 for A return parseInt(p2 + ''+ (parseInt(index_p1)+ 1)); //for cell name A1 it should return 11 } function clear(){ for(var i = 1; i < size; i ++){ for(var j = 1; j < size; j++){ $('#'+ i+""+j).val(''); } } } function refresh(){ for(var i = 1; i < size ; i ++){ for(var j = 1; j < size ; j ++){ $('#'+i + ''+ j).css('border-color','yellow'); $('#'+i + ''+ j).css('color','yellow'); } } } //A simple analysis of TOP 5 SOLO LEARNERS function example(){ size = 10; launch(); //fill in the names $('#11').val('NAMES'); $('#21').val('Nikolay Nachev'); $('#31').val('GAWEN STEASY'); $('#41').val('Deepak Gautam'); $('#51').val('NezhnyjVampir'); $('#61').val('Brains'); $('#71').val('TOTAL'); //$('#81').val('TRADITIONAL TOTAL'); //let's fill in the xp scores $('#12').val('XP'); $('#22').val('259192'); $('#32').val('144780'); $('#42').val('122443'); $('#52').val('107093'); $('#62').val('102508'); $('#72').val('=SUM(B2:B6)'); //$('#82').val('259192 + 144780 + 122443 + 107093 + 102508'); }