Archive for July, 2012

Gradually Moving Some Excel Uses To Google Docs?

Google Cloud Spreadsheets

I’m obviously a huge user of excel and have been using it increasingly as I continue to improve my skills in both building macros and using more advanced excel functions. That being said, I’ve also been using Google Docs Spreadsheets increasingly in recent years as a general move towards the clouds. Why? Here are the main reasons:

Sharing: Without a doubt, this is the #1 reason! Instead of constantly sending files to my co-workers, we can both work on the same spreadsheets, sometimes at the same time by using Google docs. This ends up making a huge difference

Available from anywhere: Being able to reach my documents, modify them from home, work, school, and more adds so much convenience, I do not have to worry about having the most updated version

Easy to use: No matter what type of device I’m using, I can usually start working on a spreadsheet within minutes without having to worry about the version that I am using or anything of that nature. It also helps avoid the issues involved in working with Mac versions of excel, with different versions, etc.

Special Uses: There are many very innovative and cool ways to use Google spreadsheets. I have used them to create surveys for my users, to publish data to websites, etc.

Things That Are Missing

That being said, I still use excel for most of the more complex things. Why?

Functions: Google docs spreadsheets do have several functions but not anywhere near as many as Excel does. There are some special ones that work amazingly well, especially in regards to gathering financial data, but overall it remains much weaker. Hopefully that will change over time.

Macros: While in theory, it’s possible to build macros for Google docs, it’s not anywhere close to being “user friendly” for the moment

User Interface: I still feel like it is still much easier to view Excel documents, probably simply because the web browser that I use to view Google docs does take out some of the space in my screen.

What About You?

Have you started using Google docs? If so, in what ways?

Extending Spreadsheet Using Excel Sumif And If Conditions

Excel function tutorials

Today I received an interesting email from a reader that had been playing around with a spreadsheet that you might remember seeing. First off, take a look at the spreadsheet. Basically, the reader wanted to add a few different functionalities which gave me some ideas to add even more. I always like to go over such problems because it helps me to add more examples of simple excel functions that become much easier to work with when used in easy to understand worksheets.

Task #1-Giving A Prize In Courses Where 4 Students (or more) Got A Score Of 70%

This was done by first writing down the courses and then using the countif function. You can find out more about the countif function here. Here is what I ended up using for maths:

=COUNTIF(B2:I2, “>=70”)

I dragged the formula down and you can see the result here:

Then, for each subject, I will simply verify that the number of students is 4 or more and write “Prize” if so or “False” if not. Here goes:

=IF(B15>=4,”Prize”,”False”)

And the new result:

Now, another interesting thing would be for students with one of the 2 best grades in 3 or more subjects to be awarded a “distinction”. How could this be done? I think the easiest would be to use the rank function. For example, to get Steven’s rank in Maths, I would use:

=RANK(B2,$B$2:$I$2)

The result is 3. So I would need to verify how many such rankings are 1 or 2. Unfortunately, in this case, I will likely be forced to use a longer formula:

=IF(RANK(B2,$B$2:$I$2)<3,1,0) This would give me a 1 if the condition is accomplished and a 0 if not. I will then do this for each subject so: =IF(RANK(B2,$B$2:$I$2)<3,1,0)+IF(RANK(B3,$B$3:$I$3)<3,1,0)+IF(RANK(B4,$B$4:$I$4)<3,1,0)++IF(RANK(B5,$B$5:$I$5)<3,1,0)++IF(RANK(B6,$B$6:$I$6)<3,1,0)++IF(RANK(B7,$B$7:$I$7)<3,1,0)++IF(RANK(B8,$B$8:$I$8)<3,1,0)++IF(RANK(B9,$B$9:$I$9)<3,1,0) Then, if all of that is 3 or more, you would get a "Yes", if not "No" so: =IF(IF(RANK(B2,$B$2:$I$2)<3,1,0)+IF(RANK(B3,$B$3:$I$3)<3,1,0)+IF(RANK(B4,$B$4:$I$4)<3,1,0)++IF(RANK(B5,$B$5:$I$5)<3,1,0)++IF(RANK(B6,$B$6:$I$6)<3,1,0)++IF(RANK(B7,$B$7:$I$7)<3,1,0)++IF(RANK(B8,$B$8:$I$8)<3,1,0)++IF(RANK(B9,$B$9:$I$9)<3,1,0)>=3,”Yes”,”No”)

This gives me 3 students with a high distinction as you can see here:

I also invite you to download the spreadsheet here.

It’s Time to Embrace Renting

Personal finance

Every single person I encounter thinks that home ownership is the best option. Nobody wants to rent. Renting is often viewed as “throwing money away.” Why would you want to throw money away when you can buy a home? That’s what everyone says about this topic.

I have different thoughts on this topic. I think that renting is a strong option. There’s absolutely nothing wrong with choosing to rent instead of owning.

What are the benefits of renting? Why should you consider renting instead of buying?

You can save money.

When you rent a place, you just pay your monthly fee and that’s all you have to worry about. The rest of your money can be put towards savings or whatever else that you want to do in life. You don’t have to put every single penny into your home. For some of us, we just need a roof over our heads.

Renting doesn’t come with any hidden fees.

As mentioned above, you only pay your rent and nothing else. A home comes with the following hidden fees that you would’ve never expected:

  • Increased consumption due to more expenses.
  • Property taxes.
  • Fixing equipment around the home.
  • The time it takes to do work around the place.

Can you handle all of those hidden expenses that come with buying?

You have the option to travel more.

As a home owner, you might get financially strapped dealing with the bills. You also don’t have any flexibility because you have to make your mortgage payments or else you lose your home. This limits your ability to travel or do anything interesting in life. I rented out my condo last fall so that I could have more money to play with and to travel. The first thing that I did was buy  ticket to Poland for a month all on my own.

Do you want to travel more? Would you rather spend the money on house-related bills.

Those are the benefits of renting. Now I want to ask you: why don’t you consider renting?

My experiment for today is simple. I want you to run the numbers. Don’t just follow your gut or listen to what conventional wisdom tells you. Do some actual calculations before making this decision. This means that you factor in all of the costs associated with buying a home. Then compare it to renting. Then factor in your savings/income.

Are you able to buy a home? Would you be better off renting for now? This is for you to decide after running the numbers.