Explanation In this example, the goal is to calculate a quiz score average for each person listed in column D using the four scores in columns C, D, E, and F. The standard way to solve this problem in Excel is to use the AVERAGE function . AVERAGE function The AVERAGE function calculates the average (arithmetic mean) of numbers provided as arguments . In this example where there are only four quiz values to work with, you could use AVERAGE with separate cell references like this:...

Purpose Return value Syntax =COMBINA(number,number_chosen) number - The total number of items. number_chosen - The number of items in each combination. Using the COMBINA function The COMBINA function returns the number of combinations for a given number of items. A combination is a group of items where order does not matter. There are two kinds of combinations: Combinations that do not allow repetitions (e.g. 123) Combinations that do allow repetitions (e....

Explanation In the Excel date system, one day is equal to 1, so you can think of time as fractional values of 1, as shown in the table below: Hours Fraction Minutes Seconds Value Time 1 1/24 60 3600 0.04167 1:00 3 3/24 180 10800 0.125 3:00 6 6/24 360 21600 0.25 6:00 12 12/24 720 43200 0.5 12:00 18 18/24 1080 64800 0.75 18:00 24 24/24 1440 86400 1.0 21:00 Since there are 24 hours in a day, 60 minutes in each hour, and 60 seconds in each minute, you need to divide by 24 * 60 * 60 = 86400 in order to convert decimal seconds to a value that Excel will recognize as time....

Explanation In this example, the goal is to count cells in a range that are blank. Counting blank cells in Excel can be tricky because cells can look blank even when they are not actually empty. The article below explains three different approaches. COUNTBLANK function The simplest way to count empty cells in a range is to use the COUNTBLANK function . In the example shown, the formula in F6 is:...

Explanation In this example, the goal is to count numbers in the range B5:B15 ( named data ) where the third digit is a specific number, indicated in column D. You might think the COUNTIF function would be a good way to solve this problem. However, for reasons explained below, COUNTIF won’t work. Instead, you can use the SUMPRODUCT and Boolean logic . See below for a full explanation. COUNTIF function The COUNTIF function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (,?...

Purpose Return value Syntax =DCOUNTA(database,[field],criteria) database - Database range including headers. field - [optional] Field name or index to count. criteria - Criteria range including headers. Using the DCOUNTA function The Excel DCOUNTA function counts matching records in a database using a specified field and criteria. Unlike DCOUNT, which counts only numeric values, DCOUNTA counts both numeric and text values. Empty cells are ignored. Use DCOUNT to count only numeric values....

About This Shortcut This shortcut will delete rows that are selected. Note: In Mac 2016, Control - also works (same as Windows). About This Shortcut This shortcut will delete columns that are selected. Note: In Mac 2016, Control - also works (same as Windows).

About This Shortcut This shortcut will force calculation in all worksheets in all open workbooks even when cells have not been changed. We aren’t aware of a keyboard shortcut to do this on the Mac. If you know of one, please let us know. About This Shortcut Use this keyboard shortcut to evaluate parts of a formula. This is not really a keyboard shortcut per se, but rather just a trick you can use to find out how Excel evaluates parts of a formula as it calculated....

Explanation The FV function is a financial function that returns the future value of an investment, given periodic, constant payments with a constant interest rate. The PV function returns the present value of an investment. You can use the PV function to get the value in today’s dollars of a series of future payments, assuming periodic, constant payments and a constant interest rate. This simple example shows how present value and future value are related....

Explanation First, this formula determines the first day of the next month after a given date. It does this my using EOMONTH to get the last day of the month, then adding one day: =EOMONTH(B5,0)+1 Next, the formula calculates the number of days required to “roll back” to the last requested weekday in the month prior (i.e. the month of the original date): WEEKDAY(EOMONTH(B5,0)+1-C5) Inside WEEKDAY, EOMONTH is again used to get the first day of the next month....

Explanation In this example, the goal is to retrieve historical stock price information for a given stock, provided as a ticker symbol like “MSFT”, “AAPL”, “MMM”, etc. over the past n days, where n is a variable that can be changed as desired. In addition, the data should be sorted in reverse chronological order, with the latest information appearing first. This can be done with the STOCKHISTORY function , whose main purpose is to retrieve historical information for a financial instrument over time....

Explanation When you use a formula to apply conditional formatting, the formula is evaluated for every cell in the selection. In this case, there are no addresses in the formula, so, for every cell in the data, the ROW and ISEVEN functions are run. ROW returns the row number of the cell, and ISEVEN returns TRUE if the row number is even and FALSE if the row number is odd. The rule will trigger on TRUE, so even rows will be shaded....

Explanation The MOD function returns the remainder after division. With a divisor of 1, MOD will return zero for any whole number. We use this fact to construct a simple formula that tests the result of MOD. When the result is zero (i.e. when the number is an integer) the formula returns TRUE, triggering the conditional formatting. When the result is not zero (i.e. the number has a decimal component, and dividing by 1 leaves remainder) the formula returns FALSE....

Explanation This formula is evaluated for each of the 10 cells in A1:D10. A1 will change to the address of the cell being evaluated, while C1:C10 is entered as an absolute address, so it won’t change at all. The key to this formula is the =0 at the end, which “flips” the logic of the formula. For each value in A1:A10, COUNTIF returns the number of times the value appears in C1:C10....

Transcript In this video, we’ll look at how to add additional pivot charts based on the same data. Once you have a pivot chart, you might want to create additional pivot charts to provide different views of the same data. This worksheet has a simple pivot table and pivot chart already set up. As always, any change to the pivot table is reflected in the pivot chart, and vice versa....

Transcript Once you’ve created a pivot chart, it’s simple to change the chart type. Let’s take a look. Whenever you have a pivot chart selected, you’ll see a new ribbon called PivotChart Tools, that contains 4 tabs: Design, Layout, Format, and Analyze. These tabs give you a full set of controls to customize your pivot chart. Basic chart options are located on the Design Tab. You can change the chart type, alter the way the data is presented in the chart, control the layout used by the chart, apply a chart style, and move a chart to a new location....

Explanation The #VALUE! error appears when a value is not the expected type. This can occur when cells are left blank, when a function expecting a number receives text value, or when dates are evaluated as text by Excel. Fixing a #VALUE! error is usually just a matter of entering the right kind of value. The #VALUE error is a bit tricky because some functions automatically ignore invalid data. For example, the SUM function just ignores text values, but regular addition or subtraction with the plus (+) or minus (-) operator will return a #VALUE!...

Transcript How to highlight above and below average values with conditional formatting In this lesson, we’ll look at another conditional formatting option in the top and bottom category - the ability to highlight values that are above or below average. Let’s take a look. Here we have a table that contains three test scores for a group of students. Let’s use Conditional Formatting to quickly highlight values that are above or below average....

Transcript In this video we’re going to combine INDEX and MATCH together to look things up. Here we have the city population data we looked at before. We used the INDEX function to retrieve information about a city with a hard-coded position value. When we supply a number, INDEX retrieves information for the city at that position in the list. Now instead of fetching information by position, let’s convert this worksheet to retrieve information by city name....

Transcript Today we’re going to look at a tip for moving through a large list. So if you have a large list in Excel…hundreds, or thousands, or maybe tens of thousands of rows, it’s just not practical to scroll through that list to get to the bottom, or scroll through that list to get to the top. It’s just going to take way too long. So wouldn’t it be nice if there was a way to just jump to the bottom or jump to the top of that list?...