Average Function in Excel (Mean)
One of the most used statistical functions in Excel is Average. Calculating the Average in Excel is much simpler than it was in the school. Simply use the Average function and select the range which needs to be averaged. In the example below we want to know the average of the marks obtained by the students so we use =AVERAGE(B2:B12).
Median in Excel
Median is a function which is used to find the middle number in a given range of numbers. When you are finding median manually, you need to sort the data in an ascending order but in Excel, you can simply use the Median function and select the range and you will find your median. We take the same example as above to find the median of marks obtained by students. So we use =MEDIAN(B2:B12).
Mode in Excel
Mode helps you to find out the value that occurs most number of times. When you are working on a large amount of data, this function can be a lot of help. To find the most occurring value in Excel, use the MODE function and select the range you want to find the mode of. In our example below, we use =MODE(B2:B12) and since 2 students have scored 55 we get the answer as 55.
1. Minimum and Maximum Formulas
Revelation keeps a spreadsheet with information including the state, model, number of units, unit price, and total revenue for each product per state. The past year’s product sales are arranged as follows:
You need to find which products have the smallest and largest demand. This is a small list, but if you sell or resell a lot of product, the following formula can be invaluable. You can find minimum and maximum units easily with the MIN() and MAX() functions.
- In cell B15, type “=MIN(C2:C13)”.
- In cell B16, type “=MAX(C2:C13)”.
You now have a quick report of the fewest number of units sold in a state (102 tablets in Iowa) and the most sold (450 laptops in Illinois).
2. Top k and Bottom k Formulas
Suppose you’re interested in more data – not just the lowest selling item but the three lowest. You can find these with the SMALL() function. To use SMALL(), you’ll need two parameters:
- The same range or list of values as you used for MIN().
Note: If you are using multiple values instead of a single continuous range, you’ll need to place each set in parentheses.
- The value k, which is the desired position from the bottom of the list. If you’re looking for the smallest value, then k = 1. To find the second smallest value, k = 2, etc.
To find the largest three values, use the LARGE() function with the same parameters as SMALL().
Note that SMALL() with k = 1 produces exactly the same result as MIN(). Similarly, LARGE() with k = 1 yields the same as MAX().
3. Conditional Minimum and Maximum Formulas
In some situations, you might need a minimum value that meets specific criteria. For example, you might want to know the fewest units sold for the spring quarter duration or for a specific product type.
Excel provides SUMIF(), COUNTIF(), and other helpful conditional formulas. Unfortunately, there is no MINIF() or MAXIF(), but you can create the same effect with a slightly more complicated method called an array formula. An array formula evaluates a range of cells instead of a single cell.
Typically an IF() formula tests the truth value of a single cell, but as an array formula, we can force it to evaluate each cell in a range. With an array formula you will get an error if you just press enter – #VALUE!. Remember to press CTRL, SHIFT, and ENTER after you finish your array formula.
Let’s find the minimum value for desktops. First, type the matching value (desktop) into the cell you compare your function to. If you are looking for desktop values, then type “desktop” in B18. The formula below will compare that cell reference to the range you are testing. Nest the MIN() and IF() statements as follows: “=MIN(IF(B2:B13=B18,C2:C13))” and press –.
The three formulas in rows 18, 19, and 20 calculate the minimum numbers of desktops, laptops, and tablets sold. You can write identical formulas with MAX() to find the greatest number of each product sold.
Excel Tips & Tricks is written by the Microsoft® Excel® experts at Fred Pryor Seminars and CareerTrack. Known for our extensive Excel training, we offer some of the best in the business. Don’t waste precious time trying to figure things out on your own. Attend one of our outstanding Excel courses and gain the knowledge you need to use Excel more effectively and efficiently. Check out an Excel course coming to a location near you when you click here.