Newsletter, Jan. '08

Spreadsheet Tips		Meet the Client

The IF function is the first tool to learn when interrogating your data. It follows a simple Yes or No answer routine to the question you ask.		Meet Working Data client Pink Connect. Established in 1998, Pink started in telecommunications and have grown to incorporate IT and Broadband services. With new premises in Shipston on Stour, they are a company going from strength to strength.


Meet the Client - Pink Connect

		Pink Connect is a Business Communications Company, established in 1998, based in Shipston on Stour, Warwickshire. They do everything BT does, except charge between 10% and 50% less than BT Retail. Uniquely they offer a 5 Year Price Guarantee.

Pink Connect also supply computer networks and installation as part of their growing portfolio of Business Communications services. One client was being moved from one style of computer network (peer-to-peer) to another (server). This was going to change the relative addresses of each of the computers in the office. Unfortunately, the business system which the client used to run their company was set up for the previous network. Working Data were called in to help get this client's system and their business back up and running, all in one day. We understand how much a small company relies on its system and the data, and always give a personal, jargon-free service to our clients, and our clients' clients!

Pink Connect have hundreds of business customers across the UK, ranging from national chains to growing start ups who like the simplicity of a single monthly bill for line rental, calls and ADSL as well as a high level of expert advice and personal service. Many businesses already use other providers for calls; however, few can match the service quality and competitive pricing available from Pink Connect. The simple test: Fax your last phone bills to them on (0845 450 0079) and their Customer Service Team will tell you exactly how much it would have cost, had you been a Pink Connect customer. Then you have a clear choice!

back to top


Spreadsheet Tips - Interrogate your data using the IF function"

It's another new year, and what have we done? Don't know? Then you need to get looking at your facts and figures and making sense of them. In this month's newsletter we look at the IF function. The logic behind this is asking your data a question which has a YES or NO answer. In our example, let's look at the last year's sales figures for a company selling sports equipment, called Sporting Data.

In Figure 1, we see the sales figures for 2007 as compared to 2006. In column B, the column headed "Item", are listed the products we are comparing. In columns C and D, we have the quantities sold for each product for those two years. In column E we show the percentage increase from 2006 to 2007.

Fig. 1


We want to identify all those products with a sales increase of 20% or more as a "High Flyer". To do this, we write an IF statement. In Fig 2., we write the heading in column F, of "High Flyer".

Fig. 2


Now in the first cell under the "High Flyer" heading, we type in the formula containing the IF function. With the IF function we are asking our data a question which has a YES or NO answer. Depending on whether the answer is yes or no, we will instruct the spreadsheet to give us a different output in the cell. If the percentage increase is 20% or more, we will say, put in the text "High Flyer", otherwise, just leave the cell blank.

Fig. 3 shows the formula we type into the cell. =IF(E7>=20,"High Flyer","")

Fig 3.


The way we would read that formula is, 'If E7 (the percentage increase) is equal to or greater than 20, then print "High Flyer", if not, print "", which is nothing.' Because "High Flyer" has quotation marks, Excel knows that it is text and will not attempt to do any calculation with it. If we wanted a number to be put into the cell, we wouldn't use the quotation marks.

To finish off, all we need to do is copy the formula down the rest of the column, either by clicking and dragging, or double-clicking the black cross hair on the bottom right corner of the cell, as in Fig 4.

Fig. 4


Next month we will take the IF function on a further stage and look at nested IF statements. If you would like a specific topic covered in our spreadsheet tips section, please contact us. A list showing the archive of previous topics covered is on the left hand side of this page towards the top.

back to top