Posts

Purpose Return value Syntax =COVARIANCE.S(array1,array2) array1 - The first set of data values. array2 - The second set of data values. Using the COVARIANCE.S function The COVARIANCE.S function calculates the sample covariance between two data sets. It measures the degree to which two variables vary together, providing insight into their linear relationship. The return value from the function is a single number that can be positive, negative, or zero , depending on the relationship between the variables....

Purpose Return value Syntax =DEC2OCT(number,[places]) number - The decimal number you want to convert to octal. places - [optional] Pads the resulting octal number with zeros up to the specified number of digits. If omitted returns the least number of characters required to represent the number. Using the DEC2OCT function The input must be a valid decimal number within the range [ -2^29, 2^29 - 1 ]. Negative Values Excel internally represents octal numbers in binary using 30 bits....

Purpose Return value Syntax =DSUM(database,field,criteria) database - Database range including headers. field - Field name or index to count. criteria - Criteria range including headers. Using the DSUM function The Excel DSUM function calculates a sum of values in a set of records that match criteria. The values to sum are extracted from a given field in the database, specified as an argument. The database argument is a range of cells that includes field headers....

Purpose Return value Syntax =DVAR(database,field,criteria) database - Database range including headers. field - Field name or index to count. criteria - Criteria range including headers. Using the DVAR function The Excel DVAR function gets the variance of a sample extracted from records matching criteria, using values in a given field. The database argument is a range of cells that includes field headers, field is the name or index of the field to get a max value from, and criteria is a range of cells with headers that match those in database....

Explanation In this example, the goal is to create a formula that performs a dynamic two-way count of all color and size combinations in the range B5:D16. The solution shown requires four general steps: Create an Excel Table called data List unique colors with UNIQUE function List unique sizes with UNIQUE function Generate counts in COUNTIFS function Create the Excel Table One of the key benefits of an Excel Table is its ability to resize when rows are added or removed....

About This Shortcut With multiple cells selected, this shortcut will enter the same data in all cells in the selection at once. This is a great way to skip a copy & paste step. Cells do not need to be contiguous; use Control (Win) or Command (Mac) to select non-contiguous cells before using Control + Enter. About This Shortcut This shortcut will insert the current date as a fixed value; it will not change....

Transcript In this video, we’ll look at shortcuts that let you work with the Ribbon. First, because the Ribbon takes up quite a lot of space, you may want to collapse it when you don’t need it. On Windows, use Control + F1. On a Mac, use Command Alt R. Each time you use the shortcut, you’ll toggle the ribbon. The main attraction of the ribbon, from a shortcuts point of view, is accelerator keys....

Work faster in Excel with over 200 keyboard shortcuts for both Windows and the Mac listed side-by-side on one handy PDF. This free PDF lists shortcuts by section with many useful links. Download the PDF below. The same shortcuts used by Excel pros Work faster and more efficiently Study offline at your leisure Compare Windows and Mac shortcuts Free instant access

Purpose Return value Syntax =EXPON.DIST(x,lambda,cumulative) x - The value at which to evaluate the distribution (must be ≥ 0). lambda - The rate parameter of the distribution (must be > 0). cumulative - A logical value that determines the form of the function. If TRUE, returns the cumulative distribution function; if FALSE, returns the probability density function. Using the EXPON.DIST function The EXPON.DIST function calculates values for the exponential distribution, which is a continuous probability distribution used to model the time between events in a Poisson process....

Excel’s fill handle is a small rectangular control that appears whenever a cell or range of cells are selected. When the cursor is moved over the fill handle, it turns into a cross symbol, which can be dragged to fill in more cells. The behavior of the fill handle varies depending on the content of the cell or range selected: When the selected range contains formulas, the formulas are copied to new locations and references are updated....

Purpose Return value Syntax =FORMULATEXT(reference) reference - Reference to cell or cell range. Using the FORMULATEXT function The FORMULATEXT function returns a formula as a text string from a cell reference. The FORMULATEXT can be used to extract a formula as text from a cell reference. The text returned by FORMULATEXT is the same as displayed in the formula bar when a cell with a formula is selected. Once text is extracted with FORMULA text, it can be handled as text in another formula....

Explanation In this example, the goal is to get and display the month name from any given date. There are several ways to go about this in Excel, depending on whether you want to extract the month name as text, or just display a valid Excel using the month name. To extract the month name from a date as text , you can use the TEXT function with a custom number format like “mmmm”, or “mmm”....

Explanation The goal in this example is to return the amount of time completed in a year as a percentage value, based on any given date. In other words, when given the date July 1, 2021, the formula should return 50% since we are halfway* through the year. *By default, the YEARFRAC function uses a 30/360-day convention, assuming a year contains twelve 30-day months = 360 days. This is controlled by an optional argument called “basis” ....

Explanation In this example, the goal is to work out the “percent of total” for each expense shown in the worksheet. In other words, given that we know the total is $1945, and we know Rent is $700, we want to determine that Rent is 36% of the total. The total already exists in the named range total (C15) which contains a formula based on the SUM function : =SUM(C6:C14) In mathematics, a percentage is a number expressed as a fraction of 100....

Transcript In this video, we’ll look at a way to create a search box that highlights rows in a table, by using conditional formatting, and a formula that checks several columns at once. This is a great alternative to filtering, because you can see the information you’re looking for highlighted in context. Let’s take a look. Here we have a table that contains of order data. We could add a filter, and use it to explore the data....

Transcript Excel has a simple function called LEN, for length, that calculates the number of characters in a text string which is a surprisingly useful function. Let’s take a look. The LEN function takes just one argument: the text you want to count. If I supply the address B5, which contains the text “Susan,” I’ll get a result of five since there are five letters in the name. The same formula will return the number eight for the text “New York....

Transcript Excel allows you to create custom formats for dates, times, text, and numbers. In this lesson we’ll create a simple custom format for positive and negative numbers based on an existing format. Let’s take a look. The easiest way to create a custom format in Excel is to first apply a format that is similar to the format you want to create. For example, suppose we want to create a custom format that shows positive numbers with a positive sign and negative numbers with a negative sign....

Transcript In this video we’ll look at how to create a named constant. This is a useful way to create a name that doesn’t appear in a cell on the worksheet. Let’s take a look. Here we have a list of materials to build a dog house which includes quantity, unit cost, and a total. Let’s add a formula that calculates sales tax. One way to do this is to name a range on the worksheet, and use that name in the formula....

Transcript In this video we’ll look at how to use VLOOKUP to group data into specific categories. Let’s take a look. Sometimes you need to group values into discreet categories that don’t exist in your data. For example, in this case, we have a list of employees and each employee is in one department. What if you need to group these employees into groups A and B, by department, as shown in this table?...

Transcript In this lesson, we’ll look at how to move and copy worksheets to other workbooks. Let’s take a look. If you’d like to move or copy a worksheet from the current workbook into a brand new workbook, just right-click the worksheet tab you’d like to move and choose “Move or Copy” from the menu. Then, in the Move or Copy dialog box, switch the dropdown selector to (new book). Until you’re really comfortable with moving worksheets, it’s a good idea to check the “Create a copy” checkbox....