August 24th, 2012

Tips, Tools and Tutorials

Categories

When I first started out working with Excel, I was a bit intimidated by array formulas. Even now, I still find array formulas to be quite sophisticated… though I reckon they’re not quite as pretentious as Sponge Robert. At some point you might find yourself needing to up the ante when it comes to complex formulas or if you’re just trying to accomplish some seemingly impossible calculations. When that time comes, you might be ready to take a stab at array formulas in Excel. If you’re ready to give them a try, here are some of my favorite applications for array formulas in Excel that I use for managing Internet marketing data.

Any time you are using an array formula in Excel, you have to remember to press **CTRL+SHIFT+ENTER**. If you don’t do this, the formula will work like normal and you won’t think anything spectacular of whatever it is that you’re trying to calculate. Also, if you try to add the curly brackets to the formula manually, it will only cause Excel to assume that your formula is text and will treat it as such. When using the array formula, type your formula as you would with a non-array formula (no need to manually enter the brackets) and hit **Ctrl+Shift+Enter** when your formula is complete. Since you have to go through that step each time you edit the formula, if you have to copy that same formula to other cells, it’s best that you develop dynamic formulas so that you can just copy and paste the cell instead of the formula itself.

Though there are many benefits to using array formulas in Excel, perhaps the most notable is the fact that it can combine formulas and save room in your spreadsheet so you can analyze even more data in a smaller space. Just like how you want to keep the important information above the fold in your website design, you should approach your spreadsheet design this way too. User experience (UX) is an important piece of websites and spreadsheets alike, so optimizing your spreadsheet space is one of many ways to develop spreadsheets with the user in mind.

When it comes to analyzing data from Google Analytics, there can be a lot of data to process that is crucial to understanding what is going on behind the scenes of your website. But when you’re inundated with loads of data, it can be a lot like swimming through a maze in the middle of a whirlpool whilst trying to avoid running into swarms of jellyfish. Okay, that was actually just a dramatization, but you get the point. Too much data is just about as bad as not enough, so make the most of condensing, prioritizing, and organizing that data by using some of these array formulas in your workbooks and/or reports:

One of my favorite tools is trying to count unique values. Whether I’m trying to find how many unique URLs are in a list, or keywords for that matter, I like to use this formula:

=SUM(IF(FREQUENCY(IF(LEN(C3:C18)>0,MATCH(C3:C18,C3:C18,0),””),IF(LEN(C3:C18)>0,MATCH(C3:C18,C3:C18,0),””))>0,1))

Where C3:C18 contains my list of URLs (some of which are duplicate).

(Don’t forget to hit CTRL+SHIFT+ENTER of course.)

Let’s say I want to know how many conversions I’m getting for 5 separate pages but I also want to know how many total conversions I’m getting overall. I could try to add an additional section with formulas to calculate the number of conversions based on the number of visits and the conversion rate for each page, and then add a SUM formula to those values together (see bottom section where the red arrows are pointing in the image below).

Or, I could just use one large formula that adds each product (visits x conversion rate) for each page together. For example:

=SUM(PRODUCT([Page 1 Visits]*[Page 1 Conv. Rate])+ PRODUCT([Page 2 Visits]*[Page 2 Conv. Rate])+ PRODUCT([Page 3 Visits]*[Page 3 Conv. Rate]) + PRODUCT([Page 4 Visits]*[Page 4 Conv. Rate]) + PRODUCT([Page 5 Visits]*[Page 5 Conv. Rate]))

…yikes!

Or, I can save space and keep my formula simple and sweet by using an array in an additional row in my “totals” section (see the “after” image). I used an array like:

=SUM(C3:C7*D3:D7)

(Again, don’t forget to press CTRL+SHIFT+ENTER to make this work.)

(Cells C3:C7 represent the number of visits for pages 1-5, and cells D3:D7 contain the average conversion rate for pages 1-5. I also copied this formula for the Organic and PPC conversions.)

Note: There is a difference in the values between the estimated number of conversions and the actual number of conversions because the AVERAGE formulas [in the AVG % row] are calculating the AVERAGE of each of the pages’ conversion rates instead of calculating the number of conversions per page and then developing an average based upon that product.

By using that array, you can save yourself the space of that whole bottom section eating away at your precious spreadsheet surface area while also saving yourself the time it would take to generate that whole section’s formulas. Targeting your efforts not only helps you save time and resources, but it is the crux of any type of optimization, whether it’s workflow optimization or search engine optimization. When it comes down to doing business, it’s all about working smarter, not harder.

Scenario: I’m working with a list where I want to count how many keywords are driving traffic to a specific page that is getting over 5 conversions (per page). This may be important to know if you’re trying to target a specific keyword and URL pair that is converting well. With the list of keywords and their URL counterparts, I can run an array formula that counts how many keywords were generating over 5 conversions from that particular page.

