Note: data ends on row 18, so the calculation is as follows: $1,006.75 / 739 = $1.36 Fields The source data contains three fields, Product, Quantity, and Sales. A fourth field called “Unit Price” is a calculated field. The calculated field was created by selecting “Insert Calculated Field” in the “Fields, Items, and Sets” menu on the ribbon: The calculated field is named “Unit Price” and defined with the formula “=Sales/Quantity” as seen below:...
Fields The source data in the example contains three fields: Area, Item, and Cost. The pivot table as shown uses only two of these fields: Area and Cost. Area is a Row field, and Cost has been added twice as a Value field, once to show total cost, once to show a percentage breakdown. To show total estimated cost, the Cost field is set to sum values and has been renamed “Estimate”:...
The name “list” is a named range B5:B14. Named ranges are absolute references by default, so be sure to use an absolute reference if not using a named range. The second part of the formula is an expression that works out the correct row number as the formula is copied down: The result of this expression is a single number starting at 10, and ending at 1 as the formula is copied down....
When a dynamic array formula outputs multiple values, it is said to “spill” these values onto the worksheet. For example, if I use the UNIQUE function on this list of colors, UNIQUE spills 3 values - red, blue, and green. The rectangle that encloses these values is called the “spill range”. Notice when I select any cell in a spill range, the rectangle will appear with special highlighting. The spill range for a given formula is dynamic, and may expand or contract as source data changes....
where “data” is an Excel Table in the range B5:D16. In this configuration, VLOOKUP matches the text “RED” in row 5 of the table and returns 10 from the Qty column in the same row. This is an array formula and must be entered with control + shift + enter, except in Excel 365 or Excel 2021. Note: With XLOOKUP, or INDEX and MATCH, the approach is the same but the formulas are more straightforward: XLOOKUP example, INDEX and MATCH example....
Bar charts are also versatile. They can be used to plot both nominal data and ordinal data, and they can be used instead of a pie chart to plot data with a part-to-whole relationship. If you’re trying to decide on a chart type, a bar chart is a good first choice. Pros Very easy to read Versatile Easy to add data labels at ends of bars Room for longer text labels...
Pros Can show the relationship of one variable to another Unique ability to show data about a third dimension Can visually display correlation Cons Unusual chart type may be difficult to ready for many people More difficult to set up than other chart types Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....
The goal in this example is to calculate the years required to save 100,000 by making annual payments of $5,000 where the interest rate is 5% and the starting amount is zero. Payments are made annually, at the end of each year. To solve for periods, the NPER function is configured like this: where: rate - from cell C6, 5%. pmt - from C7, $5,000 (entered as negative value) pv - from cell C4, 0....
Excel ships with a large number of “presets” that make it easy to create new rules without formulas. However, you can also create rules with your own custom formulas. By using your own formula, you take over the condition that triggers a rule and can apply exactly the logic you need. Formulas give you maximum power and flexibility. For example, using the “Equal to” preset, it’s easy to highlight cells equal to “apple”....
which returns a value of 30. Because each hour can be represented as 1/24, you can convert an Excel time into decimal hours by multiplying the value by 24, and convert to decimal minutes by multiplying the value by 1440 (24 * 60) . With the time value 6:00 cell A1, you can visualize the conversion like this: The Excel time 6:00 converts to 360 minutes. Format result as number When you multiply a time value by 1440, Excel may automatically format the result using a time format like h:mm, which will display the value incorrectly....
where cola is the named range F6, and contains the adjustment as a percentage. The formula in cell C6, copied down is: With 30,000 in C5 as shown, the formula is solved like this: As the formula is copied down the table, the relative reference C5 changes at each row, while the named range cola (F6) behaves like an absolute reference and does not change. The result on each new row is the previous year’s base plus the adjustment....
where data is the named range C5:C16. As the formula is copied down, it returns a count for each of the number ranges shown in columns E and F. COUNTIFS function In the example shown, the formula used to solve this problem is based on the COUNTIFS function, which is designed to count cells that meet multiple criteria. The formula in cell G5, copied down, is: COUNTIFS accepts criteria in range/criteria pairs....
It’s important to understand that DOLLAR returns text and not a number, so the result cannot be used in a numeric calculation. If the goal is simply to format a number as Currency, applying a standard number format is a better option. Video: How to use number formatting. The DOLLAR function takes two arguments, number and decimals. Number is the number to format, decimals controls how the number is rounded and specifies the number of digits to the right of the decimal point....
FACTDOUBLE takes just one argument, number, which should be a positive integer. If number is not an integer, the decimal portion of number will be removed before the factorial is calculated. Examples For even numbers, the double factorial is the product of all even integers less than or equal to number and greater than or equal to 2. For example, the double factorial of 8 is 384: For odd numbers, the double factorial is the product of all odd integers less than or equal to number and greater than or equal to 1....
FORMULATEXT takes just one argument, reference, which is normally a cell reference like A1. If you use FORMULATEXT on a cell that doesn’t contain a formula, it returns #N/A. FORMULATEXT will handle formulas up to 8192 characters. In Excel 365, the FORMULATEXT function will return more than one result when given a range that contains formulas. These results will spill like other dynamic array formulas. In earlier versions of Excel, FORMULATEXT will return a single result from the upper left cell in the range....
Because the harmonic mean tends toward the smallest values in a set of data, it limits the impact of large outliers, but exaggerates the impact of small outliers. The harmonic mean is always less than the geometric mean (GEOMEAN), which is always less than the arithmetic mean (AVERAGE). The HARMEAN function takes multiple arguments in the form number1, number2, number3, etc. up to 255 total. Arguments can be a hardcoded constant, a cell reference, or a range....
By default, Excel uses the list separator defined under regional settings in Control Panel. The US English version of Excel uses a comma (,) for list separator by default, while other international versions may use a semicolon (;). This impacts how functions are entered in Excel. In the United States, and countries like Canada, Australia, United Kingdom, etc. functions are entered with arguments separated by commas. In other countries like Spain, France, Italy, Netherlands, Germany, functions are entered with semicolons....
Notes: The payment returned by PMT includes principal and interest but will not include any taxes, reserve payments, or fees. Be sure you are consistent with the units you supply for rate and nper. If you make monthly payments on a three-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 3*12 for nper. For annual payments on the same loan, use 12 percent for rate and 3 for nper....
The SEQUENCE function takes four arguments: rows, columns, start, and step. All values default to 1. The rows and columns arguments control the number of rows and columns that should be generated in the output. For example, the formulas below generate numbers between 1 and 5 in rows and columns: Note the output from SEQUENCE is an array of values that will spill into adjacent cells. The syntax for SEQUENCE indicates that rows is required, but either rows or columns can be omitted: The start argument is the starting point in the numeric sequence, and step controls the increment between each value....
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
