Google Sheets – Converting Binary to Percentages

google sheets

I'm using a binary system to determine whether someone showed up that date or not. 1 is "yes" 0 is "no". I am looking to turn this binary into a percentage:

100/5 = 20    * 3 = 60%

but I will need to add more dates in future and I don't want to keep having to calculate it by myself.

Is there a formula for this?

Best Answer

It's better to have percentage on the left, next to the name. Otherwise, in a few months you will be scrolling way over to the right to see someone's percentage.

Use the average function, which takes the average of the values. And format the cell with this formula as a percentage, so that 0.6 is shown as 60%. (See Format > Number > Percent in the menu).

Example: =average(C2:2) is the average of all numbers in row 2, from column C to the last column (whatever it is). Put this in column B, copy it down, format as percentage and you won't need to change these formulas again.

Code

function mySplit(range, delimiter) {
  var output = [], maxCol = range[0].length;
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var colCounter=0, counter=0;
    for(var j=0, jLen=range[0].length; j<jLen; j++) {
      if(range[i][j] == "") {
        colCounter++;
      } else {
        if(range[i][j].split(delimiter).length > 1) {
          counter++;
        }
      }
    }
    output.push(counter + "/" + (maxCol - colCounter));
  }  
  return output;  
}

Screenshot

enter image description here

Example

I've created an example file for you: Certain Text Characters
If you copy the file, then you can access the code. Add the code into your existing sheet by going to Tools\Script editor in the main menu. Paste the code and save.

Google Sheets – Time Tracking Methods

I made a simple version of your problem you can play around with: Make a copy of the sheet

There are basically two things to do:

1. Get the duration for each visit

For that I added a Column with an arrayforumla (so we don’t have to copy it in every row) that basically looks if we are in a “Leave” row and if so, gives us the time difference between the leave and the arrival before that.

=ArrayFormula(
    IF(
        A2:A="Left";
        TO_PURE_NUMBER(B2:B) - TO_PURE_NUMBER(B1:B);
    ) 
)

(expanded for better readability

(Note: Your table needs to be well formated for that, meaning Leave and Arrival should always alternate)

Now you already got the duration for each visit.

Aggregate for each day

To make this simpler, lets add a Col to our sheet, that figures out the day without time for each row. Like this:

=ArrayFormula(IF(ISBLANK(B2:B);;INT(B2:B)))

The important part is the INT(B2:B) which converts your datetime to a date

Now I would recommend to create another sheet and automatically generate a day overview there, by simply putting a query in A1.

=query(
    Log!A1:D;
    "select C, sum(D) where A = 'Left' group by C label C 'day', sum(D) 'total time'"
)

That’s it! Here is the sheet.