Excel stores and calculates floating point numbers. Sometimes, the result of a formula is a very close approximation. 1. For example, take a look at the formulas below. At first glance, everything looks alright. 2. However, if we show 16 decimal places, we can see that one result is a very close approximation. You don’t […]
Trace Precedents | Remove Arrows | Trace Dependents | Show Formulas | Error Checking | Evaluate Formula Formula auditing in Excel allows you to display the relationship between formulas and cells. The example below helps you master Formula Auditing quickly and easily. Trace Precedents You have to pay $96.00. To show arrows that indicate which […]
A formula in a cell that directly or indirectly refers to its own cell is called a circular reference. This is not possible. 1. For example, the formula in cell A3 below directly refers to its own cell. This is not possible. Note: Excel returns a 0 if you accept this circular reference. 2a. For […]
Excel functions such as SUM, COUNT, LARGE and MAX don’t work if a range includes errors. However, you can easily use the AGGREGATE function to fix this. 1. For example, Excel returns an error if you use the SUM function to sum a range with errors. 2. Use the AGGREGATE function to sum a range […]
This example illustrates the ISERROR function in Excel. 1. For example, Excel displays the #DIV/0! error when a formula tries to divide a number by 0. The ISERROR function checks whether a value is an error and returns TRUE or FALSE. 2. Use the IF and the ISERROR function. If a cell contains an error, […]
This example illustrates the IFERROR function in Excel. 1. For example, Excel displays the #DIV/0! error when a formula tries to divide a number by 0. 2. Use the IFERROR function. If a cell contains an error, an empty string (“”) is displayed. .
##### error | #NAME? error | #VALUE! error | #DIV/0! error | #REF! error This chapter teaches you how to deal with some common formula errors in Excel. ##### error When your cell contains this error code, the column isn’t wide enough to display the value. 1. Click on the right border of the column […]
Round | RoundUp | RoundDown | Even and Odd | Nearest Multiple This chapter illustrates three functions to round numbers in Excel. The ROUND, ROUNDUP and ROUNDDOWN function. Before your start: if you round a number, you lose precision. If you don’t want this, show fewer decimal places without changing the number itself. Round 1. Round […]
Use the MAXIFS and MINIFS function in Excel 2016 to find the maximum and minimum value based on one criteria or multiple criteria. 1. For example, the MAXIFS function below finds the highest female score. Note: the first argument (D2:D12 in this example) is always the range in which the maximum or minimum will be […]
When you add a trendline to an Excel chart, Excel can display the equation in a chart (see below). You can use this equation to calculate future sales. The FORECAST and TREND function give the exact same result. Explanation: Excel uses the method of least squares to find a line that best fits the points. […]
This example teaches you how to use the PERCENTILE and QUARTILE function in Excel. Below you can find a list of scores (green fill for illustration only). 1. Use the PERCENTILE function shown below to calculate the 30th percentile. Excel returns the value 12.7. This means that 30% (6 out of 20) of the scores […]
The RANK function returns the rank of a number in a list of numbers. 1. If the third argument is omitted (or 0), Excel ranks the largest number first, second largest number second, etc. Note: when we drag the RANK function down, the absolute reference ($A$1:$A$9) stays the same, while the relative reference (A1) changes […]
Rand | Randbetween Excel has two useful functions when it comes to generating random numbers. The RAND and RANDBETWEEN function. Rand The RAND function generates a random decimal number between 0 and 1. 1. Select cell A1. 2. Type RAND() and press Enter. The RAND function takes no arguments. 3. To generate a list of […]
A clever Excel trick to change negative numbers to zero but leave positive numbers unchanged. Below you can find two columns with numbers. To subtract the numbers in column B from the numbers in column A, execute the following steps. 1. Select cell C1. 2. Enter the formula =A1-B1 3. Click the lower right corner […]
Average | Averageif | Median | Mode | Standard Deviation | Min | Max | Large | Small This chapter gives an overview of some very useful statistical functions in Excel. Average To calculate the average of a range of cells, use the AVERAGE function. Averageif To average cells based on one criteria, use the […]
SLN | SYD | DB | DDB | VDB Excel offers five different depreciation functions. We consider an asset with an initial cost of $10,000, a salvage value (residual value) of $1000 and a useful life of 10 periods (years). Below you can find the results of all five functions. Each function will be explained […]
This example teaches you how to create a loan amortization schedule in Excel. 1. We use the PMT function to calculate the monthly payment on a loan with an annual interest rate of 5%, a 2-year duration and a present value (amount borrowed) of $20,000. We have named the input cells. 2. Use the PPMT […]
What’s compound interest and what’s the formula for compound interest in Excel? This example gives you the answers to these questions. 1. Assume you put $100 into a bank. How much will your investment be worth after one year at an annual interest rate of 8%? The answer is $108. 2. Now this interest ($8) […]
Investment | Annuity This example teaches you how to calculate the future value of an investment or the present value of an annuity. Tip: when working with financial functions in Excel, always ask yourself the question, am I making a payment (negative) or am I receiving money (positive)? Investment Assume that at the end of […]
This example teaches you how to compare loans with different durations in Excel. 1. First, we calculate the monthly payment on a loan with an annual interest rate of 6%, a 20-year duration and a present value (amount borrowed) of $150,000. Note: we make monthly payments, so we use 6%/12 = 0.5% for Rate and […]
