Spy Journal 3.0 Filtering the web for you

The Subtotal function is commonly used when creating Subtotals on a table with the Data | Subtotals Menu command. However it can be just as easily used by writing the formula and has some powerful uses.
The syntax is SUBTOTAL(function_num,ref1,ref2,...)
The function numbers allow you to use any of the following functions in the subtotal:

The most common is of course the SUM function.
When and why should you use SUBTOTAL and not just the SUM function on its own.

The SUBTOTAL function has some powerful abilities that the commonly used SUM function doesn't. For example when summing a list of cells that contains hidden rows or columns the SUM function will include them. However the SUBTOTAL function only operates on visible cells. The values of the hidden cells are excluded from the total. This is especially useful when filtering a list if you want to see the total of the visible (or filtered) cells.
Note by default, if you have a filtered list of values and you double click the sum (sigma) icon on the toolbar after you select a cell below the bottom of the filtered range Excel will not use a SUM formula but automatically use a SUBTOTAL function.

The SUBTOTAL function ignores other subtotals within the range. So if you have several subtotals and a grand total, the grand total formula can be the subtotal of the entire range that includes the subtotals and it will ignore them.

Links to my other Articles about Subtotals including using outlining.

http://www.spyjournal.biz/exceltips/2005/03/creating-subtotals-in-excel.html

http://www.spyjournal.biz/data_filters_in_Excel_2003_and_2007

Ok this post might bring out the fanbois but that isn't the purpose of the article.

What I want to demonstrate is the business reasons and to some extent personal reasons why I believe the Microsoft Ecosystem is the best.

This isn't to say that the others don't have their place, and I’ll explain what that place is. I'll also hopefully demonstrate the overlaps as well.

Let start of why the basic understanding of what each of these three eco systems are and what they do.

Apple. The iWorld revolves around two basic things, hardware and authentication. Apple has been around for a long time and at various stages in my career has been my primary system (shock horror for some of my friends right there). However all that changed with the iPod and iTunes.

Apple dramatically changed their ethos of being a purely devices company until that point to force an authentication process (needed to access the iTunes store). With various incarnations since then they have until recently made this very difficult for people. There have been numerous cases of issues with devices locked to single users who had lost their authentication details unable to access their purchased music, many manufacturing faults with original iPod, and then the whole iCloud issue with families using a single account being forced to effectively change to individual accounts. Additionally most non tech users didn't know the right way to set this up and continually lose their iPhone contacts with their phones, lose their music and don't use even half of the power of this actually pretty good system. (Note Apple users are not alone in that problem)

All the way through the evolution of their devices since the iPod, iPhone, iMac etc., Apple have continually evolved their authentication process. It is now pretty good for a standalone user to have all their iDevices connected, syncing and backed up, able to be replaced simply and easily.

One other comment, the Apple store is based on the iOS development platform and although there is a pretty low bar for developers any app released for this platform works across all devices.

Google. Google don't own Android. A lot of people don't realise that. Android is an open source OS developed by Google and based on the Linux kernel. It is primarily a mobile OS. Interestingly there are more mobile devices using android than any other OS combined, iOS, windows etc. The primary difficulty with Android is the code development process that has seen massive code splits and forks based on hardware. In fact there are so many sub branches of the code that it is almost impossible for application developers (and there are over 1 million apps in the Google Play Store) to build apps that are compatible with every device. I own several android devices and some of them can have the same app installed on them but many of the apps I own will only work on one device and not others.

The android world also revolves around hardware. That is the actual software is specific to each device, and iterations of the core OS are not always or generally backward or forward compatible. In addition because the devices are mobile devices the phone providers generally control the update release path. Jail breaking mobile devices is often the only way for many users to gain access to new OS upgrades.

Google is also an authentication platform just like Apple. However their spread and control is much wider. Google is primarily a software company, not a device company. They don't (with the exception of the Chromebook) build any of the billions of android devices out there. The build software, and a very impressive list they hold. The key to the entire Google world is your Google account - often known just as the Gmail account.

Microsoft. Windows is what most people think of when you mention Microsoft. And for good reason. Windows OS in one way or another is the operating system used by over 80% of all users. This hasn't changed markedly in the 10 years, although mac has doubled from 4.5% to 9% and mobile use has gone from 1% to 5% approximately. Source http://www.w3schools.com/browsers/browsers_os.asp

Microsoft have almost never been a device company, and largely their device attempts have been marked disasters financially. (Zune, Xbox, Surface). All great devices, but poor marketing and cumulative bad decisions (e.g. the Blu-ray v HD DVD war).

Microsoft Windows OS however has only been one strength. The other core foundational strength of authentication like the others is equally important. And all the apps in their store (over half a million) work across all devices.

But Microsoft Office is the primary reason why Microsoft is different from Apple and Google.

