Newsletter Issue 43, April 2002

Sam Young Newsletter

Issue 43, April 2002

Hi guys,

Check out Hewlett Packard's Cool Town below. There are a couple of sites for those of you who want to know more.

We have the last session in our Excel formula series (for now) in Excel Array Sum Using "Or" Statements. 

Password Protecting Workbooks   TLAs for SMEs        Short & Hot Keys        Hot Linx

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.

Cool Town

The Cooltown project sites in Paolo Alto, USA & Wokingham, UK, are designed to demonstrate technologies for a 'digital lifestyle'.  Showing off research from HP's labs around the world, a bucket load of technologies are combined into a practical, working environment for the next generation of retail e-commerce, travel and admin.

Divided into zones (home, travel, retail, government & office), the Cooltown site experience is intended to simulate a day in the life of a wirelessly connected urban professional, showing a vision of the very near future. Everything actually works to show how wireless networking, advanced user interfaces and existing systems can be linked together.

You can listen to HP's Internet Angel - a video-quality talking head of an extremely wide-eyed woman with a Californian accent - announce that it is 5am, and that she is waking us up half an hour early because of traffic congestion, and here was the soft rock music we requested. The simulated day continues with similar delights: a mirror in a baroque gilt frame dissolves into a list of things to do and urgent video mail; a hire car announces it is overheating and guides us to the garage where a replacement is waiting; a shop attempts to coordinate a tie purchase - Rainbow Lizard pattern - to a shirt colour, and a workplace environment changes desk heights when the worker logs on and makes decisions based on democratic voting from overseas offices. 

Although Cooltown contains no breakthrough technologies, the idea is that all the systems are practical, are based on open protocols and inter-operate seamlessly. HP says that Cooltown is expected to make the intended audience (heads of industry and senior planners in large corporates) aware of the possibilities of the practical adoption of advanced technologies.

Check it out on http://www.cooltown.hp.com/cooltownhome/ and http://eu.cooltown.hp.com/home.htm

Excel Array Sum Using "Or" Statements

This time we are looking at to make calculations which use OR conditions to make calculations of the type, for example, "Sum Sales where Location = Dunedin or Salesperson = ASB". 

Here's the worksheet example from last time: 

		A	B	C	D	E
	1	Location	Salesperson	Sales	Car?	# of Calls
	2	Christchurch	NNW	1715.23	Yes	Over 50
	3	Christchurch	SXP	1605.00	No	Over 50
	4	Dunedin	ASB	1854.72	Yes	Over 50
	5	Christchurch	ASB	975.47	No	Under 50
	6	Dunedin	ASB	2140.20	Yes	Over 50
	7	Christchurch	NNW	1571.32	Yes	Under 50
	8	Christchurch	SXP	1654.29	No	Under 50
	9	Dunedin	SXP	1245.65	No	Over 50
	1o

One of the formulas from last time, which we used to add the Wages for employees who were both staff (Yes) and who worked in Accounts was this: =SUM(IF((A2:A9="Dunedin")*(B2:B9="ASB"),C2:C9)) This formula works on the basis that each test will evaluate to TRUE or FALSE (1 or 0) and the results of both tests are multiplied together. Only if both tests evaluate to TRUE (1) will the result of the multiplication be 1, and the Wages amount for that row will be included in the total. 

You can use this same process to make an OR calculation, but this time you should add the results, rather than multiplying them. Now, when the results of the tests are evaluated and added, the Sales amount in any row in which any one of the tests evaluates to true will be included in the result. If both tests evaluate to true, the amount will still be included. So, this formula will sum all the sales for all salespeople working in Dunedin or for ASB in particular : =SUM(IF((A2:A9="Dunedin")+(B2:B9="ASB"),C2:C9)).

• Remember that this is an array function, so you must press Control + Shift + Enter after you've typed it for it to work. If you don't, press F2 to edit the cell and then press Control + Shift + Enter. You'll know you've got it right when you check the cell formula and it's surrounded by curly brackets {}. 

