How the Mighty =If Formula Can Help Your Accounts
Published: May 20, 2014
Author: Spencer Fair
The =If formula is a simple formula that can be used in a lot of fun ways. I use it a lot to parse out specific sections of data from a larger set of data.
The formula breaks down into three parts (three comma-separated parts of the formula):
The first is used to check for something, whether a specific formula or a specific word.
The second is what the =If formula will return if the first check is TRUE.
The third is what the =If formula will return if the first check is FALSE.
This is a simple example. The =If formula will check if A1=10. If it does, it will return the word “Yes”; if it doesn’t, it will return the word “No”.
You can use the =If formula with other formulas to find words instead of numbers.
This uses the search formula to find “_B_” in a specific cell, in this case the campaign column of data. Using naming convention, we know any campaign with “_B_” indicates the campaign is from Bing.
If the Search formula finds “_B_” it will return the number where “_B_” starts in the cell.
This is why the search formula is then wrapped in an ISNUMBER formula. It verifies whether or not the search formula found the word/symbols we were looking for. If “_B_” is in the cell, the search formula will return a number and the ISNUMBER formula will return a TRUE. If it does not find “_B_” it will return a #value! And the ISNUMBER will return a FALSE. The ISNUMBER results will tell the if statement whether to return the true value (second part of the if formula) or the false value (the third part of the if formula). In this case, if the search formula finds “_B_” the if formula will send back Bing, and if it doesn’t find “_B_” it will fill in Google.
That’s great, but what if there are legacy campaigns or campaigns that do not follow the naming convention? Maybe I want to be sure only Bing campaigns get labeled as Bing campaigns and only Google campaigns get labeled as Google campaigns (not everything that isn’t “_B_”).
The awesome trick of the =If formula is that the true or false values can be formulas as well. This allows for a lot of really cool possibilities.
For our example, this is how it could look:
The example makes the formula a bit more complicated, but bear with me. The formula is identical to the earlier Bing/Google check, except in the FALSE value, I put in another =If formula.
The second =If statement will only run if the first check (first part of the If statement) returns a false value.
If the first =If statement returns false, it will run a second =If statement IF(ISNUMBER(SEARCH(“_G_”,L31)),”Google”,L31)
This does the same as the first except it checks for the Google naming convention in the campaign name (“_G_” in this case).
This prevents campaigns that are outside the naming convention from being included in the false value.
This turns the =If statement from a yes, no formula into a multi-variable parsing formula, allowing you to pull multiple values from a cell with a single formula.
In the example, you can see the second =If statement (in the false value of the first if statement) has its own false value. I have it set to return the cell it is checking. This means if it finds neither “_B_” (Bing) or “_G_” (Google), it will return the whole campaign name. You could set this to any value or word ex: “unknown”…etc.
The formula could be fixed further to have a third and fourth if statement to check for Google and Bing as full words as well.
This leaves the =If formula open to a long string of if statements inside of =If statements to parse out a specific set of values/data.
An example of this would be pulling out all geos you specifically target from the campaign names:
=IF(ISNUMBER(SEARCH(“_NY_”,B2)),”New York NY”,IF(ISNUMBER(SEARCH(“_LA_”,B2)),”Los Angeles CA”,IF(ISNUMBER(SEARCH(“_CH_”,B2)),”Chicago IL”,IF(ISNUMBER(SEARCH(“_SF_”,B2)),”San Francisco-Oakland-San Jose CA”,IF(ISNUMBER(SEARCH(“_DC_”,B2)),”Washington DC”,IF(ISNUMBER(SEARCH(“_MA_”,B2)),”Massachusetts”,IF(ISNUMBER(SEARCH(“_WA_”,B2)),”Washington”,IF(ISNUMBER(SEARCH(“_CO_”,B2)),”Colorado”,IF(ISNUMBER(SEARCH(“_SD_”,B2)),”San Diego CA”,IF(ISNUMBER(SEARCH(“_TX_”,B2)),”Dallas-Ft. Worth TX”,IF(ISNUMBER(SEARCH(“_PA_”,B2)),”Philadelphia PA”,IF(ISNUMBER(SEARCH(“_MO_”,B2)),”St. Louis MO”,”Misc”))))))))))))
Seems complicated, but all it is really doing is step-by-step checking for each geo-targeted name in the campaign naming convention and labeling each for easier data separating later on.
This is just opening you up to the uses of the =If formula; it can accomodate varied formulas inside it to do many different operations. Play around and try combining multiple formulas together inside an =If statement to get exactly what you are looking for.