Intermediate Formulas and Functions

Welcome back to our Excel training video sessions! In this installment, we will delve into intermediate formulas and functions that will enhance your Excel prowess. So, let's jump right in!To begin, we'll explore the average function. To use it, simply start typing your formula with an equal sign and type "ave." Excel will automatically generate the average function for you. Press tab, and the function will be completed.

Next, highlight the cells you want to calculate the average for using the arrow and shift arrow keys. Press Enter, and voila! The average value will be calculated.Now, let's talk about filling formulas. Suppose you want to extend a formula across a range of cells. Highlight the cells you want to fill, and to fill right, use the Ctrl + R shortcut. This action will copy the formula from the original cell and generate the results for the selected cells.

Moving on to the median function. Type an equal sign and begin typing "me." The median function will appear, and you can press tab to continue. Select the range of cells you want to find the median for using the arrow and shift arrow keys. Press Ctrl + Enter to fill the formula for all highlighted cells.For finding the minimum and maximum values in a selected cell range, we have the min and max functions. Type an equal sign, followed by "min" or "max." Highlight the desired cell range, and the function will generate the respective minimum or maximum value.

Now, let's explore the small and large functions. These functions allow you to select the nth largest or smallest value in a sorted array. Type an equal sign, followed by "sm" or "lg." Press tab, highlight the desired cell range, and specify the desired position, such as the second smallest value. The function will return the corresponding value. Next up, the count function. This function automatically counts the number of cells with values. To use it, type an equal sign, followed by "count." Highlight the cells you want to count, and the function will display the total count. Similarly, you can use the counta function to count all cells with any value and the count blank function to count the number of blank cells within a selected range. Let's talk about rounding.

To round a number, start typing the formula with "ro." Select the cell you want to round, specify the number of decimal places, and press Enter. You can also use the roundup function to round a number up or the rounddown function to round a number down.Lastly, we have the product and sumproduct functions. The product function multiplies values in a selected range, while the sumproduct function multiplies and adds values from two arrays. By selecting the appropriate cell ranges, you can calculate total sales or other relevant metrics.

To recap, we covered average, filling formulas, median, minimum and maximum functions, small and large functions, count, counta, countblank, rounding, and product and sumproduct functions.Keep practicing these functions, and don't forget to visit our simulation based learning platform at keySkillset.com, where you can improve your efficiency and speed in Excel.Thank you for joining us in this intermediate functions and formulas video. Stay tuned for more Excel tips and tricks in our future videos. See you next time!