You can also use array formulas to count the number of salespeople who either work in Dunedin or for ASB : =COUNT(IF((A2:A9="Dunedin")+(B2:B9="ASB"),C2:C9)), but the action doesn't stop here. You aren't limited to COUNT and SUM and you can use other functions like AVERAGE for example. Try these functions which average the sales for both our previously stated AND and OR test criteria: =AVERAGE(IF((A2:A9="Dunedin")*(B2:B9="ASB"),C2:C9)) =AVERAGE(IF((A2:A9="Dunedin")+(B2:B9="ASB"),C2:C9)) You can also use MAX and MIN as shown here: =MAX(IF((A2:A9="Dunedin")*(B2:B9="ASB"),C2:C9)) =MAX(IF((A2:A9="Dunedin")+(B2:B9="ASB"),C2:C9)) 

Once you see how easy it is to create array formulas you can extend them and add more columns with other criteria to calculate multiple criteria, for example, calculating the sales for all salespeople who work in Dunedin with a car and who are have made over 50 calls;

 =SUM(IF(((A2:A9="Dunedin")+(D2:D9"Yes")+(E2:E9="Over 50"),C2:C9)) 

When you need to do tedious calculations like this, array formulas allow you to reduce the workings to a single cell which is both neat and very easy to troubleshoot when things go wrong.

Have fun! And don't forget the Control + Shift + Enter to get your formulas started. 

Password Protecting Workbooks

I am sure that most of you know how to protect an Excel workbook so that people can't change it (Tools menu |Protection | Protect workbook). But this will only stop people being able to make changes. They can still open the book and view all the sheets.

There is another security feature of Excel that allows you to place an "Open only on entering correct password" security function in a workbook. Very useful for keeping your gambling debts safe!

Many of you won't have run into this feature as it is reasonably hard to find (who would have poked around in Excel and looked at the tools menu on the "Save As" dialogue box to find something like this except a REAL computer geek?!)

For "Open Only on Password" do the following; 

1. Have the workbook you wish to password protect open 
2. Go to the File menu, select "Save As" 
3. Go to the Tools menu (top right of the "Save As" dialogue box), select "General Options" 
4. In the "Password To Open" box, type a password, and then click OK (NB: Passwords are case sensitive so make sure you are VERY careful at noting exactly what you key in. Write your password down and keep it secure. Lose your password and you can't get into your workbook anymore...) 
5. In the "Re-enter Password To Proceed" box, type your password again, and then click OK. 
6. Click "Save".

Piece of cake. When you know how.

TLAs for SMEs

Here are this newsletter's TLAs for you;

• VISCA, Video System Control Architecture. Developed by Sony, this is a protocol for daisy chaining up to seven video devices together and connecting them to a single serial port 
• VERONICA, Very Easy Rodent Oriented Net-wide Index to Computerized Archives. Developed at the University of Nevada, Veronica is a constantly updated database of the names of almost every menu item on thousands of gopher servers. The Veronica database can be searched from most major gopher menus
• WinWAIS, Windows Wide Area Information Servers.

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, Alt & F7;

• PowerPoint, Word "Find next misspelling (Automatic Spell Checking enabled)" ALT & F7
• Excel "Carry out the Move command (workbook icon menu, menu bar), or use the arrow keys to move the window" CTRL & F7 
• PowerPoint "Carry out Move command (presentation Control menu)" CTRL & F7 
• Word "Doc Move" CTRL & F7

Hot Linx

Any of you who still remember the eulogy from Four Weddings & A Funeral may be interested in checking out what else Auden wrote at http://www.audensociety.org/ 

If you aren't keen on Datafellows or your AV software provider's website for your virus and hoax information, maybe you might like http://www.vmyths.com/ or http://www.incidents.org/ 

Ever been lucky enough to see aurora borealis? Well, it doesn't matter if you have or not, as this Norwegian site has some online for you to check out at http://www.northern-lights.no/ 

Want to avoid the worst movies of 2002? Then check out the Ultimate Worst Movie List at http://www.thestinkers.com/2002sofar.html 

---

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