In one of my previous posts I embedded a Google spreadsheet in the post to show you a practical use of the CountIf function. Read it here: Practical use of CountIf with Google Docs Spreadsheet.

This time I’m going to use Microsoft Office Web Apps, Google’s competitor when it comes to online documents.

Excerpt taken from Microsoft Office Web Apps site:

Whether you’re in the office, at home, or on the road, Microsoft Office Web Apps help you get more things done virtually anywhere and anytime. These convenient online companions to Microsoft Word, Excel, PowerPoint, and OneNote offer you an easy way to access, view, and edit documents directly from your Web browser.

I first read about embedding Microsoft Web Apps documents through Office Web Apps blog post Embedded Excel and PowerPoint Available Now on SkyDrive. Then I went to this post dedicated to Excel: Embedding Excel Web App in your own web page or blog. This last post shows some good samples of the power that embedded spreadsheets gives you.

My intent is to highlight a really important feature that Google is currently missing in its service: cell references (line numbers and column letters) in online published documents.

Take the same Excel spreadsheet from last post but now hosted in the cloud by Office Excel Web App:

As I used cell references throughout that last post to explain things, it would be really useful if Google spreadsheet had cell references so that users could spot the cell I was referring to instantly.

You can see above that Excel Web App does show cell references making the overall user web experience better.

For example, above I allow you to edit (AllowTyping=True) the contents of this spreadsheet on the fly. I also allow you to sort the table contents (AllowInteractivity=True). The modifications you make are valid only in this session. If you close or reload the page your changes won’t be saved. This is really nice. A lot of possibilities emerge from this.

Changes you make are reflected in the spreadsheet. Try for example adding one more TL class in cell D5. The cell color will change to yellow. You’ll see that cell I17 will have its value changed. It’s going to be -1. The formulas are being updated automagically. You can even see the formulas by double clicking a specific cell that has a formula.

One thing I noticed is that color formatting rules applied to cells are kept as you define in Excel Desktop App (Excel Web App counterpart). I tried to configure these rules in Excel Web App but I couldn’t find a menu option that would allow me to change them.

It’s important to mention that although great part of Excel Desktop App features aren’t available for configuration online, Excel Web App still honors Excel Desktop App configurations you make in your workbook. This is cool!

If you're curious, this is the code I'm using to embed the spreadsheet in this post:

<iframe src="http://r.office.microsoft.com/r/rlidExcelEmbed?su=-6822816632184108919&Fi=SDA1507C6BFF0A0889!186&AllowInteractivity=True&AllowTyping=True" frameborder="0" width="525" height="475" scrolling="no"></iframe>

If you want to learn more about the options available to embed an Excel Web App workbook in your site or blog, take a look at: Customize how your Excel workbook is embedded.

If you wanna follow the explanation of this post by looking at cell references, go to this post: Taking Microsoft Office Excel Web App for a spin

I’ll show you a practical use of the CountIf function that you can apply in both Microsoft Excel spreadsheets and Google Docs spreadsheets.

Let’s start with this sample spreadsheet:

I’m using the above spreadsheet to keep track of my progress during the course for my first driver’s license.

The CountIf function is being used in column Remaining classes for each discipline. For example, the actual formula in cell I17 is this:

I17 = H17 - countif(D3:I14, B17)

Let’s break this thing and explain each piece:

H17 is a fixed value = 18. I know it beforehand. In this case for the Transit Legislation discipline I must have a total of 18 classes.

The countif function has this form: COUNTIF(range, criteria). So the above countif(D3:I14, B17) is telling us that:

D3:I14 is the range of cells we’re searching and B17 is what we’re after… The range encompasses all the disciplines’ acronyms from 11/23/2010 to 12/9/2010 for all the timetable.

B17 is the discipline’s acronym, in this case it is TL.

Easy to understand, isn’t it?

Doing the above with countif I can tell how many classes I have scheduled for that discipline. Then I just subtract this value from the total necessary classes to have a picture of how many classes I still have to go through to fulfill the necessary ones.

Let’s do the math:

CountIf returns 18 because LT appears 18 times in the timetable. Making the substitution of values:

I17 = H17 - countif(D3:I14, B17)
I17 =  18 - 18 = 0

Now I know that if I follow the above schedule I’ll probably finish the theoretical course by 12/9/2010. Pretty good.

This was a simple and practical use of the countif function that came in handy in this situation.