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. Spreadsheet Data 1 shows our data.
Spreadsheet Data 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 Spreadsheet Data 2.
Spreadsheet Data 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 Spreadsheet Data 3.
Spreadsheet Data 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. Spreadsheet Data 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 ).
Spreadsheet Data 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 Spreadsheet Data 5.
Spreadsheet Data 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 Spreadsheet Data 6.
Spreadsheet Data 6
Filling in the other two search criteria with �1million and 25 employees further refines our search and narrows down the targetted prospects. See Spreadsheet Data 7.
Spreadsheet Data 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.
