In the previous section of this book we talked about earlier, We began to talk about building calculations within Excel. And we saw just a simple little calculation rework, We were able to do some additions as far as referencing cells. We were able to take a cell reference such as before and then add that to cell reference B5 then add that to B6 and B7, We were able to do some addition.
You have got all your mathematical operators available to you. You have got the addition symbol you have got minus subtraction multiplication you have got division, you know your exponent you have got all those various operators that you can utilize to perform arithmetic type calculations math.
Built into the Excel application the nice people at Microsoft have built several functions, functions that we can then utilize to perform calculations.
And we talk a little bit about what Excel functions are how we can utilize them to help us become more efficient as we work with our data inside of Excel.
Excel Function: A predefined formula that performs a calculation
So, first off an Excel function really on a cell function is a predefined calculation.
Now the nice people at Microsoft have built a few hundred different functions for us to use, these predefined little packages that will do some type of calculation whether it is summing up a range of cells, it is finding an average within a range of cells it is looking up information based on criteria that we give the application it could be all slew of things, I want to be able to find my house payment or the next car that I buy how much am I going to pay for that monthly built into Excel , then have a function that will do that for you.
Now, in-order-for us to utilize a function, we need to really understand what the function is and what are the different pieces of a function.
So, let me give you an example here.
This is going to be one of the first functions that we see inside of this book, this is the most common function that is used worldwide within Excel, this is the SUM function, here is an example.
This is taken right from the exercise file from the Monthly budget worksheet that we are creating together as a group.
The SUM function as you can see here, I want to build a sum up. I want to add up a range of cells well in order for me to do that we can do it like we did earlier, where I say equals this cell plus this cell plus this cell plus this plus the cell plus the right, I can do that and do that.
What if you have thousands of records and you want to add up you want to sum up all that data, I could take forever, Taping that out like we did earlier. built into Excel we got the SUM function that will streamline those efforts.
Now I want to break down functions in general, there is really three different pieces that we use as we build out an Excel function. We just saw a quick preview of some function both now, break it down into its three different pieces.
The first data and we have actually already seen this, every calculation from the simplest calculation to the most absurd complex were huge dynamic whatever huge function right or calculation they all start with the same thing from the most basic to the most complex they all start with the equal sign “=”
= FUNCTION NAME
The next step as far as using a function you then need to designate or denote which function you want to use. For example if I want to sum up a range of cells let us say = and I will bring in the SUM function, that is its name sum assume, if I want to find the average of a range of cells let us say =average.
The thing about learning functions, it is just that you got to learn the functions we have got to know about, there is a some function, there is an average function, there is a function for looking up information, there is a function for finding the minimum value from a range of cells or the maximum value from a range of cells. We got to learn what the functions are.
But once you got that down then there is one more piece, we got to bring in the function arguments or the parameters that that function needs.
So, let me give me an example of a complete function here.
This is what we were looking at earlier, It is got all three pieces, It is got the equal side. Maybe that is a given you always do that with any type of calculation, then you got the function name, In this case you got some. And then the sum function specifically within the parentheses needs to know the range of cells, that you want to sum up in this case equal sum “=SUM”. Let us sum up cells B4 through B8 and as far as functions are concerned when you deal with a range of cells to say through like B4 through B8. We use the colon character B4:B8, that is your through that is your range of cells.
So that is really the three pieces: equals, the function name and any arguments or parameters that that function needs in-order-for it to do its job.
Remember these functions there are predefined the nice people Microsoft built them, they determine how to how we use them and what types of data it needs to do its job. That is where the arguments come into play.
Now the next thing I want to bring your attention to, and we are going to get familiar with Function Arguments window.
You click fx icon then select any function you want (example SUM) then click OK button
This is called the Function Arguments window.
This is a little window built into the Excel application that is going to assist us in utilizing these functions, especially when you are just starting out utilizing functions, whether it is the same function or something more complex like Index and Match or Vlookup, or some other more complex function, you are going to want some help.
So, this function argument window is going to come in very handy. Because it will kind of take your hand and walk you through the process and explain each step to you.
Now here I am looking at the arguments for the sum function. You can see number 1 and number 2 there, the number 1 and number 2 they are just expecting a range of cells. You want to sum what is it that you want to sum.
Here I just said I want to sum before through and recall in through B8.
The nice thing about this windows gets it kind of takes your hand watch it through the process, they give you some great information, they are going to tell you what the function does.
The next thing it tells you is what of those arguments, What is number 1 what is number 2.
It says number 1, number two and so on. You can have between one and two hundred and fifty-five numbers to sum or ranges of cells that you can sum up to under 55 that is quite a bit.
It is also down the lower left-hand corner of the window is a nice little link that will jump out to a Microsoft page that will give you some help about the active function. In this case if I click that link help on this function in the lower left it will take you to a Microsoft page that talks of all about these some function, super slick, Very helpful.
So, this is your arguments window again excels got all sorts of functions in there as you are learning them.
I highly suggest not only going through this book and learning the functions, I am going to walk you through but read through the window, get an idea of what they are all about and potentially what you are going to be using for and what you could use them for.
Now like I mentioned there are loads of functions inside of excel by the last the last number that I read as of the 2013 edition, We have actually a few versions later. Now there were 461 functions built into the Excel application.
Then Microsoft adds functions they remove functions. And typically when they remove a function they replace it with something else , something that is more streamlined more efficient does more for you, it is more accessible those types of things.
There are loads of functions inside of Excel.
Here this is a part of the ribbon inside the Excel interface, this is the formulas tab. You will find the form is tab there at the top of your screen.
Here they give you what is called the function library essentially a bunch of little books where you can go find all these various functions.
Then we are going to take-a-look at this we are going to become familiar with it and how you can find the various functions that live inside of your copy of Excel, get ready for this, I am excited.
I love functions any way that is going to save me time and help me become more efficient as I am performing calculation great.
First we got to start out what is more basic type functions like some Min, Max, Average, Count those types of things and as you progressed later and later inside of this book, we are going to see more complex more advanced those dynamic functions, I spoke about earlier. Get ready for it, Let us jump into the next lecture.