In this article let's get back to basics of Excel and discuss something I am asked quite frequently about.
What is the difference between a Formula and a Function in Excel?
If you use Excel as a rather large calculator then you have probably used it to do something like this- ( the result being 23)
=1+2+4+6+10
or
=A1+A2+A3+A4+A5
What you have done in Excel right there in the example above is used a Formula.
If you find this useful, then that is great, but Excel is way more powerful and can speed up simple calculations (like the one above) and also very complex calculations.
It contains to date approximately 400+ built in formulas for us to choose from, these are known as Functions. Some of the most well used and well known are VLOOKUP, SUM, (which I have used in the above example) INDEX or MATCH. As Microsoft release new versions of Excel, they add some and also they can retire some as well.
So to demonstrate the difference between the above Formula calculation as a Function in Excel it would look like this, which of course result in the same answer, 23.
=SUM(A1:A5)
The terms Formula and Function can be used interchangeably and often are, and essentially they give the same result - that being an output as we have seen in the above example. A Function is pre-defined by Excel and a Formula is user created I.e typed into the formula bar directly..
There a few differences to differentiate between Functions and Formulas.
Functions
Functions are grouped together in their functional categories within the Functions Library in Excel 2013 onwards if you want to take a look at all of the available functions within a category then use the drop down arrow.
What is the difference between a Formula and a Function in Excel?
If you use Excel as a rather large calculator then you have probably used it to do something like this- ( the result being 23)
=1+2+4+6+10
or
=A1+A2+A3+A4+A5
What you have done in Excel right there in the example above is used a Formula.
If you find this useful, then that is great, but Excel is way more powerful and can speed up simple calculations (like the one above) and also very complex calculations.
It contains to date approximately 400+ built in formulas for us to choose from, these are known as Functions. Some of the most well used and well known are VLOOKUP, SUM, (which I have used in the above example) INDEX or MATCH. As Microsoft release new versions of Excel, they add some and also they can retire some as well.
So to demonstrate the difference between the above Formula calculation as a Function in Excel it would look like this, which of course result in the same answer, 23.
=SUM(A1:A5)
The terms Formula and Function can be used interchangeably and often are, and essentially they give the same result - that being an output as we have seen in the above example. A Function is pre-defined by Excel and a Formula is user created I.e typed into the formula bar directly..
There a few differences to differentiate between Functions and Formulas.
Functions
- A Function is already built into the Excel software example SUM, AVERAGE,VLOOKUP
- We can nest Functions with Formulas
- Functions usually have a built in wizard to help you step through the numerous stages of creating them
- Functions can be used simplify more complex mathematics
- A formula is typed by the user directly into the formula bar in Excel
- A formula cannot be nested
- A formula does not have built in wizard. The user has to manually type the formula
- A formula is a simple mathematical calculation
Functions are grouped together in their functional categories within the Functions Library in Excel 2013 onwards if you want to take a look at all of the available functions within a category then use the drop down arrow.
B Johnston is an advanced Microsoft Excel user (MCP) with over 20
years experience in Spreadsheets and the creator of the How To Excel At Excel
Blog and Newsletter. She is passionate about helping others to overcome
their spreadsheet fears though regular YouTubeVideos and her regular
Formula Friday Blog Series. Join in the discussion and become part of
the vibrant community.
Article Source:
http://EzineArticles.com/expert/BJ_Johnston/850729
0 comments:
Post a Comment