Given the example below, I could just add a helper column that would designate whether or not the corresponding row meets the criteria I set (in this case, I want to count how many times a visitor landed on URL 4 [http://www.website.com/url-4] that lead to more than 5 conversions).

By adding a conditional formula (like =IF(AND(C3=”http://www.website.com/url-4″,13>5),TRUE,FALSE)) and then adding a SUM formula at the bottom of that list to count how many “TRUE” values appear in that list (like =COUNTIF(F3:F18,TRUE)) I can count how many times those two criteria are met. See below…

However, if I don’t want to add another column or I don’t have room to add the extra column, that’s where adding an array comes into play. Instead of having to add an additional column, all I need to do is add the following formula in cell F19:

=SUM((C3:C18=”http://www.website.com/url-4″)*(E3:E18>5))

(did I mention… don’t forget to hit CTRL+SHIFT+ENTER?)

Taking that a step further, let’s suppose that I don’t just want to count how many times that multiple criteria are met, but that I want to perform a summation of values based on all of the criteria I designate…

If I have a list of my top 500 keywords from Google Analytics and I want to be able to calculate how many visits occurred for a single page, I would normally have to sort my data by URL and then add a SUM formula for the applicable area, or add another helper column like in the previous example followed by a SUM formula, or I can accomplish this with a single formula without having to sort any of the data or add any helper columns.

Here’s an example:

Using the same data in the previous example, in my list of keywords and their corresponding URLs, visits, and conversion value counterparts, I can modify my previous formula by simply removing the “>5” criteria and adjusting the cell references so that I can get a total number of visits for a specific URL (in this case it’s still URL 4–http://www.website.com/url-4).

But I can accomplish this effect without an array and having to hit CTRL+SHIFT+ENTER by using a SUMIF formula like:

=**SUMIF**(C3:C18,”http://www.website.com/url-4″,D3:D18)

If I later wanted to add another criterion, if I start out with the array formula like

=SUM((C3:C18=”http://www.website.com/url-4″)*(D3:D18))

then if I wanted to add another condition/criterion, I can append the additional criteria in the formula quickly and easily as needed. So, in this example, if I wanted to know the number of visits that went to URL 4 that had more than 5 conversions per keyword, I’d use the following formula:

=SUM((C3:C18=”http://www.website.com/url-4″)*(D3:D18)***(E3:E18>5)**)

Considering how incredibly complex you can get with these combinations, you can get easily overwhelmed with data. Finding a way to minimize the amount of manual editing of these formulas is recommended. In the case with the previous formula, if you can list your page’s URL in cell F3, for example, you can swap out the page URL in F3 instead of having to edit it manually in your formula by using this formula instead:

=SUM((C3:C18=**F3**)*(D3:D18)*(E3:E18>5))

Have you ever found yourself trying to find out how many visits you get for a specific keyword only to find out that you’re getting visits for that keyword to two different pages? If only you could use a lookup formula and specify not only the keyword but also the URL to match it with, too. With an INDEX/MATCH array, you can do that.

When you run into issues with keyword cannibalization, you can sometimes find it difficult to get a look at some of the metrics related to a particular keyword when it applies to several URLs. Though it’s definitely a good thing to have as much “search engine real estate” as you can get, you don’t want to take any traffic away from the pages that are already doing well, nor would you want to waste any of your link building efforts pointing to the wrong URL.

In the example above, an INDEX/MATCH array formula was utilized in cells D11 and E11 that correspond to both Visits and Conversions, respectively. Though you’d still need to enter the desired keyword and URL pairs (cells B11 and B12), once you have those values listed, the formulas will lookup the Visits and Conversions for you.

Notice that it is not pulling the data from row 7 which also lists “keyword 1” but a different URL (URL 2) in cell C7. Although an INDEX/MATCH formula will select the data from the first occurrence of data that matches the criteria specified, if you were planning on looking for the data corresponding to the Visits and Conversions to URL 2, without that array formula with multiple criteria to specify the URL, the INDEX/MATCH formula would reference the data from the first occurrence (row 3).

In D11: =INDEX($B$3:$E$7,MATCH(B11&C11,$B$3:$B$7&$C$3:$C$7,0),3)

In E11: =INDEX($B$3:$E$7,MATCH(B11&C11,$B$3:$B$7&$C$3:$C$7,0),4)

(The 3 at the end of the formula in D11 corresponds with the number of columns to the right that you want the data to pull from… since the Visits column is three columns to the right of where our range is specified [$B$3:$E$7], we denote 3, and for the Conversions column we’d denote 4, and so on.)

If you plan to utilize these formulas but want to revert back to just looking up the metrics corresponding to keyword without associating a specific URL (if you intend to leave cell D11 blank at any time), you can nest your array formulas within an IFERROR formula like so:

D11: =IFERROR(INDEX($B$3:$E$7,MATCH(B11&C11,$B$3:$B$7&$C$3:$C$7,0),3),INDEX($B$3:$E$7,MATCH(B11,$B$3:$B$7,0),3))

E11: =IFERROR(INDEX($B$3:$E$7,MATCH(B11&C11,$B$3:$B$7&$C$3:$C$7,0),3),INDEX($B$3:$E$7,MATCH(B11,$B$3:$B$7,0),4))

And the fun doesn’t stop there… as you can see in the formulas I’ve mentioned above, only a few different functions were used (SUM, FREQUENCY, LEN, INDEX, MATCH, etc.). However, you can make your own array recipes using whatever ingredients (functions) you need. Whether you need to calculate AVERAGE or MAX or TRANSPOSE or whatever, you can use arrays to do some amazing calculations. Try them out for yourself or click on one of these helpful links to learn more about using array formulas in Excel:

Microsoft Office Introduction to Array Formulas in Excel – https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

Pearson Software Array Formulas – http://www.cpearson.com/excel/ArrayFormulas.aspx

About.com’s Ted French on Lookup Formulas with Multiple Criteria – http://spreadsheets.about.com/od/lookupfunction1/ss/2011-03-03-excel-2010-vlookup-multiple-values-sbs-tutorial.htm

There are no comments yet.