Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Sheets function to calculate time averages and best times
/**
* Averages time provide in formate minutes:seconds:millisec
*
* @param {range} input The value to multiply.
* @return The average of input range.
* @customfunction
*/
function AVERAGETIME(input) {
if (!input.map) //test if it is not an array
return input
var total_time = 0;
var total = input.length;
for (var i =0 ; i<total; i++){
var time = getTimeInMicroSeconds(input[i]);
total_time += time;
}
var average_time = Math.floor(total_time/total);
var microseconds = average_time % 100;
var seconds = Math.floor(average_time/100) % 60;
var minutes = Math.floor(average_time/6000);
return correctSizeString(minutes,2)+"."+correctSizeString(seconds,2)+"."+correctSizeString(microseconds,2);
}
function BESTTIME(input) {
if (!input.map)
return input;
minimum_time = Infinity;
index_minimum = -1;
for (var i=0; i<input.length; i++) {
var time = getTimeInMicroSeconds(input[i]);
if (time < minimum_time){
minimum_time = time;
index_minimum = i;
}
}
return input[index_minimum];
}
function getTimeInMicroSeconds(timeGiven) {
var time = String(timeGiven).split('.');
return parseInt(time[0])*6000 + parseInt(time[1])*100 + parseInt(time[2]);
}
function correctSizeString(time, length){
var strTime = time.toString();
while (strTime.length < length){
strTime = "0" + strTime;
}
return strTime;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.