As a Merchandiser or Ecommerce Manager if you’re asked how you would rate your Excel skills. The person asking is likely referring to how confident do you feel using Excel to make calculations and analyse data. Rather than asking how confident you feel using it to store, organise or format data. Though these later skills are important, as a Merchandiser or Ecommerce Manager much more of your time in Excel will be spent on analysis.
For some, Excel will have been first introduced at school. Making its interface a familiar sight once in the work place, and muscle memory soon kicks in to help you to locate the common features. For others the interface of Excel is less familiar and figuring out where things are and how forward cover, conversion rate, ROAS or OTB are calculated isn’t as straight forward as it might seem.
What is a formula, and Why Do they Matter?
In Excel formula is the term used to describe the instructions input for the Function to return the desired result.
How to find the right formula in Excel
In this post I will cover three different ways to find the right excel formula. I’ll include my thoughts on each option, how effective it is, how accurate the result and whether or not I got to the desired result. By the end of the post you’ll be feeling more confident in the best way for you to find the right excel formula.
1. Use the Excel insert function window
Straight out of the block, this is my least favourite option to use. I have tried to use it so many times in my career as a Merchandiser and very few times has it actually delivered on what I asked.
To open the window you will first need to click on the Insert Function button.
Excel functions are split into 13 categories, so if you know you are looking for a formula related to Date & Time for example, you can select that category. And each of the listed functions will have a brief explanation of what it does underneath.
But let’s say we are looking for something super Merchandisie like stock cover. Searching for this in the Insert Function window will return no results this is because there is a difference between an Excel Function and a formula.
What are Excel Functions?
Excel Functions are predefined formulas that will perform the desired calculation (or format) based on the structure and criteria of the given instructions. It might be helpful to think of the Function as the name of the formula, for example SUM is the name of the formula that can add the values together of a given number of cells.
What’s the difference between a Function and a Formula?
It might seem like semantics but there is a difference between a Function and a Formula. After all a Function will only return a result if it is given instructions that will allow it to complete the calculation. Whereas a formula will return a result based on the given values and math symbols.
Therefore the Insert Function window will rarely show Merchandising or Marketing specific formulas. I’m guessing it’s because they can be written by an individual most of the time. Or because a lot of the formulas we use are combinations of multiple functions.
2. Master the Functions you know
Being the Excel wizard that I am, this is my favourite choice. The more you know and understand the Functions you use most often. The easier it will be to switch between them to get the result you want. And you will be able to write formulas like this:
=IFERROR(IF(Z12/(SUMIFS(Z$12:Z$1537,$K$12:$K$1537,$K12))>0.04,VLOOKUP($A12,CAMPAIGNS!$A:$H,8,0),” “),””)
This formula combines four different functions.
The end result of this particular formula is to return the classification of different items based on their sales contribution to their categories. Sound familiar?
Let’s walk through it together
A request comes in, “we need to see which of these items are performing against their classifications (pre-determined) we only want to include items which are contributing at least 4% to their respective categories”
First thing we need to do is identify the sales contribution
=Z12/(SUMIFS(Z$12:Z$1537,$K$12:$K$1537,$K12)
In this example column z is the sales and column k is the category. With this formula we are only dividing our items sales, by the sales of other items within the same category.
A sumifs formula is a really good way to summarise data based on set criteria.
Next we need to make sure we only include the classification of the item if the sales contribution (above formula) is greater than 4%
=IF(Z12/(SUMIFS(Z$12:Z$1537,$K$12:$K$1537,$K12))>0.04
Now we need to include the relevant classification. Which are included in the same file but on a different tab.
=IF(Z12/(SUMIFS(Z$12:Z$1537,$K$12:$K$1537,$K12))>0.04,VLOOKUP($A12,CAMPAIGNS!$A:$H,8,0),” “)
In this example the predetermined categories are on the campaigns tab. The item cell is in column A, on our active tab and the campaigns tab. Therefore on the campaigns tab we need to look from column A, our reference column, to column H, our results column, to return the correct classification. Finishing off the IF Function with a blank for any result that is less than 4% sales contribution.
Finally we will wrap this formula up in a nice neat bow so that our spreadsheet is pleasing to the eye with the Function IFERROR.
=IFERROR(IF(Z12/(SUMIFS(Z$12:Z$1537,$K$12:$K$1537,$K12))>0.04,VLOOKUP($A12,CAMPAIGNS!$A:$H,8,0),” “),””)
This part of the formula says if after the sales contribution is calculated, the classification has been looked up but there is an error, leave blank.
An error in this example would likely be because the item is not included in the list of classifications.
I hope that has helped you to see the benefits of mastering the functions you currently use. By stringing this set together, three different calculations have been able to happen in one cell. Keeping the column count down on your document and presenting the desired data in an easy to read format.
3. Ask the Internet
As the saying goes, “you don’t know, what you don’t know”
I have learnt more complex formulas from conversions with Google, and the sources it provides, than I would have if I went on an Excel course. Not to say Excel courses aren’t useful and valuable. But I don’t think they will be the answer to all your Merchandising and Marketing queries.
Asking the Internet is time consuming, and frustrating. Less so than using the Insert Function option, but it will take a few tries to find the formula that fits your needs.
Like this post? You should see my newsletter.
Subscribe to the Excel & ME newsletter to get updates that will empower you to inform your business decisions with data.
Closing
The best way to find the formula that fits is to get up close and personal with the functions you already know. And when that doesn’t work talk to Google, or me. I can be your Excel Agony Aunt or Couples Counsel send your Excel queries to me here.
