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 […]
