Using =split() to create column headers: A "dripping awesome" result

Asker Anbotero asked the following question on the Docs forum yesterday:

"I happen to have columns A and B, each filled with “X”s: 

A     BX
        X
X     X
X
X        X
X     X
How do I count the number of cells filled in A that are not in B, and vice-versa? There is this undocumented Google Docs function, but I couldn’t understand how it works: COUNTUNIQUE().

For A it should be 3, and for B just 2."

Adam provides a wonderful and quick answers for finding the answer for column A:

=ArrayFormula( SUM( ( A:A = "X" ) * ( B:B <> "X" ) ) )

This formula must be applied in a column that is not A or B because his ranges use open ends. If this formula was placed in a cell in column A or B the formula would error as a circular dependency. His formula basically counts the number of times there is an X in A and not in B which is exactly what Anbotero was trying to solve. To amend this for column B it would look like:

=ArrayFormula( SUM( ( B:B = "X" ) * ( A:A <> "X" ) ) ).

So, while Adam was dutifully providing this answer I was trying to figure out how to do this with one single formula. Here is what I came up with:

Click here to make your own copy to play with.

The formula:

=arrayformula(if(row(A1:B2)=1,split("UniqueCol1xUniqueCol2","x"),split(countif(arrayformula(if(A3:A=B3:B,1,if(A3:A="X",2,3))),"2")&"x"&countif(arrayformula(if(A3:A=B3:B,1,if(A3:A="X",2,3))),"3"),"x")))

I'm more interested in explaining the method as to drilling down the function but if you want to know feel free to write in the comments and I will expand further. The function creates a 2x2 table. The first row basically takes the string UniqueCol1xUniqueCol2 and "splits" it between A1 and B1. The function knows to split when it sees "x", the delimiter, in the string. UniqueCol1xUniqueCol2 becomes UniqueCol1 | UniqueCol2.
The function knows to do this because of the first condition in the if statement. In general, if you want to create a row with headers always use =row() for the first condition in an if statement. Set this equal to the row number where you want the column headers to begin. In this example, I want the column headers to appear in row 1. Therefore, I use =if(row(A1:B2)=1...

The same is done for the second row but I don't need to define this row because I'm only using 2 rows in my range. Therefore, if its row 1 put my headers, if its not then do my calculations. For the calculations, the method of using =split() is the same. I do the calculation for column A, join it with x and then join it with the calculation for column B. Before the split, the function ends up returning 3x4. Then =split() does the same with 3x4 as it did with UniqueCol1xUniqueCol2.

There you have it! Yogi also just posted another method on his blog. All of these different solutions truly show how robust Google Spreadsheets can be (or how addicted the Docs TCs are!).


Cheers!


PS: Adam's query solution also works! See Sheet2 on my spreadsheet for that example.

New Docs List UI - Viewing Density

Ok so this is wonderful.  Today Google Docs is fully rolling out the new UI for the Docs List.  But the thing I am most excited about is Viewing Density!  You have the ability to choose how dense your Docs List appears.  This works by changing the amount of white space between Docs in the Docs List.  Currently there are three settings Comfortable, Cozy, and Compact.  Personally, I enjoy the least white space between Docs as possible (Compact View).  To change the viewing density click on the little gear in the top right corner (not the complete upper right corner).  The mouse over should say "Settings."  You can then choose your density and see a real-time change.

Enjoy!

Tip for Finding "lost" Docs

Occasionally users will not be able to find their Docs in the their Docs List. As much as people think Google does something to remove their Docs they rarely disappear because of something Google did.  Below are a few tips for finding these lost Docs.

1. Search for the Doc in the search bar in the Docs List by the title of the Doc (assuming you know it).
2. Search using "*" which will return all of the Docs in your Docs List regardless of whether or not you have chosen to "not show them in Home."
3. Check your browser history for recent viewings of your Google Docs.  Each Doc has its own URL which is saved in your browser history (assuming you don't delete browser history every so often).
4. Check the Trash.

Keep in mind that if you are NOT the owner of a Doc and the owner deletes it, the Doc is gone forever.

If you are still unable to find the Doc then maybe there is a problem on Google's end.  I've seen posts on the forums where Docs disappear only to reappear later. Sometime a little time shakes things out.

Cheers!

Methods of Depreciation: A Template

One of my classes in grad school is called Health Care Accounting.  We are tasked with concepts from Financial Accounting, Managerial Accounting, and topics from Financial Reporting.  Most recently we discussed methods of Depreciation.  In our context, we looked at depreciation in terms of how we allocate costs of an asset that has a usable life of some x years.  The template I created requires the user to input original cost of the asset, the salvage value (if applicable, otherwise $0) and the usable life in years.  The three methods I created the template for are Straight-line, Double-Declining Balance and Sum-of-the Years' Digits.

If you are interested in any of the functions feel free to comment!

Check it out!  This is also my first post to the Google Docs Template Gallery (which is kind of broken right now). Please rate it once its up! If you would like your own copy or to play around with it, simply choose File>Make A Copy from the view-only version below.

Enjoy!

Depreciation Methods Template

Reflecting on the TC Summit

This past week I was fortunate enough to go to Mountain View, CA for the Global Top Contributors Summit at the Googleplex.  The experience was truly amazing.  I got to meet Teresa C, the Google Docs, Sites and Apps Script Community Manager.  As a TC I am in daily contact with her about Google Docs so it was finally nice to meet her in person.  I also met many other wonderful Google employees.  We got to discuss the products with project managers, engineers and other support team members.  It was truly amazing how interested they were in what we had to say.  There were many situations where we would ask questions or have ideas and they would immediately contact someone to find an answer if they didn't know.  Google is extremely interested in what the users want and this made me extremely excited.  I really wish I could express how amped I am about Google, their mission and their products after going to the TC Summit.  I think they really understand people and how to make products that are always innovative.  I think this will make me be an even better TC.

To all who are reading this I hope you continue to stay with Google products.  I saw things that were truly amazing which proved how innovative Google actually is.

I was also impressed how Google treats their employees.  We all always hear about the things that go on at Google but the perks themselves are not really what impressed me.  Google treats their employees the way they do because they understand how to have productive employees.  If their employees are not being 100% productive then they believe they shouldn't be at work.  When this is the case, Google has provided activities that will help employees get to that 100% productive point.  Many people would think that the Googlers would take advantage of this but they do not.  Googlers are the type of people who know when its time to work and when its time to play.  I truly believe this why Google is so successful.  They pick the right people, put them in the right places, and provide them an environment to be productive, innovative and to explore the craziest ideas. Bureaucracy simply doesn't exist.

Before the TC Summit I was rather annoyed by the amount of complaints I saw from users and put part of the blame on Google.  I thought "Why would Google release a product with all of these bugs?!"  This led to my adoptions of "fixes before features."  But now I get it.  I know exactly why Google releases things knowing full well that it isn't perfect.  So I will be getting rid of the "fixes before features" tag in my signature soon.  However, I think my understand of innovation deserves its own post after a few more days of reflection.

So in the words of Yogi, let us keep Googling!

Cheers!