Over at twitter, @forthemoves asks,
@For_the_moves Same as growing your vocabulary. Remember, words (or functions) = ideas. the more you know, the better you can think.
— Chandoo.org (@r1c1) October 12, 2016
That got me thinking. How many functions should you care to learn?
My answer
Simple. Think of functions as words. The more you know, the better you can communicate with Excel. It is just like building your vocabulary. That said, there is no point memorizing every last one of them. And let’s be honest, its not like you are going to suddenly start using ACOTH(), BAHTTEXT() or COMPLEX(). But learning just SUM() and IF() is going to get you in to trouble quickly. Go beyond the basics and pick up necessary formulas to become awesome at work.
Here are few places to start your journey – Top 10 formulas for analysts & Beyond IF() and SUM() – 15 formulas for you.
What do you think?
How many formulas should you learn? What formulas are must everyone working with Excel know? Please share your thoughts in the comments section.
Note: It is a long weekend in NZ (labor day). Although every week is a long weekend for me (I don’t go to work, I am happily self-employed). That said, this one is special as kids get an extra day to enjoy. So we are going on our first ever overnight tramp (hike + stay in woods). We are heading to Rimutaka forest park. Next transmission will be on Tuesday. Enjoy your weekend. And if you get bored, you know what to do.
16 Responses to “How many formulas should you learn? [Weekend Poll]”
This is an interesting question. Firstly, I agree with the sentiment that the more you know the more you can understand and do.
However, the FAST financial modelling standards say we shouldn't use too many Excel functions, don't use OFFSET and INDIRECT ... don't use ROUND ... and more.
Now what?
Duncan - that's interesting! I can understand advising not to use indirect and offset as they are volatile (cause formula recalculation), but what is the rational behind round?
I think it's worth it to build up that vocab - you never know when you're going to be able to whip out a little known formula to save someone a bunch of time. Just this week I was able to save someone concatenating 100+ email addresses manually by showing them textjoin.
Good to see you exploring our beautiful country Chandoo! We're doing the Abel Tasman hike this weekend and are so pumped!
I use OFFSET and I use INDIRECT as they can solve major problems easily in both cases. I use ROUND. The standard says many useful things but then it tries to limit what we think! That cannot be a good thing.
They deprecate nested IF statements which I understand but then don't mention IFS even though the standard was published long after IFS arrived.
Then again, the standard says never create a formula longer than your thumb and every formula should take no longer than 24 seconds to understand! How long is your thumb? What screen resolution are we using? Why 24 seconds??
In summary, when I started my professional product cost modelling work all those years ago I learned as much as I possibly could. Now people think I am a genius but it's certainly true that I can solve practical problems that save people time, that stop them making mistakes and even allow them to do things that are otherwise impossible!
The functions you need are the ones to learn. Problem is knowing the formulas you need. So I agree with your answer Chandoo to go beyond the basics. And I like the analogy between functions and vocabulary.
I have to agree with GraH. It seems every time I start a new project I'm delving into new territory with functions. Nested IF's when doing time expired calculations, percentage changes to create a variance range, concatenate is very useful even with text, COUNTA can be a life saver and my latest new one was building a calendar. Chandoo, will smile now, but VLOOKUP has to be mentioned.
yes, day by day I am getting fascinated by new functions of excel..and the joy I get while learning it and using the same n my work is similar the one I used to experience learning english vicabulary during my childhood dsys
DYNAMIC (CASCADING) DROPDOWNS
Whilst I now use a vast amount of Excel formulas and VBA, the formulations that I have used extensively on almost every dashboard and interactive sheet since discovering them in 2013 have been INDEX, used to create dynamic named ranges and applied to data validations but also to create pivot tables that automatically expand and reduce as I change the source data table. These formulas were literally game changers for me when I discovered them, along with the equally powerful INDEX/MATCH, and marked a turning point in my professional career.
As an example, If I had a data table from A1:C4 with headers, my formulas for data validation based on Column and Row would be:
Column: =named.range1
Formula for named.range1: = $A$1:INDEX($A:$A,COUNTA($A:$A))
Row: =named.range2
Formula for named.range2: =$A$1:INDEX($1:$1,,COUNTA($1:$1))
For the pivot table , the source would be named.range3
Formula for named.range3: =$A$1:INDEX($1:1048576,COUNTA($A:$A),COUNTA($1:$1))
These were powerful enough but then Chandoo did an article on 13th Feb 2014 ‘Dynamic (Cascading) Dropdowns that reset on change’. This one rocked my world and not only did it mean that I could create cascading dropdowns without VBA, I also discovered the EXCEL masters that are Roberto Mensa and Jeff Wier – just read their comments exchange and how passionate they are about Excel formulation and VBA!– mind blowing.
Thank you Chandoo for all that you have given.
Interesting question.
To me, it's not about the number of functions we know. It is about how well we can "combine" the functions we learned and work out a solution for the problem we encounter.
To be honest, who would say he/she knows all the 400+ functions in Excel. Even though they do, how many function will he/she use in daily work?
If we know say 20 functions, AND we know how to write nested formula, it means a potential of =COMBIN(20,X) combinations already...
In a nutshell, quantity is one thing, quality is another. 🙂
Excellent point.
Hi Sir
Is there any Podcast supported software for Windows?
Thank You,
Bhavani Seetal Lal,
Hyderabad.
I find that I continually use Vlookup(), Sumifs(), Countifs(), and I'm starting to memorize index()match(). I still need to look it up a fair bit though. When I'm doing a lot of importing into the Work Force Management tool, I find that I need to force Dates into text so TEXT() is easily remembered for now. 🙂 Indirect I don't tend to use too much, but it can be handy for a lot of template work. Biggest time save for most of my work however is when I finally got comfortable with using Pivot Table.
Next on my list is trying to simplify my life with Get & Transform (Power Query) and Power BI.
I have to point that language knowledge you have active, passive and any knowledge. To EXCEL function:
Active – you know the formula, his syntax and usage.
Passive – you know that the formula exists, but you don’t know exact syntax or usage.
Any knowledge – you don’t know the formula
I mention three example:
First SUBTOTAL – I use daily/active SUBTOTAL(9, …..) for SUM the numbers, but passive I know that is possible use SUBTOTAL for MIN or MAX but I don’t know the parameter.
Second my UDF for wood volume for staying tree which I have written two years ago. I wrote it for one project and today have only passive knowledge about it.
Third I have personal workbook there I have mentioned some interesting formulas and it usage, many of them is from Chandoos website (like the next the 13. Friday).
So my answer for the question “How many formulas should you learn?“ You need to learn active only this formulas you use daily (in my case is maybe 40 or 50), and passive I know almost all EXCEL formulas or maybe opportunities which EXCEL give.
I personally newer learn any financial formulas (like NPV) because I prefer to make clearly calculation model which I could adjust exact for the problem (e.g. years rate, monthly payment).
In my opinion number of formulas and which formulas to know will largely depend on your roll. Financial functions are must, if your roll is in finance/accounting. Statistical functions, if your roll is in survey data analysis etc.
But I think following formulas are useful regardless of your roll.
Text functions - Trim, Clean, Left, Right, Mid, Substitute
Math - Sumifs, Averageifs, Sumproduct, Countifs, MOD, INT
Logical - IF, IfError, Or, And
LOOKUP - Vlookup, Lookup
Combo - Index/Match, Index/Small(If())
But with Internet resources being what it is. It is far more important to know how to search for right info. If you know what to look for, you will find the answer or something that you can adopt to your need.
Good points especially the ability to net search.
As many as you can within reason.
Everyone has different needs; the ability to match a solution to a problem is far more valuable than knowing all the solutions.
Cheers,
Harry.
I use some other funky ones from time to time.
I use a lot of Month, Weekday and Hour formulas for categorical classification.
I also to a lot of AND/OR.
This morning I used CountBlank\CountA to tell me how many members were missing in an array.
For a quick and dirty analysis, I often use Correl to see if two arrays might have a statistical relationship. (I just found out there's a Pearson function which does the same thing.)
Anyway, funky stuff.