Google-sheets – Timestamps in scatterplot

google sheetsgoogle-sheets-charts

I have a Google spreadsheet with two sheets. The first one is for data input from my Android phone using Tasker. Input sheet looks like this:

clock   duration
16,25   29
6,45    23
16,3    35

where "clock" indicates time of departure (from work or home) and "duration" indicates travel time from home to work or vise versa.

Another sheet has these clock values converted to Google spreadsheet time format, and the same corresponding durations:

CLOCK   DURATION
 16:25  29
 06:45  23
 16:30  35

Now I want to visualize these in scatter plot. Problem is that the sheet is automatically updated every time I leave from home or work, so the input lines alternates between around 7 and around 16 and when I plot these I get the values in the order they are on the sheet like so:

But what I want is that the x-axis is constant say from 6 to 18 and the values plotted in this range. This works with normal numbers but time values just appear in the order they are in the sheet.

Best Answer

Maybe you have "Treat labels as text" checked for the Horizontal axis.

This has 04:00 and 18:00 added to show more labels on the x-axis, and minor grid lines added.

Related Solutions

Google-sheets – Daily calculation and record insertion in Google Spreadsheet

With the following Google Apps Script you can grab the LIBOR value and added it to a new sheet. Goto the script editor:
enter image description here

and add this script:

UPDATE 25-01-2013
I've noticed that the trigger occasionally troughs a NA#. Most probably because the GoogleFinance formula takes some time to get its info. Therefore I revised the snippet a bit, to have a sleep function added to it:

NEW

function myCustomCalculation() {
  //set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get both the sheets 
  var sh0 = ss.getSheets()[0], sh1 = ss.getSheets()[1];

  // sleep
  Utilities.sleep(6000);

  // get cell value
  var cell = sh0.getRange("D2").getValues()[0];

  // add libor value to other sheet  
  sh1.appendRow([new Date(), cell]);
}

OLD

function myCustomCalculation() {
  //set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // flush the spreadsheet before using it  
  SpreadsheetApp.flush();

  // get both the sheets 
  var sh0 = ss.getSheets()[0], sh1 = ss.getSheets()[1];

  // get cell value from sheet[0]
  var cell = sh0.getRange("D2").getValues()[0];

  // add libor value to other sheet  
  sh1.appendRow([new Date(), cell]);
}

The clue to the story is to create a time based trigger:
enter image description here

and set the trigger to execute at 5-6 pm:
enter image description here

See the example file I´ve created: LIBOR. I've added an extra menu item, so that you can execute the script this way.

Google-sheets – Generating charts from Google Forms data via Google Sheets

You could concatenate the strings, so that the row Male | City A is represented as MaleCity A, and count the occurrences of those strings.

Given that column B contains either Male or Female, and column C contains either City A or City B, you could count males/females in each city:

Number of males in City A: =COUNTIF(ARRAYFORMULA(CONCAT(B2:B;C2:C)); "MaleCity A")
Number of females in City A: =COUNTIF(ARRAYFORMULA(CONCAT(B2:B;C2:C)); "FemaleCity A")
Number of males in City B: =COUNTIF(ARRAYFORMULA(CONCAT(B2:B;C2:C)); "MaleCity B")
Number of females in City B =COUNTIF(ARRAYFORMULA(CONCAT(B2:B;C2:C)); "FemaleCity B")

The percentage of males/females in each city can then easily be calculated.

See the example spreadsheet I set up.

Best Answer

Related Solutions

Google-sheets – Daily calculation and record insertion in Google Spreadsheet

Google-sheets – Generating charts from Google Forms data via Google Sheets

Related Topic