New Terms for Google Docs

If you frequent the Docs forum or Drive forum you might have noticed a few new terms for describing Google Docs.  With the introduction of Drive it has been difficult or many users to figure out what exactly is going on and what exactly everybody is talking about. You might have also noticed that "Documents" in your upper black bar now says "Drive."  I hope my guide below can help with the rebranding!

Google Drive (or Drive)- This is the all encompassing term for cloud storage of files using Google's service.  You can use this term by saying things like "Hey, my computer died, but luckily I have everything saved in my Drive."  Drive also has various apps associated with it that allow you to collaborate in real-time. These files are in a Google format and must be edited in a browser.  Currently, only Docs (see below) allow for offline editing and offline editing is not supported on mobile.

Doc(s) - Formerly known as Documents.  This will be the most confusing term going forward as the term "Google Docs" use to encompass Documents, Spreadsheets, Presentations, and Drawings.  This now refers to the Google format for word processing.  Google Documents is now Google Docs.  A single Google Docs file is called a Google Doc.  You can use this term by saying something like "Hey, I'm sick of sending this Word file through email.  How about turning it into a Google Doc so we can collaborate in real time?" (In my mind, this is much better and how it should have been from the beginning.)

Sheet(s) - Formerly known as Spreadsheets.  A single Google Sheets file is called a Google Sheet.  Not to be confused with a tab in the Sheet.  You can use this term by saying something like "Hey, I want to use Google Sheets to plan our next vacation budget!"
   Other notes: There has traditionally been dissimilarities between Microsoft Excel and Google Sheets terms.  We will see how this goes but in the past the term "workbook" has generally been avoided.  In the Google Sheets world, the terms "tab" and "sheet" will be synonymous and while the term "Sheets" could be used in place of "workbook."   However, I am going to strongly advise that "workbook" be used to describe the collection of "tabs" or "sheets" in a single file (regardless of whether we are talking about Excel or Sheets).  The capitalization of Sheets will indicate the Drive app only and a lower-case "sheets" will refer to a "tab." A lower-cased "spreadsheet" is also synonymous with "sheets" and "tabs" but the upper-case "Spreadsheets" no longer exists and should not be used.  You might want to read that a few times.

Slide(s) - Formerly known as Presentations.  This one is an easy transition and can be used by saying something like "Hey, you know building this presentation would be much easier if we did this with Slides?"

Drawings - Drawings is still Drawings.

As you can see this will be a difficult transition but I hope we can all use the same terms when discussing Google Drive on the forums or anywhere else.

Please comment if you think anything is unclear and I can make some edits!

Note: These are just my conclusions from using Google Drive but I hope Google releases something soon explaining everything.  But for now...use this.

Cheers!

Person Dispatching and the Rectified 5-cell Simplex Pentagon

There are a bunch of wonderful apps and location-based services for managing supply lines, keeping track of door-to-door sales associates, and optimizing these paths.  But I came up with a quick model using Google Spreadsheets.

The basic idea is that you have a map with n locations that you have to visit.  The goal is to find out how many places you could go in a day based on a bunch of parameters (distance between points, average speed, etc.). So how do you do it?

The first thing I did was determine what my map would look like.  In the real world you would actually know the distances between each location and how many locations you have.  In this case I don't, so I guessed using a mathematically-pleasing design.  I decided on a orthographic projection of a rectified 5-cell pentagon, which is a complicated way of saying, in 2 dimensions, a small pentagon inside of a big pentagon.

To do the calculations you can determine distances from each points as long as you know the length of each side (T) and the length of diagonals (D) of the pentagons.  You can adjust this in I2 and I3 on each of the person tabs. Other variables you can change include average speed while traveling, amount of time spent at each location, some lag time to account for variation, and number of hours worked per day.

Once you have added all of your information you can get a forecasted number of locations to be visited per day as well as other random information.  I've also included a sensitivity analysis by varying time at location and lag time, all as percentage change from current forecast.


If you'd like to play around with if you can access the spreadsheet here and go to File>Make a copy for an editable version.

Enjoy!

Manipulating Form Data

Hi all,
   Sorry its been a while since I've posted.  To make up for it I'm doing a Hangout On Air tomorrow about manipulating Form data.  I'll post the video here afterwards.  Enjoy!

Ted J

=sumproduct() follow-up with live stream!

Thanks to those who watched live.  You should be able to see the recording below.  Enjoy!

The above video is related to the shared spreadsheet below:

Vertical Summing Examples

Functions covered include =sumproduct(), =sumif(), and =query().

Cheers!

Using =sumproduct() in Google Spreadsheets

It has been a while since I posted but I promise to have some exciting posts soon!  But for today I thought I would show a quick example of a way to flip sumif behavior.

Consider a situation where all odd number rows have names and all even number rows have a numerical value in a single column.  In my example, I either have Ted or Adam in the odd number rows.  The goal here is to find the sum of the values directly below each time Ted appears and to find the sum of the values directly below each time Adam appears.



Formula in D1: =SUMPRODUCT(N(A$1:A$19=C1),A$2:A$20)+INDEX(0,1)
Formula in D2: =ARRAYFORMULA(SUMPRODUCT(N(A$1:A$19=C2),A$2:A$20))

What does sumproduct() do?
Sum product takes up to ??? (documentation says 30 but I think it is closer to 255) ranges (in Google Spreadsheets) or 255 ranges (in Excel) and multiplies the corresponding values together and then sums up all of the values.  If my function was =sumproduct({2,3,4},{1,2,3}) the result would be 20 (2*1 + 3*2 + 4*3).  In order for the sum and multiplication to work we have to turn our criteria into a number.  Therefore we use the N() function to turn a value into 1 if the criteria is met or 0 if not.  The second step is to define the range we want to multiply the first range by and that is basically the same range we had before but shifted down a cell.  Instead of A1:A19 it becomes A2:A20.

To visualize what is going on see the tab called Visualize in the example or watch the video below.

Now that we have defined our ranges to be multiplied, the function is ready to go!  Since SUMPRODUCT must be array enabled to work we must apply an array-enabler.  I've illustrated two ways to do this depending on whether you are using Google Spreadsheets, Excel, or both.  By wrapping the whole function with arrayformula() you enable the function to do array calculation.  You can also do this by adding index(0,1) which when evaluated is always zero and has no effect on the function, but enables the array calculation for Google Spreadsheets and Excel.  Arrayformula() is not supported by Excel and you will see a #NAME? error.

Enjoy and feel free to add any questions in the comments.

Also, see the video explanation recorded as a Google Hangout On Air and feel free to make a copy of the Spreadsheet to explore.

Cheers!