Fantastic Formulae and How To Use Them

Is the image of a digital marketer, focused on tweaking a long formula while sipping from an ‘I heart spreadsheets’ mug full of coffee a thing of the past? Well, perhaps, but formulae are still important so we’ve gone into our Kinase favourites and how they’re useful for digital marketing

Optimisation may be done by bidding algorithms, but there’s still data to crunch, ad copy lines to edit, and insights to be gleaned using a spreadsheet. Excel and Google Sheets remain core skills. So we’ve drawn together some Kinase favourite formulae you shouldn’t miss - and outlined how each one is useful for digital marketing.

EOMONTH

EOMONTH returns the end of the month, starting from a date and moving forward X number of months. Very useful for phasing and forecasting. Don’t sleep on this one, it’s not just built for accounts departments working out deadlines for invoices.

XLOOKUP

So you learned a vertical lookup (VLOOKUP) to find data - then added horizontal searches with an HLOOKUP. But have you tried an XLOOKUP?

It looks up in every direction - so if your database or array is large and you don’t want to rearrange it just for your lookup, then going for XLOOKUP makes sense.

SUMIFS

Alongside VLOOKUP, this is a stone cold back office go-to which can be wielded to make your data analysis that little bit more elegant.

Best thought of as a way to qualify basic sums, you can add in a range and qualifier (including wildcard searches). Things really kick off with a nested set of SUMIFS in which the qualifier for one sum is in itself a SUMIF…

ARRAY and PRODUCT

PRODUCT allows you to multiply cells together in a simpler syntax than a long string. You can multiply, say, 6 cells together with

=PRODUCT(A1:A3, C1:C3)

Combining SUMPRODUCT with a set of ARRAY formulae can then give you an easy way to construct a ‘Total’ value for a set of tables, for example. It’s a classic combo for a reason.

POWER

Useful in forecasting - in particular, for calculating diminishing returns on your metrics once you have a trend line established. Use POWER to extrapolate from your trend line equation what the impact of X (i.e. increasing clicks) would be on Y (i.e. conversions).

REGEXMATCH

Regular Expressions (REGEX) allow you to find and match from long data strings in Google Sheets using an expanded syntax much more powerful than the more familiar ‘wild cards’. Useful for parsing search and geographic data where text and numbers are mixed up.

Find which patterns are useful for you. For example, find data that is ‘not a digit’ using the ‘\D’ pattern. Or extract urls, postcodes or extract text from a longer string.

Note: in Excel you’d have to use a third party plugin, but it’s still possible to use REGEX there.

IFISNUMBERSEARCH

ISNUMBER returns true or false depending on whether a cell contains a number. But used to power a search, it can also then search for particular text in substrings i.e. search for ‘square’ in a list:

=ISNUMBER(SEARCH("square", A2))

It could also search for numbers within a range, for example greater than zero: 

=ISNUMBER(A2:A5))>0

GOALSEEK

An algorithim rather than a formula as such, but hey: GOALSEEK is found under ‘What If’ analysis in Excel. It back-projects the missing gap in a series of steps - you know the outcome and it fills in what you would need in a data set to achieve that outcome. In other words, you ask it to achieve X result in a given cell, by changing Y parameters in a set of other cells.

For example, what interest rate is implied by a target loan amount repaid over X number of months? Let GOALSEEK tell you. Or if you need a grid of geographic bid modifiers to net out at the same overall cost before and after an optimisation, GOALSEEK could map that out.

CORREL

How strong is the correlation between two data sets? CORREL is a quick first pass at telling you. Try it. We think you’ll like it.

LINEST

This is one for the real Excel heads. LINEST uses the ‘least squares’ method to return a linear trend formula for a dataset.

Often in digital marketing, you have a data set and want to analyse the trendline. So you create a graph and then look at the equation from the line produced. LINEST can cut straight from the data to the equation. And if you want to go beyond a linear result, then this could be the start of your LINEST journey. 

QUERY

Your Excel file or Google Sheet has sprawled and become a database, or part of one. Now your old analysis methods have become unwieldy. It’s time to get to grip with QUERY formulae.

These are ways to interrogate large datasets; sets from multiple sources; or for aggregating attributes of data based on criteria. Basically, they are ways to use your database. Custom reports and trend analysis begin here.

It’s going to take some tutorials to get going at this level, but bear in mind that there are different query languages depending on the software you’re using, or it may be possible to use Structured Query Language (SQL), as in Excel. For example, Google Sheets has a different and simpler language which powers its QUERY formula.

Read more Kinase How To guides