Friday, 24 November 2017

Quick Excel Tip: VLOOKUP

Excel's VLOOKUP feature lets you turn it into a sort-of database.

If you use Excel to manage projects, invoices or other tasks where you enter the same set of contact details you can semi-automate the process using a handy function called VLOOKUP.

It's extremely handy but many descriptions I've read have been a little brain-bending so here's my cheat sheet.

Take the following breakdown of the function's different elements:

VLOOKUP ( a, b:c, d, FALSE)

Now let's explore each element:

a) Take this value...

b)
     } scan this range for the same value
c)

d) ... and report the related value in this column.

This means that Excel will take a value, such as a name, scan a range of cells looking for this same name and return related data, such as an address, age or pay rate.

EXAMPLE 1:

VLOOKUP(B3,$B$3:$G$100,2,FALSE)

Look at the value in B3 - this could be the exact name of a client: "Fred Smith"

Scan the range $B$3:$G$100

Find the matching client name ("Fred Smith") in this range and report the corresponding value in the second (2) column ("1 Redwood Avenue").

The 'FALSE' entry simply means that the match of B3 with the data in the specified range should be exact.

EXAMPLE 2:

VLOOKUP(B3,Clients!$B$3:$G$100,2,FALSE)

The same as above, but in this case the clients' details are stored neatly in a separate worksheet called Clients.

Friday, 6 May 2016

Certified insane: Anti-malware certifications

[Nearly ten years ago I wrote an article for Computer Shopper magazine about anti-virus (aka anti-malware) certifications. The words, along with my website, have long since shuffled off into archive.org. Here's a copy, which I think stands up pretty well even today, despite the progress that testers (including myself) have made since then.]

No-one likes taking exams. Imagine that you are sitting at a desk in a dusty, draughty school hall facing a two-hour test. Imagine that your future rests entirely on how well you do. Get a good mark and you stand a chance of employment. Fail, and it’s the dole queue. What a horrible situation. I hope you studied hard.

But wait, it’s not as bad as it seems because this examination board has some special rules. If you fail you can have a second chance. Not just a chance to retake, mind you. You get an extra special second chance. Pay a load of cash and you can take home the answers. You then have six weeks to memorise them before being allowed to sit the exact same exam for the second time.

I don’t know about you, but if I was an employer I’d think twice about picking staff based solely on their passing this exam. Holding such a certificate would be virtually meaningless, showing only that candidates had enough cash to pay for the answers and enough sentience to be able to memorise and repeat them. Yet this is how anti-virus companies earn those smart-looking certificates that they stick on their product boxes.

COASTING ALONG

Have a look at the box for the anti-virus software that you are using now. I bet it displays a certificate called West Coast Labs Checkmark and another called ICSA Labs Certified. If not, it will be on the company website. West Coast Labs and ICSA Labs run independent tests against security software submitted by developers. But what do you know about these tests? Do they mean that you are holding the best anti-virus program available? Or do they simply verify that you are holding some anti-virus software?

There are a few tests that West Coast Labs runs against an anti-virus program. According to the literature on its website, these include two levels of anti-virus test and a test for Trojans. The first anti-virus test checks how well the software detects real viruses from the wild. These viruses are two months old, which means that the anti-virus companies have had plenty of time to prepare their virus-definition updates. Too much time, some would argue.

The second anti-virus test addresses the program’s ability to disinfect files. This disinfection has to be good enough to allow the user to “use as much of the data as possible”, but files and documents that have been disinfected do not have to be restored to their exact, original state. Anyone who knows about how viruses work accepts that it’s incredibly hard, if not impossible, to disinfect files thoroughly. I’m not criticising West Coast Labs for its methodology here, but you might be interested to know what the industry means when it refers to disinfection.

The most sensible advice anyone can offer when you are faced with an infected file or document is that you should restore it from an uninfected backup. If you continue to use a disinfected file, you may find it is unstable, while documents that use macros may lose some of their functionality once a macro virus has been removed.

TROJAN RECORDS

West Coast Lab’s Trojan test uses a special test suite of Trojan files. Who knows what files lurk in this collection? The anti-virus companies do, once they have had their products tested. If their anti-virus software misses any of the Trojans they are sent copies of these bad files, which they can use to generate updates. Six weeks later, after an update, the program will be tested again, with the same Trojans. It is hard to imagine circumstances where an anti-virus product could fail this test.

ICSA Labs is a bit stricter, and continues to test products at least once every three months. If any fail, the developer is given seven days to fix the problem. ICSA Labs does not publish whether or not it passes samples of the viruses to the developers to help them pass the test. It’s interesting to note that neither does it test for non-replicating malware. This means that spyware and other threats that do not attempt to spread themselves automatically fall outside the test’s remit. It does have a dedicated anti-spyware test but, at the time of writing, ICSA Labs’ website reports only one consumer product as having passed. This is Trend Micro PC-cillin Internet Security 2007, which we found to be one of the least effective products in our Labs test this month.

WASTE PAPER

Does this mean that the certificates earned by anti-virus software are fluff, designed to baffle buyers with science? Not entirely. These certificates acknowledge that the software in question works on at least a basic level. After all, a company does need some expertise to be able to put together a working anti-virus program. But the thing that makes one anti-virus program better than another is its continued ability to detect threats quickly and accurately. The certification test results don’t really show whether or not the program can do this.

In my humble opinion, the testers should not share their samples. If the anti-virus companies can’t detect viruses using their own resources, their service is lacking. A good service is central to creating a useful anti-virus program because, when you buy a box of security software, you are really subscribing to a service. To be any good, this cannot rely on information supplied by testing companies, independent or not, every three or four months. The company has to have a live view of real viruses as they spread across the internet so that it can issue updates fast enough to protect computer users.

Don’t get me wrong. Testing anti-virus software properly is not an easy task, and there are lots of different methods you can use. A certificate from an independent lab is better than nothing, but I’d entreat you to view it like a basic hygiene certificate in a takeaway, rather than a rave review from Egon Ronay.

First Published in Computer Shopper, issue 230, April 2007. The above article is © Dennis Publishing Limited 2007. UK property of Dennis Publishing Ltd. This article may not be reproduced or transmitted in any form in whole or in part without the written consent of the publishers.