The world’s premier productivity application has gone from strength to strength ever since Windows 95 and Office 95. Prior to that there were alternative competitors but they rapidly dropped off the scene and by 2000 were all but non-existent. There are still small alternative open source alternatives like Open Office. And although Google Apps is seen as an alternative, it’s not actually for one important reason - there is no desktop install for Google Apps - you must be online to use their browser apps. Microsoft office has browser apps that deeply and richly integrate with a full installable desktop application and that is why they are not comparable.

So that's the background out of the way.

Now for the primary reason that I prefer Microsoft over Google and Apple. And note - this is not to denigrate the others, they just can't do what Microsoft can do. Also please note I am dumbing down here to a lowest common denominator. Yes there are technically savvy people (and the fact you are reading this blog probably indicates you are one of those) to whom this lowest level doesn't apply. But the general run of the mill average computer user doesn't even understand all these points of difference. Their password is the name of their dog with their year of birth backwards or it’s written on a yellow sticky note under their keyboard because their company makes them change it every 60 days (how inconvenient). They don't use keyboard shortcuts and use their mouse as their primary input tool, they still use desktop icons because most of their important documents are saved onto their desktop in a forest of icons. They don't know how to use the start key (what is the start key?). In a Microsoft world this user needs at least one password, maybe two. They have their password to access their computer - (which on a Windows 8 pc is either their Microsoft ID or their company domain authentication). They may have both a Microsoft ID and a company AD authentication - especially if they have office 365.

Everything they need to do is controlled and accessed with this authentication.

Now if you add a windows phone or table there is no change. Everything is seamlessly synced between a Windows 8 pc, phone or tablet device and there is no need for any further authentication.

And here is where it gets tricky.

If you want to use an iPhone or Android mobile device, you suddenly enter their worlds for authentication. And now nothing syncs. And you suddenly have two sets of passwords and two sets of apps that don't necessarily crossover.

And then there are the browser wars (currently being won by chrome and Google). Sure you can use Chrome without signing in and many people do. But as soon as you want to sync your browsing history and preferences between your mobile device (android anyway) and your pc you need a Google account. And don't try and have a pc with chrome as your browser of choice and an iPhone. Never the three shall meet!

And so it should have become abundantly clear right there why I favour the Microsoft world. For most (over 80%) users they’re using it for work or personal computer. Office is really the only product tool there is. And authentication is critical so having multiple authentications and knowing when to use them is just painful.

As an IT professional who spends most of my time assisting these users who are not tech savvy, who are 30 plus years old generally (not digital natives) who can't remember complex passwords or use apps or tools to do it for them, every additional level of complexity is just too much. They don't spend their waking moments figuring out how they can mash non compatible systems together. They use their computing devices as necessary evils for accomplishing their daily tasks.

Microsoft makes that easy. Google and Apple just complicate it.

For a long time I have known Charley Kid and supported and sold his Excel Dashboard tools. I use many of his tools as reference material when designing and developing client reporting systems myself.

Right now Charley is having a Half Price Sale!

Here are the details:

Here’s where you can grab everything for half off:

http://exceluser.com/training/index.htm

I’m holding this special sale for two reasons. First, it's a great way to say THANK YOU to all the people who read my "Excel for Business" newsletter. And second, it helps the many subscribers who've asked for an inexpensive way to buy a lot of my Swipe Files for immediate download.

Try My Products for 50% Off and Get Two Unique Bonuses!

If the 50% discount isn’t enough to have you heading to my “half-off” web page, these two free gifts will surely do the trick:

Have you heard of Office 365? If not you are probably living under a luddite bush as the advertising is everywhere. From popping up in Skype (now owned by Microsoft) and being named sponsor for the recent V8 Supercar Tasmanian Round (in Australia) to TV spots and Billboards as well as YouTube Office 365 is an integral part of Microsoft's big bold new direction.

Microsoft’s vision in the 80s was “A PC in every home”

Today it is “A continuous cloud service for every person every device and every business” Kevin Turner - Microsoft COO, WPC10

With business users now often having more powerful PCs at home then on their desktops large businesses are often lagging behind. In the past small business was given the crumbs from Enterprises table. Now the tables have well and truly turned. With Office 365, Microsoft have created the opportunity for agile small businesses to utilise the power of cloud computing and enterprise level IT architecture for a fraction of the cost on a monthly subscription.

Lets go through the benefits and features.

• Access email anywhere on any device – using Exchange as the backend for email management and storage
• Shared calendars and out of office messages (not available in standard Outlook)
• Share documents with version control and security management to people in and outside your organisation
• Full High Definition video conferencing

We have been working with Office 365 for a long time (from long before it was marketed as that) and are busy installing it in many of our customers organisations.

Here is a slide show presented recently to a small business network group.