Last week, after publishing my most recent post about Google spreadsheet, I received 5 emails within a few hours from readers that are increasingly using Google docs. I asked them why? Not surprisingly, they said it was mostly the ability to have multiple users at once as well as the free cost. For years, the main drawbacks for Google spreadsheets (when comparing with Excel) has been the lack of advanced functions, macros, etc.

Things Are Changing Quickly

Over the past few months, I’ve published posts about getting financial data into the spreadsheets through the Google finance function, one of many “custom” functions.

Then last week I discussed the use of scripts to build “vba-like” functions

Today, I wanted to discuss some of the changes in the new version of Google spreadsheets that is being rolled out:

-Offline working: This is GREAT. Yes, I love working online and will always do so when possible. But the ability to work on my docs when offline is a major benefit. Yes, you need to use Chrome as your browser but that is what I always use anyway.
-Improved Formula Editing: This makes it easier to work with functions in a similar way to what Excel offers
-New Functions: They added functions such as “Sumif”, “Countif”, “AverageIf”… ring any bells?
-Filter views: I will need to experiment with this but it does look promising

Many other things were improved/upgraded and added.

Clearly, Google Spreadsheets always was an alternative to Excel but as time goes by, it’s clearer that the spreadsheets are quickly catching up to MS Excel. It’s still lacking many different functions but the gap is shrinking.

If you are not yet using Google Docs, I’d love to hear why, and if you are, could you imagine moving away from Excel at some point? I’m far from that point but I would say that I spend as much time in Google spreadsheets as I do in Excel now…!

There are many benefits to using Google Docs instead of Microsoft Excel which I’ve certainly discussed on this blog but one big downside in my opinion is/was the lack of complexity of Google docs. I run several excel spreadsheets that use complex functions, macros, etc.

Google Docs Scripts Could Be The Way Forward?

Google does offer ways to add complexity through “scripts” which I had heard about but had never used. I then ended up looking for ways to get the latest price of “Bitcoins” and while there were ways to do it with a simple formula, I did see a script which looked interesting so I decided to give it a try. I’ll tell you how step by step.

Step #1-Create A new spreadsheet

Step #2-Name a cell (this specific script would add the Bitcoin value into a specific cell so I needed to name a cell:

Step #3-Create A Script

Step #4-Run The Script

Voila! You can see the value here:

This is certainly interesting and I’ll without a doubt be reading up on these and trying several more. I doubt I’ll get rid of Excel anytime soon but if I’m able to move some of my spreadsheets, I’d be thrilled.

One big challenge that I expect is that I’ll certainly have a lot more trouble finding info about Google Doc scripts than Excel VBA.

Have any of you tried Google Docs scripts? If so, I’d love to hear from you.

Today I received a question from a reader who had purchased our Excel ebook, it’s a question that comes back fairly frequently so I thought I’d give a public answer. I’ve done similar examples but it’s always helpful to do it another time. The problem is fairly easy:

****************************
I am trying to figure out how to calculate a commission structure based on a range of dollar amts.

I’m guessing I can do this with IF function, but I can’t seem to nail down the syntax right.
****************************

There are a hundred different ways to do this but here is what I will do. I’ll start the opposite way that you would typically expect. First, I want to know the commission on sales of over $25,000. Basically, the vendor would earn:

5% on $2500 = $125
10% on $2500 ($5000-$2500) = $250
15% on $20,000 ($25,000-$5000) = $3000

So the vendor would earn $125+$250+$3000+20% (amount – $25,000) or $3375+ 20%

if the sales is in cell A1, then I’d get:

=IF(A1>25000,3375+0.2*(A1-25000),0)

Makes sense? That was one of the 4 scenarios. Now, I’ll move to a vendor that would sell between $5001 and $25000. He would generate a commission of:

5% on $2500 = $125
10% on $2500 ($5000-$2500) = $250

So the vendor would earn $125+$250+15% (amount – $5,000) or $375+ 15%

=IF(A1>5000,375+0.15*(A1-5000),0)

If I combine both together, I get:

=IF(A1>5000,375+0.15*(A1-5000),IF(A1>25000,3375+0.2*(A1-25000),0))

Now, I’ll move to a vendor that would sell between $2501 and $5000. He would generate a commission of:

5% on $2500 = $125

So the vendor would earn $125+10% (amount – $2500)

=IF(A1>2500,125+0.1*(A1-2500),0)

If I combine everything together, I get:

=IF(A1>5000,375+0.15*(A1-5000),IF(A1>25000,3375+0.2*(A1-25000),IF(A1>2500,125+0.1*(A1-2500),0)))

Then, I can simply replace the 0 (which is for cases where the sales are less than $2500) by 0.05*sales

=IF(A1>5000,375+0.15*(A1-5000),IF(A1>25000,3375+0.2*(A1-25000),IF(A1>2500,125+0.1*(A1-2500),A1*0.05)))

I dragged the formula and got a few examples:

You can also download the spreadsheet here

As many of you know, I now have all of my investments in one Google docs spreadsheet. I used to do this in Excel but over time found Google docs both easier to do and also available from any location. One of the few things that I was manually updating was the exchange rate (I do hold both some Canada and US listed stocks. I did a bit of research and it’s actually fairly easy to do:

=GoogleFinance(“CURRENCY:USDCAD” ; “average”)

Easy enough? Now I only need to manually update my document when I receive dividends:)

Do any of you use Google docs to keep up-to-date with your investments?

One of the very real examples that I’ve had in the past few months is trying to find baby names which as you can imagine means looking at a bunch of lists, discussing, etc. My wife and I have a few different “criterias” and one of them is having a name is would also be “fairly common” in both the UK and Canada where we might end up living at some point.

So I downloaded the list of the top names used in 2013 in all 3 countries as you can see here. I’ll start by looking at all of the US names to see if they are part of the top 100 names in both Canada and the US. How?

Let’s start with the first name, I’ll simply add next to it:

=VLOOKUP(A2,E:F,2,FALSE)

Now I’d like to replace the 0 by 1 and the #N/A by 0… so I’ll do:

=IF(ISERROR(VLOOKUP(A2,E:F,2,FALSE)),0,1)

Then I’ll simply add the reference to Canada as well:

=IF(ISERROR(VLOOKUP(A2,E:F,2,FALSE)),0,1)+IF(ISERROR(VLOOKUP(A2,G:H,2,FALSE)),0,1)

To make it more clear, I’ll then add a condition:

=IF(IF(ISERROR(VLOOKUP(A2,E:F,2,FALSE)),0,1)+IF(ISERROR(VLOOKUP(A2,G:H,2,FALSE)),0,1)=2,”Yes”,””)

Then I could add a filter to get the list of names that fit the critera: