Counting Unique Values of a Data Set

Here is a quick How-To from the Spreadsheets forum.  There are a few ways to do this in Google Docs but I wanted to highlight the use of the =query() function which uses GViz.  See these quick links for Google's Documentation:
Using =query()
Google Visualization API Query Language

I know "API" and "Visualization" and "Query Language" sound scary but they really aren't!  In this quick example I'll show you how to sum up data for unique values!

Consider the following data: Ted 5, Yogi 6, Adam 9, Ted 7, Adam 11, Yogi 13, Ted 6.
This example is meant for much larger data sets where there could be many different names of people with many different data points. Basically, the query() function will help us sum up the numbers next to each person's name by person and sort by the highest sum.


Breakdown of function from the center of the nest:
=query(A2:B8,"Select sum(B) pivot (A)") : This is the query used to collect the data that I would like. A2:B8 indicate the range of the data I'm going to query over. "Select sum(B) pivot(A)" says that I want to select the sum of column B by the values in column A. In spreadsheets world, this is basically a pivot table where you pivot on on the unique values from column A. This function alone puts the unique values (names) as their own column and puts the sum under it. However, most of the time it is easier to view this as the user in the post requested. See the Just query() tab above.


=transpose(query(A2:B8,"Select sum(B) pivot (A)")): This transposes the table. Basically it turns columns into rows and rows into column. See the transpose(query()) tab.


=sort(transpose(query(A2:B8,"Select sum(B) pivot (A)")),2,FALSE): The final step sorts the table by the summed column. The 2 indicates the second column in the table is the column to sort by and FALSE indicates to sort in descending order.

Happy Holidays and the coming weeks

Wow. I realized I posted ONCE in November. Not good.  Anyways, a lot has been going on with Docs and I hope to post more soon!  The past couple of weeks have been pretty crazy but I plan on doing a few more analysis posts about the forum itself, the future of Google Docs and try to apply some of what has been going on to a few models about change.  It would be really great to help users learn how to be the most resilient Google product users!  I do think some of this learning is going on by newer users but I hope a few of my insights will help others see their use of Google Docs from a new light.

However, I probably won't publish these posts for a few weeks (and hopefully from my new Chromebook!).  For now, enjoy this image I made using G+. Happy Holidays!!

The New Presentations Editor / Prelude to "The Google Way"

Just as a preface...I am slightly annoyed as I write this post...

The new Presentations editor has been available as an opt-in feature for the past few weeks now.  A lot of users have been posting about their experience so far but I wanted to make a few quick comments about what I've seen on the forums recently.

I am very surprised by the amount of negativity I've seen recently.  The new look for the Docs List has been out for 3 months, the new looks for all parts of the Docs Suite have been available for a while depending on which part of the suite you are using, and Google's global UI change is also being slowly applied across Google products.  For Docs, the newest roll out is the new Presentation editor.  One thing I don't users fully realize is that this is currently an opt-in feature.  This means that you don't have to use it and you have the option of using the old Presentations editor.  This is the Google way of doing things.  I have a post about my understanding of the Google way and whether or not I subscribe to it coming soon.  But for now, I want to make this point:

The new Presentation's editor is not in its final form and there is no reason to get worked up over a feature that isn't working correctly.  You are not being forced into using it hence the reason for the opt-in feature.  This is Google's way of a final testing for a new product.  They fully expect things not to work correctly and hope that users bring these issues to the forefront by way of the forums.  This is simply a preview of what the new editor is going to be like once the old version is no longer supported.  I think we can all agree that the new editor is a step up from the old editor.

My thinking about the new Presentations editor also applies to the newer Docs List.  This has significantly improved since the new Docs List was presented in early August.  While I have no idea how long it will take before the classic view is no longer supported, it is important to note that it has been three months.  Serious Docs users should be trying to get used to the new look because soon this will be the only look for the Docs List.

Google is in the business of pushing out innovative features that might not be completely bug free.  If they sat around trying to make everything perfect then the innovation would be curbed.

As a user of many Google products on a daily basis, I provide the following advice.  Get to know the product you are using as well as you can.  Be open to the idea that things will change and that Google does A LOT of user experience research to find out where they should take the products.  When something new comes to a product you use, try to embrace it because most likely what you are seeing is the future of the product.  Also, it will be to your advantage to provide feedback via the links you can find in the product or on the Help Forums.  I'm not sure about the other forums but I know the Docs TCs do their best to be on top of the forums, on top of issues that users are saying, and also provide our own feedback to the Guides. At least in Docs, the Guides truly respect our opinions and trust us to represent the users.

