I’ve got 13 Function Categories but my formula ain’t in 1

Understanding Excel’s Function categories

If you’re like me, you’ve probably tried, and failed, to find a formula you need using the Insert Function search box in Excel. Seems like there are so many options but what you really need can not be found.

One reason for this is that you are looking in the wrong place. Looking for a formula rather than a Function.

I won’t go into the details for each of the 13 categories because, well long. But microsoft has put together a very helpful fact sheet, that does just that. And the Excel version compatibilities. So. Moving on.

The difference between a formula and a Function

Okay so. I’m not going to lie, it took me a while to get my head around this too, but a formula and a Function are two different things. I like to think of the formula as the instructions and the Function as the action. For example =SUM is a function, but without the instructions of what to sum nothing will appear.

Technically speaking the formula is the written expression and the Function is a predefined calculation, but who’s splitting hairs.

Okay, so let’s split hairs for a moment. But only because I am nothing if not thorough. Going back to the =SUM example. 

      – As a true formula this would read =(a2+b2+c2+d2+e2) 

      – When using the SUM Function it would read =SUM(a2:e2).

All because Excel knows; whatever sits within the parentheses, brackets to you and I, needs to be added together. Based on it’s predefined instructions.

      – Another way to write it would be =SUM(a2,b2,c2,d2,e2)

Now that’s all cleared up.

Why can I still not find the Function I need?

Now that we know the difference between a formula and a Function the likely answer is because you are not looking for a Function but a formula.

I know I’m losing you and the frustration is building but stay with me.

There are a ton of great Functions in Excel

VLOOKUP is one I am sure you are familiar with. SUM we have just discussed. TODAY, DATE, WEEKDAY, IF, AND, IFERROR, COUNTIF, CONCATENATE, FIND, TRIM, LEFT, AVERAGE. My personal favourite SUMIFS and many more.

But if you are looking for margin, in a non-financial purely Merchandising or Marketing sense you are out of luck.

Not because Merchandising and Marketing formulas aren’t important. Far from it. From what I can gather it’s because the formulas we use include changing, non-static, variables or the formulas are too simple, not specific enough.

Let’s go back to =SUM. Yes it is a simple Function but it is also specific.The instruction is to add together the values of specified cells.

Hypothetically if there was an =COVER Function the instruction would be to divide your sales by your stock. 

But that is the same instruction as

      – open rate, dividing the number of sent emails by the number of times the email was opened,

      – and don’t forget sales per store, dividing the number of units sold by the number of stores that recorded a sale.

So =COVER as a Function is too confusing for the user as well as Excel. There are too many potential outcomes for dividing one number by another so it’s not worth creating specific Function for it.

I don’t know for sure that that is the reason, but it makes sense. And to give a shoutout to Occam and his razor, the simplest explanation is usually the right one.

So what to do now?

I would recommend trying out a few of the different Functions that they suggest when using the search box on the Insert Function tool. Nothing wrong with some independent learning, and who doesn’t love to learn a new excel trick. But when that gets boring, and it will, think about what it is you need the formula to do.

Try writing out what it is you want to achieve. I find once I can see what I want to find. It is easier to work out how to translate it into something Excel will understand.

But if you are really stumped, ask the Internet. It is a font of knowledge and the key teaching tool I used to gain my self-proclaimed wizardry status.

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.

Until next time.

Ax

One reply on “I’ve got 13 Function Categories but my formula ain’t in 1”

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.