Newsletter - Mar. '08

Spreadsheet Tips		Meet the Client
In this month's tips we look at the AND function. It allows us to search a list using multiple search criteria, which is demonstrated in the context of a marketing exercise.		Meet Working Data Client, Puddleduck Parts Ltd. which was started by husband and wife team, Julian and Jane Wharton, as a 'bit of a hobby' in 1998, dismantling and restoring Aston Martin cars. Puddleduck have now grown to become the world's largest Independent supplier of Aston Martin car parts.


Meet the Client - Puddleduck Parts Ltd

		Puddleduck Parts Ltd. was established in 1998, by husband and wife team, Julian and Jane Wharton, who started it as a 'bit of a hobby'. Based on the outskirts of Warwick, Puddleduck sell new, used and re-sourced Aston Martin parts across the globe.

Puddleduck differs from all other dealers/specialists in that, initially, cars were bought to dismantle for the spares which, over time, had become impossible to find, in order to keep many prestigious and rare models on the road. The business has now grown to incorporate total ground up restorations and mechanical work, as well as worldwide parts sales of new, second hand, re-sourced & re-manufactured parts, making the company the largest Independent in the world.

Working with a spreadsheet of over 48,000 individual parts, many with 3 separate price structures, plus a search engine parts page on the web site, was pushing the spreadsheet beyond its capabilities. The limitations for multiple users and constant erroring made it unsuitable and a source of constant frustration.. "We had outgrown the spreadsheet based system and it was starting to cause more trouble than it was worth. Working data were called in to design a user-friendly, stable networked system. They achieved this in a small space of time, at very reasonable cost and with no fuss. Listening to our needs and comments they have made our working life easier and more cost effective."

What's new?

With an expansion programme now underway Puddleduck is going from strength to strength.

Contact Puddleduck by email or telephone +44 (0)1926 624 172.

back to top


Spreadsheet Tips - The AND function
Many people who approach Working Data for bespoke software ask for help with marketing data and processes. It is impossible to replicate the functions of such a system in a spreadsheet, but there are a few select elements which can be reproduced. In this month's spreadsheet we look at the AND function in the context of a marketing task.

Preparing for a marketing exercise requires getting your data in good order, and hopefully, won't mean staying late for days on end. At the offices of our telecomms company, Talking Data, we have a spreadsheet of prospective clients. How do we identify those prospects we are targetting with our new deals? It would be excellent to be able to select those companies which have the appropriate turnover and number of employees, and are within the sector we want to approach. It would save a load of time and ensure we were taking the right deal to the right people. The AND function is an ideal tool for this multiple-criteria search. Fig 1 shows our data.

Fig 1


The blue table is where we will input our search criteria, whilst the green table shows our list of raw data. Our first step is to add an extra column to the green table, with the title of 'Prospect?' in cell E12. In cell E13 we construct the formula which will help us find our targets.

The AND function takes the list of criteria that we give it and finds out if they are either TRUE or FALSE. If all of the criteria are TRUE, it displays TRUE. If any one of the criteria are FALSE, it displays FALSE. In our example here, we want the AND function to tell us which companies fit the three search criteria we enter into the blue table.

The equation is =and(B13=B6,C13>B7,D13>B8) B13 is the sector that the company operates in, whilst B6 is the sector we enter as a search criteria. C13 is the company's turnover, whilst B7 is the minimum turnover we are targetting. Therefore, we ask AND to determine if the company's turnover is 'greater than' the figure we enter into the search field, B7. D13 is the number of employees in the company, whilst B8 is the minimum number of employees we are targetting. As with the turnover, we are asking AND to determine if the number of employees in the company is greater than the number of employees we have specified in the search field, B8. See Fig 2.

Fig 2


Upon pressing Return, the formula gives us the result 'FALSE'. As we have not entered any values in the search criteria fields, it is not possible for the criteria to be 'TRUE'. We must make one further adjustment to the formula before copying it down the rest of the column. As the search criteria fields will be the same for each row of data we look at, we need to alter the cell addresses in the formula to make sure that they are not updated as we move down the rows. We do this by adding a dollar symbol before the row and column. e.g. A1 is replaced by $A$1 In our example, we want to replace the cell addresses for the search criteria fields, which are B6, B7 and B8. We replace these with $B$6, $B$7 and $B$8. See Fig 3.

Fig 3


We can now make one other adjustment to the formula which is designed to make the results much easier to read and the finished spreadsheet tool more user-friendly. With the IF function, we can ask the cell to display something different depending on whether the AND function gives a result of TRUE or FALSE. When the AND function returns FALSE, that means that the company does not meet our search criteria, so we don't want our attention distracted by them. And when it returns TRUE, that company is one of our target prospects, so let's make it clearer. Fig 4 shows the AND function placed within the IF function. We are asking the cell to show a dash (-) if the answer is FALSE, and to show the word 'prospect' if the answer is TRUE. (You can read more about the IF function here ).

Fig 4


We can see that instead of displaying 'FALSE', the cell now just shows a dash (-), which will be much easier to overlook as we go through the table targetting 'prospect'. Now we copy the formula down the rest of the column. See Fig 5.

Fig 5


The spreadsheet tool is ready. Let's do a search for 'Construction' companies. In cell B6, type in 'Construction'. After you hit Return, you will see that the two companies have been picked out as prospects. See Fig 6.

Fig 6


Filling in the other two search criteria with �1million and 25 employees further refines our search and narrows down the targetted prospects. See Fig 7.

Fig 7


With the AND function we can search our data with multiple criteria. Combining it with the IF function then allows us to customise the output and create a more user-friendly tool which will save time and target our prospecting more efficiently.

If you have any questions about the AND function and when to use it, or if you would like to suggest a topic for us to cover in a future newsletter, please contact us.

back to top