Excel SUM MAX MIN AVERAGE

Part -7 – Excel SUM MAX MIN AVERAGE 

Download Excel Templates – Excel SUM MAX MIN AVERAGE

Excel SUM MAX MIN AVERAGE (without solution)

Excel SUM MAX MIN AVERAGE (with solution)

Transcript For The Video – Excel SUM MAX MIN AVERAGE 

Now having  looked at  the some of the basic calculations in terms of multiplication, let us try to use some formulas for which we will be adding some levels here lets write total salary we would also like to calculate let’s say maximum salary of our employees. The minimum salary drawn and let’s say the average salary. So we would like to calculate all these for monthly as well as the annual. So for doing that let’s start with the monthly and go immediately below the column F which is F23. Now there are two ways of doing this one is there is an excel menu which determines how the functions are. So if you have to total this function that you need to use is sum. SUM. And for this we can look at how do we insert functions. So let’s click here we go to, we can choose different kind of functions. Let’s choose SUM because that’s what we want to do. We want to sum total the monthly salary and let’s press ok. And once we have done that you need to click here and choose the numbers or the range which you want to sum total. This can be done by dragging from the top till the bottom of the employee table. So this will sum total the monthly salary of the employees and what you will do next is just press ok and you will find that the formula which is equal to sum F4 colon F21 is being executed. And the sum total comes out to be 92700 USD as the monthly salary of XYZ company. Now this was the 1^st approach. The 2^nd approach would be let me delete the, this original formula 1^st. It would be typing the formula which means you type equal to you need to type sum total SUM  open bracket and here it defines the numbers. The number 1, number 2, within this bracket you can choose the full range from F4 till F20 and don’t press enter as of now. Close the parentheses 1^st and then press enter. With this you will find that you have created your 1^st set of formulas which is the sum total of this monthly salary. Now this is how you can proceed normally when you are using excel a lot may be the 2^nd approach is much more suitable because your well versed with the formulas the 1^st approach involves uses of mouse becomes a bit tedious at times. So I would highly recommend that you must use the 2^nd approach. Now let us try to find the annual salary of the total employees. Now here I will teach you another set of approach, so lets look at home and the many here which is auto sum. Just click on this auto sum and you may find that your full formula which is equal to sum G4 till G22 is being shown in the cell. The next thing you will have to do is just to press enter and you will find the overall annual package or annual salary is been calculated using this formula. So as you may have seen you know excel being a intelligent tool it automatically identifies what you are trying to do based on your previous history and sometimes it’s really works well. So I would like to recommend that if you have used such kind of formulas please do double check on whether this formula is totally accurate. Now let us move to the next set of formulas and the approach will essentially remain the same. The 1^st approach would be to insert this function from the top and find the relevant formulas. So here we want find the maximum salary. So the formula would be MAX bracket open and the selection of numbers. So I have selected this function MAX and I will press OK and in order to choose the numbers I will have to click here and select the range. So this approach will be as simple as this, I will just press OK and what we find is the maximum salary is 9200 dollars. Let us try to calculate the maximum salary which is the annual salary using the 2^nd approach for the annual one. So this would be as we do for others we use equal to this is basically excel way of telling that anything that comes after equal would be a formula. So this is equal to and use max with a bracket open what would this mean is you need to now select this range of numbers and lets select the full range in term of annual salary and we close the bracket and press enter in order to find the maximum salary. So this is the maximum monthly salary and we have the annual salary calculated using the formulas. So let us now calculate the minimum salary as well this will be equal to the formula for minimum is MIN, this is the formula this returns the smallest number in a set of value and it ignores logical values and text. So open brackets and select the range within which you want to find the minimum and close the bracket. So what we find the minimum salary is 2100 dollars. Likewise lets quickly find the minimum for the annual salaries. MIN bracket open and lets quickly select the range and bracket closed. So this is 25200 dollar is the minimum salary. What about the average salary, there are 18 employees and each employee is drawing different kind of salaries. So what will be the average salary? So its very easy if you are using excel and again you just need to know the function. As intuitive as it can be average is the formula that we will use and the format remains again the same. Lets select the range from F4 to F21 and bracket close. So that we find is the average salary is 5150 USD. And lets find the average again for the annual salary this is bracket close and this is 61800 dollars. So as you may have seen we have written these formulas twice one in this set of column which is F and the same formula was written in this column which is G. Now there is essentially a shortcut by which you can totally avoid writing a formula in the G column if you have written that formula for the F column. Say for example lets take the case of total salary which is the monthly salary. Now for this we have already written the formula which consist of this block. Ok and we just need to shift this block from F to G block. For calculating the total salary. So the way we can do it is earlier we had learned copy and paste from Microsoft word to excel sheet. Here what we will be doing is copy and paste from MS Excel to MS Excel itself. So this means coping the formula from column F to column G. So what we will do is we will select the cell just right click go to copy and select the cell G I will do the right click again. Select cell G right click and paste. What we find is that the formula gets copied from the left to the right the difference being that since we have shifted one column here the column, the sum total for the column also get shifted. So this is the way which you can actually copy and paste your set of formulas. A regular tip for shortcuts CTRL C and CTRL V. This is copy and this is paste. Now let me show you what this shortcut tip is all about. Let me delete the annual salary again. Go back to the monthly salary, what I need to do is just use the keyboard buttons and press CTRL and C the moment I do that it gets highlighted this means that copy has been activated the next function that I need to do is paste. So where do I need to paste this formula. I want to paste this formula in cell G23. How will I do that this is CTRL and V. So the moment I type CTRL and V you will find that the formula gets replicated in cell G. So this is how you know you can work along with the shortcut tip on copy and paste.

Recommended courses