How to find the right formula in Excel

What is the best way to find the right excel formula?

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.

How to 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 this button 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.  

Date and time insert function

But let’s say we are looking for something super Merchandisie like stock cover.

Failed to show result insert function

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:

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.

Moving onto my last suggestion.

Ask the Internet

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.

Conclusion

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

Ax

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.