Purpose Return value Syntax =FACT(number) number - The number to get the factorial of. Using the FACT function The Excel FACT function returns the factorial of a given number. In mathematics, the factorial of a non-negative integer n is the product of all positive integers less than or equal to n , represented with the syntax n! FACT takes just one argument , number , which should be a positive integer....
Purpose Return value Syntax =FACTDOUBLE(number) number - A number greater than or equal to -1. Using the FACTDOUBLE function The FACTDOUBLE function returns the double factorial of a number. A double factorial is calculated differently for even and odd numbers. For an even number, n , it’s the product of all even integers less than or equal to n and greater than or equal to 2. For an odd number, the double factorial is the product of all odd integers less than or equal to n and greater than or equal to 1....
Explanation In this example, the goal is to calculate the number of months between two valid Excel dates . This is a curiously tricky problem in Excel because the number of days in a month varies, and the rules about how a whole month might be calculated are not obvious. In addition, there is not a modern Excel function dedicated to the task of calculating months between dates. I have no idea why....
Purpose Return value Syntax =HARMEAN(number1,[number2],...) number1 - First value or reference. number2 - [optional] Second value or reference. Using the HARMEAN function The Excel HARMEAN function returns the harmonic mean for a set of numeric values. The harmonic mean is a kind of numeric average, calculated by dividing the number values in a list by the sum of the reciprocal of each value. In other words, the harmonic mean is the reciprocal of the average of the reciprocals....
Purpose Return value Syntax =HEX2DEC(number) number - The hexadecimal number you want to convert to decimal. Using the HEX2DEC function The input number must be less than or equal to ten alpha-numeric characters, otherwise the function returns the #NUM! error value. The internal (binary) representation of the hexadecimal number uses two’s complement notation. The first bit indicates whether the number is positive or negative, and the other 39 bits indicate the magnitude of the number....
About This Shortcut This shortcut is the same as filtering a pivot table field to exclude an item in the field. Check the filter afterwards to confirm how the shortcut works. About This Shortcut This shortcut will embed a pivot chart on the same worksheet as the pivot table. Select a cell in the pivot table first. No Mac equivalent.
Explanation When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each of the 10 cells in B2:B11, and B2 will change to the address of the cell being evaluated each time, since B2 is relative. The formula itself uses the SEARCH function to find the position of “dog” in the text....
Details Excel has a built-in conditional formatting rule that will highlight dates that occur “this month”. But what if you want to highlight dates in the same month and year for any date? In other words: Given a date, what formula will highlight other dates in the same month and year? Like so many things in Excel, there are many ways to solve this problem. Need some ideas or inspiration? You can find Excel’s date functions here ....
Transcript In this video, we’ll look at how to make a 100% stacked area chart. Stacked area charts make sense when you want to show changes in a part-to-whole relationship over time. 100% stacked charts are focused on proportions, at the expense of actual values. To better explain this, I’ll create a basic stacked area chart, then compare with a 100% stacked version. First, I’ll select data, excluding totals. Then I’ll use recommended charts to insert a stacked area chart....
Transcript In this video, we’ll look at how to make an area chart. Like line charts, area charts are a good way to show trends over time. However, when plotting multiple data series, you must pay attention to the order in which the data series are plotted. Here we have some US census population data for several states. Let’s plot this data in an area chart. To start off, I’ll just select data for Arizona, and create a new area chart....
Transcript In this video, we’ll look at how to move a custom table style into another workbook. You won’t find an import style command in Excel, or any other direct method for moving a custom style from one workbook to another. However, there are a couple easy workarounds. One option is to copy an entire worksheet into another workbook. In this workbook, I have a custom style already defined, as you can see in the styles menu....
Transcript In this lesson, we’ll look at how to move data from one place to another in Excel. The most common way to move information is to use cut and paste. We’ll cover three options: using buttons on the ribbon, using keyboard shortcuts, and using the right-click menu. Let’s take a look. The most basic way to move content in Excel is to select one or more cells, then click the “cut” button on the ribbon, move to another location, and click the “paste” button on the ribbon....
Purpose Return value Syntax =IMAGE(source,[alt_text],[sizing],[height],[width]) source - Path to image as a URL. alt_text - [optional] Text to describe the image for accessibility. sizing - [optional] 0 = Fit to cell, 1 = Fill cell, 2 = Original size, 3 = Custom size. height - [optional] Height of the image in pixels. width - [optional] Width of the image in pixels. Using the IMAGE function The IMAGE function is Excel’s solution for adding images to a worksheet with a formula....
Purpose Return value Syntax =IPMT(rate,per,nper,pv,[fv],[type]) rate - The interest rate per period. per - The given payment period. nper - The total number of payment periods. pv - The present value, or total value of all payments now. fv - [optional] The cash balance desired after last payment is made. Defaults to 0. type - [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0....
Purpose Return value Syntax =ISPMT(rate,per,nper,pv) rate - Interest rate. per - Period (starts with zero, not 1). nper - Number of periods. pv - Present value. Using the ISPMT function The ISPMT function calculates the amount of interest in a given period of an investment where principal payments are equal. The given period is specified as a zero-based number instead of a 1-based number. For example, to calculate the interest amount in payments for a loan where the amount is $10,000, the interest rate is 10%, and there are 5 periods in which the principal payment is constant (even), you can use:...
Explanation In this example, the goal is to look up and retrieve an entire column of values in a set of data. For example, when a value like “Q3” is entered into cell H4, all values in the range E5:E16 should be returned. For convenience and readability, quarter (C4:F4) and data (C5:F16) are named ranges . Although this example shows off the simplicity of the XLOOKUP function, it can also be solved with a straightforward INDEX and MATCH formula, as described below....
Purpose Return value Syntax =MONTH(date) date - A valid Excel date. Using the MONTH function The MONTH function extracts the month from a given date as a number between 1 and 12. For example, given the date “June 12, 2021”, the MONTH function returns 6 for June. MONTH takes just one argument, serial_number , which must be a valid Excel date . The MONTH function returns a number . If you need a month’s name , use the TEXT function as shown below ....
Explanation This formula relies on the standard behavior of the COUNTIF function. The range is C5:C8, the criteria is provided as not equals OK: =COUNTIF(C5:C8,"<>ok") The COUNTIF then returns a count of any cells that do not contain “OK” which is compared to zero. If the count is zero, the formula returns TRUE. If the count is anything but zero, the formula returns FALSE. Ignore empty cells To ignore empty cells, you can use a more generic version of the formula:...
Explanation This formula sits inside a small summary table with percentile values in column F and gender values in G4 and H4. Working from the inside out, the IF function is set up like this: IF(Table[Gender]=G$4,Table[Score]) Here, each value in the gender column is tested against the value in G4, “Male”. The result is an array of boolean values like this: {88;85;77;FALSE;FALSE;FALSE;83;FALSE;FALSE;79} Only scores associated with males make it into the array, female scores are translated to FALSE....
Purpose Return value Syntax =PERCENTRANK.INC(array,x,[significance]) array - Array of data values. x - Value to rank. significance - [optional] Number of significant digits in result. Defaults to 3. Using the PERCENTRANK.INC function The Excel PERCENTRANK.INC returns the relative standing of a value within a data set as a percentage. For example, a test score greater than or equal to 80% of all test scores is said to be at the 80th percentile....