I don't mean to be negative but I feel that the mood of the Docs forum has been inappropriately negative lately.  It is much easier to help someone who comes to the forums who is willing to be helped as opposed to someone who comes to the forums to just complain.

Disclaimer:  The views represented here are my own are not necessarily those of Google.

Raw Materials Needed to Build Widgets!

KVarik recently asked a question about how to figure out how many parts are needed to create different individual items.  After discussing the issue, I soon realized that the outcome could be very valuable to anybody.  After reading this,feel free to use the template located here.

While I'm not exactly sure what KVarik was using it for I'll explain the template and the use in terms of a production line.  Let’s assume that Ted Company makes different kinds of Items.  Each Item is made from a combination of 10 different parts. I only have 10 different kinds of parts currently but the template can calculate up to 21 different parts.  Currently the template also only shows 10 different items but the number of items that can be applied is infinite.

So the spreadsheet allows me to decide how many of each type of Item I want to make and how many of each part I will need to make each item.  In order for this to work a user must indicate how many of each part are needed to make each Item.

One might think the solution was simple but keep in mind that each Item is composed of a different number of each part.  This means that the system had to be able to know how many of each part were needed for each Item and how many Items were being made.  I accomplished this by using a modification of Adam's arrayformula and vlookup combination.

Here is the function: =arrayformula(iferror($B3:B*vlookup(A3:A,'Item List'!A2:Y,{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22}*sign(row(A3:A)),FALSE)))

Now if you read the other post about using arrayformula and vlookup then this looks very similar with only two changes.  The first change I made was to the first parameter of the if statement.  Adam/Ahab's method creates a header row for the first row by explicitly defining what is going to go in each column header.  In this case the column headers are defined by other means so I only want to start the matching and multiplying process.  Therefore, I completely got rid of the nested if statement and created one iferror statement.  I still use the iferror() trick which involves only including the first parameter. Remember, that by excluding the second parameter, the function returns blank cells when it errors (in this case if doesn't find the item in the Item List).

The second change is that I added “$B2:B*” before the vlookup.  Think back to the days of linear algebra.  If you want to multiply a matrix (or array) by a value you simply indicate that value on the outside of the matrix (or array).  In this case I want to multiple the key array for each Item by the quantity I want.  So my output will look something like this:

$B3:B*{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22}

Where $B3:B is the quantity of the Item

Where {2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22}is the key array from the Item List for the Item type in column A.

I suggest opening the template, making your own copy and changing the quantity on the Manifest tab.

Let me know if you have any questions about the template and have fun! 

Note: my normal convention is to indicate fields that can be edited without disruption of the spreadsheet with a yellow background and fields that should not be edited have a grey background.

Cheers!

Current healthz/404 Redirect Problem

There is a rather major issue right now affecting some users of consumer (maybe apps) accounts when it comes to accessing the Docs List.  The Docs List is accessed by clicking on Documents from your Gmail or by going to docs.google.com and signing into your account.  Users affected are being redirected to docs.google.com/healthz which results in a 404 Error.

For those of you posting on the forum, please know that Google is aware of the issue and trying to isolate the problem.

If you are experiencing the problem please post on the main thread that I have targeted here:


Healthz/404 Error Redirect Help Forum Thread

Rebecca is requesting IP addresses of those experiencing the issue.  In addition, please include whether you are on a free consumer account (@gmail.com @yahoo.com etc.) or Apps account (@yourdomain).

In the meantime, if you are unable to access your Docs List try accessing your browser history.  When you open a Google Doc this is like going to a webpage and each Google Doc has a unique URL.  You should be able to click on the URL from your browser history to access your Google Doc.


Just a quick note about writing on the forums:  I do my best to keep the forums organized but you will see quicker responses by posting in the correct section.  For example, this is a problem affecting the Docs List which means the appropriate place for a thread of this nature is in the Docs List section under Report a Problem.

Hopefully this gets resolved soon!  And always, if you have any suggestions or information you would like to provide about this problem please do so on the forum thread.  The more information the better!

Cheers!