Purpose
Return value
Syntax
=FORECAST.LINEAR(x,known_ys,known_xs)
- x - The x value data point to use to calculate a prediction.
- known_ys - The dependent array or range of data (y values).
- known_xs - The independent array or range of data (x values).
Using the FORECAST.LINEAR function
The FORECAST.LINEAR function predicts a value based on existing values along a linear trend. FORECAST.LINEAR calculates future value predictions using linear regression, and can be used to predict numeric values like sales, inventory, test scores, expenses, measurements, etc.
Note: Starting with Excel 2016, the FORECAST function was replaced with the FORECAST.LINEAR function. Microsoft recommends replacing FORECAST with FORECAST.LINEAR, since FORECAST will eventually be deprecated.
In statistics, linear regression is an approach for modeling the relationship between a dependent variable (y values) and an independent variable (x values). FORECAST.LINEAR uses this approach to calculate a y value for a given x value based on existing x and y values. In other words, for a given value x, FORECAST.LINEAR returns a predicted value based on the linear regression relationship between x values and y values.
Example
In the example shown above, the formula in cell D13 is:
=FORECAST.LINEAR(B13,sales,periods)
where sales (C5:C12) and periods (B5:B12) are named ranges . With these inputs, the FORECAST.LINEAR function returns 1505.36 in cell D13. As the formula is copied down the table, FORECAST.LINEAR returns predicted values in D13:D16, using values in column B for x.
The chart to the right shows this data plotted in a scatter plot .
Note: Although FORECAST calculates future value predictions, it can also be used to interpolate and even predict the past. Mark at Excel Off The Grid has a detailed explanation here .
Notes
- If x is not numeric, FORECAST.LINEAR returns a #VALUE! error.
- If known_ys and known_xs are not the same size, FORECAST.LINEAR will #N/A.
- If the variance of known_x values is zero, FORECAST.LINEAR will return #DIV/0!.
Purpose
Return value
Syntax
=FREQUENCY(data_array,bins_array)
- data_array - An array of values for which you want to get frequencies.
- bins_array - An array of intervals (“bins”) for grouping values.
Using the FREQUENCY function
The FREQUENCY function counts how often numeric values occur in a set of data and returns a frequency distribution – a list that shows the frequency (count) of each value in a range at given intervals (bins). FREQUENCY returns the distribution as a vertical array of numbers that represent a “count per bin”.
The FREQUENCY function always returns an array with one more item than bins in the bins_array . This is by design, to catch any values greater than the largest value in the bins_array . The general pattern for FREQUENCY is:
=FREQUENCY(data,bins)
where data_array and bins_array are typically ranges on the worksheet.
Instructions
To create a frequency distribution using FREQUENCY:
- Enter numbers that represent the bins you want to group values into
- Make a selection the same size as the range that contains bins, or one greater if want to include the extra item
- Enter the FREQUENCY function as a multi-cell array formula with control+shift+enter.
In Excel 365 , it is not necessary to enter FREQUENCY as an array formula. See notes below.
Examples
In the example shown, the formula in G5:G8 is:
{=FREQUENCY(C5:C14,F5:F8)}
Entered as a multi-cell array formula.
Note: the curly braces added by Excel automatically when entered with control + shift + enter .
Horizontal results
The FREQUENCY function always returns a vertical array of results. To return horizontal results, wrap the FREQUENCY function in the TRANSPOSE function :
=TRANSPOSE(FREQUENCY(data,bins))
Excel 365
In Excel 365 , which supports dynamic arrays natively , it is not necessary to select multiple cells before entering the FREQUENCY function. In cell G5, you can simply enter the formula below:
=FREQUENCY(C5:C14,L5:L8)
FREQUENCY will return an array of six counts, and these counts will spill automatically into the range G5:G9. The count in the last row (G9) is the overflow bin, the count of any values greater than the largest value in the bins_array .
Notes
- FREQUENCY returns multiple values and must be entered as an array formula, except in Excel 365 .
- FREQUENCY always returns an array with one more item than bins. This is by design, to catch any values greater than the largest interval in the bins_array.
- Each bin shows a count of values up to and including bin value, excluding values already accounted for.
