How to use the Substitute Function Formula Friday #18 Formula...Read More
Have a project in mind?
I can help bring your ideas to life. Let’s talk about what we can build together.
I’m going to take you through three different methods, I have used when looking for a new excel formula.
There will come a time for all of us when we need to use a new formula to simplify the data given to us. I’ll discuss 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 you’ll hopefully leave feeling more confident to find your formula that fits.
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 this 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.
As you can see no results. It’s important to take a moment here to defend the honour of my beau. There is a difference between an Excel Function and a formula. Simply put a formula is written by you, the user. An Excel Function is a predetermined formula written by, well Microsoft, or whoever owns Excel now. 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.
Which leads me on nicely to:
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:
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?
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
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%
Now we need to include the relevant classification. Which are included in the same file but on a different tab.
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.
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.
Moving onto my last suggestion.
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.
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.
Until next time
Finding an exact match for your needs on the internet can, at times, feel like trying to find the missing pair to your odd sock. You know it’s in the drawer somewhere, but you just can’t see it for love nor money. That’s where I can help, think of me as your Excel Agony Aunt, send in your lonely heart letters and I will find your perfect formula partner.