Friday, 29 March 2002

Newsletter Issue 42, March 2002

Sam Young Newsletter

Issue 42, March 2002
Hi guys,
This month's I look at why, at some point in the not too distant future, we should be thinking about keeping all our files on the internet in Internet Profile Hosting below.
Continuing on with improving our Excel usage, this time we look at multiple criteria "if this, then that" functions for you in Excel Array Sum Using "And" Statements
Don't forget, if you want to be taken off my mailing list, click here to send me a reply e-mail and I will remove your name.

Internet Profile Hosting

Well, I have come around.
"Around to what?" you are probably asking. Around to web-based file storage.
When I first heard of Microsoft's ".net" idea, I thought it was dumb. Why on earth would you want to have all your personal information and files on the internet? The security issues. The inconvenience with obtaining files. The access speeds.
But now, having looked at where technology is aiming to go, I can see WHY we will all end up with our data stored - not on our personal PC at home - but with a data storage company on the net.
For us to be truly mobile, we need to be able to reach our data from anywhere in the world, country, office, street or house. Most of us don't have a network to call our own; and indeed, we don't need one. There's a perfectly good internet out there that we can use instead. 
So your files are on the net. You access them through an ASDL connection from home, from work, from any internet café, from your PDA, from a public terminal at the library. The PC automatically starts up with YOUR software (you are the licensed user), your profile, your shortcuts and your files. Seamless. You can see your PC world the way you are used to, anywhere you need to.
There are a pretty good pile of positives to pick through. Delayed at Singapore airport and forget your music? Never mind. Re-download your MP3 stick at a kiosk. Need your files from work on a Sunday? Open em up (maybe even "unscrew" 'em using a fingerworks keyboard - see below!) from home. Get a virus in your PC? Wipe it out and reconnect to the net where your files are backed up safe and sound. Want to create an access folder for shared files with others? Your data hosting company will set one up. All your profile, your files, your settings, your software. This is truly PC anywhere.
There are some negatives. Technology hasn't caught up with us yet. We need ASDL connections to achieve this "Nomadicity". Everywhere. And cheaply. If the wired phone companies don't get their act together on this, then the mobile people will. And security is a real biggie.
Data warehousing companies will have to get a lot of security issues sorted out. You are likely to have to have a two-way recognition system to get at your personal information; like retina scan and voice recognition together, but what about virus attacks, hackers and so forth? 
What happens if you don't pay your warehousing bill? How do you access your information? Can you get it back? Who owns it?
There is also the risk that you may end up being locked in to software upgrades, as the software companies will no longer sell you a version. You will just pay rent on a copy of the latest release.
There will be some interesting things to sort out, but I think that overall it is positive. I like the idea of being me wherever I go. Of not having to worry about leaving files behind in a meeting. And of never having to do another backup!

Excel Array Sum Using "And" Statements

Here's the worksheet example from last time, with a few more lines added: 

A   B  

        1  Location  Salesperson  Sales 

        2 Christchurch NNW 1715 .23

        3  Christchurch SXP 1605.00

        4 Dunedin  ASB  1854.72

        5  Christchurch ASB 975.47

        6 Dunedin  ASB  2140.20

        7 Christchurch NNW 1571 .32

        8 Christchurch SXP 1654.29

        9 Dunedin SXP 1245.65


Array Sum
Where you need to look at multiple criteria, we need an Array Sum. An Array formula is a complex to create, but it works very well in situations like this one. 
To get a sum on "Location = Dunedin" AND "Salesperson = ASB", in C10, the array formula which will solve our problem is: =SUM(IF((A2:A9="Dunedin")*(B2:B9="ASB"),C2:C9)) or even shorter, =SUM((A2:A9="Dunedin")*(B2:B9="ASB")*C2:C9)
These formulas only add the corresponding entry in column C if the corresponding tests on the data in columns A and B return true values. Excel calculates this by treating the values in columns A, B & C as one dimensional arrays and it performs the calculations using these arrays. It evaluates the tests on the data in row 2, then row 3, through to row 9 and then sums the results. 
To make the formula work, press Control + Shift + Enter. This lets Excel know that you're using an Array formula. If you forget, you'll see #VALUE! appear in the cell and you should select the cell, press F2 as if to edit it and press Control + Shift + Enter to enter.
To count the matching entries (rather than sum them) use: =COUNT(IF((A2:A9="Dunedin")*(B2:B9="ASB"),C2:C9)) and press Control + Shift + Enter. 
Array formulas like these make multiple calculations and return a single result from them. 
While many calculations can only be made using array formulas, this is one calculation you could have made another way; using Excel's Conditional Sum Wizard add-in. 
You can test this by loading the Add-in; choose Tools, Add-ins, enable the Conditional Sum Wizard and click OK. You'll find it is now installed on the Tools menu - select Wizard then Conditional Sum. To create the formula, first select the entire data area as the list to work with, then specify the two tests which must be met (the Staff value must be equal to Yes and the Department value equal to Accounts, and finally indicate the cell to contain the answer. The resulting formula will be different to the two we've shown but works just as well. 
Next time we'll look at formulas to make either or tests like Location = Dunedin OR Salesperson = ASB.


There is a wee company liaised with the University of Delaware in the US called Fingerworks. You will be hearing about them at some point in the near future as, for the last four years, they have been developing a revolutionary new keyboard system.
This keyboard will end up being like one of those flexi-plastic mats that you use to protect your table. At the moment they are about 5mm thick, and still fairly rigid. but they have prototpyes that are transparent.
The best thing is that you use no pressure at all to strike the keys - it can sense your fingers moving towards "A" and assume that you mean "A" before you even get there. 
The mouse is a REAL touch pad. You just twirl your finger gently around and tap with your finger for a mouse click. You want to open a file? Make a motion like unscrewing the lid of a jar. Close the file? You guessed it; screw it up again. 
Lots of clever things like this will make this keyboard VERY useful for people with OOS injuries like tendonitis. But as you still need fine motor control to use it, I dunno if it is going to make much of an impact on Carpal Tunnel and De Quirks type OOSes, but at least it's a start.
I really like the mouse. Very clever. Check them out at 

TLAs for SMEs

Here are this newsletter's TLAs for you;
  • PDA, Personal Digital Assistant, for those of you who have forgotten!
  • PABX, Private Automatic Branch Exchange is an automatic telephone switching system within a private organisation
  • HDTV, High Definition Television is a TV display technology that gives 35 mm picture and compact disc sound quality. Pretty greedy on bandwidth so not yet feasible

Please feel free to email me with any TLAs that you want to get the bottom (meaning!) of.

Short+Hot Keys... and now tips
All the Function keys for you - this time it's all you can do with Ctrl & F6 or Alt & F6;
  • Access, Excel, PowerPoint, Word "To cycle between open windows" CTRL+F6 
  • Word "Next Window" ALT+F6
Hot Linx
If you are looking for texts on the net & want some freebies, or are looking for some free study guides, then check out 
How many of you have checked out our excellent local Art Museum on line? Their site has all the latest gen on what is happening at 
Ever heard of Freda Mary Cook (née Pym)? No? Well perhaps you should find out about our land and our people at 
Heard of HPs new project, Cooltown? No, well you had best check out their ideas for the future then at 

                                Catch you again soon!! E-mail your suggestions to me here