get the average of a range of both numbers and text

readme.md

#A custom function for Google Spreadsheets

This function allows you to find the average of a range of numbers.

It will also take into account the words "Complete", "Incomplete", and "Absent". Completes are averaged as 100s. Incompletes are averaged as 0s. Absent is disregarded entirely so it won't factor into the average.

averageCompletes

/*Average of text fields */
function AVGCOMPLETES(arr) {
  //declare the array that will be used to get the average
  //push 100 to the array if the field is 'complete' and 0 to the array if the field is 'incomplete'
  var averageArray = [];
  for(i = 0; i < arr[0].length; i++) {
    if(typeof arr[0][i] === 'string') {
      if(arr[0][i].toLowerCase() === 'complete') {
        averageArray.push(100);
      } else if(arr[0][i].toLowerCase() === 'incomplete') {
        averageArray.push(0);
      }
    } else {
      averageArray.push(arr[0][i]);
    }
    
  }
  
  //average all the values in the array
  var length = averageArray.length;
  var average = averageArray.reduce(function(first, second) {return first + second;}) / length; 
  
  //return the rounded average
  return Math.round(average);
}