How to Use Auto Fill in Excel to Show Predicted Prices

Save

The FORECAST tool allows users to predict future prices using a linear trend calculated by the least squares method. In other words, Excel will provide an estimation of future prices based on the tendency of past prices. After using the FORECAST tool for one time period, the Auto Fill feature may then be used, which makes the process of price prediction for subsequent time periods easier and faster.

  • Create two columns or rows, one of which should contain prices and the other should contain a time frame (for example, years). In this example, use cells A2 to E2 for known past prices, and A1 to H1 for the corresponding years. Cells F1 to H1 are the years you wish to predict prices for. Cells F2 to H2 are empty.

  • Insert known data. Use a numerical value for time and fill these values into cells A1 to H1. In this example, 1 to 8 are filled into cells A1 to H1 respectively to represent years. For known prices, the values 10, 11, 12, 13, and 14 are filled into cells A2, B2, C2, D2 and E2 respectively.

  • Type in the FORECAST function by first left-clicking the cell that represents the first unknown price. In this example it would be empty cell F2, which corresponds to year 6. Type in "=FORECAST(x, Known_y's, Known_x's)" without the quote marks.

  • Add a value for 'x' in the FORECAST formula. Substitute 'x' for the cell reference representing the first year of unknown prices. In this example it is year 6, or cell F1.

  • Add a value for "Known_x's" in the FORECAST formula. Substitute "Known_x's" for the range of cells that represent the time period for which price is already known. In this example these are years 1 to 5, and therefore "A1:E1" without the quote marks.

  • Add a value for "Known_y's" in the FORECAST formula. Substitute "Known_y's" for the range of cells that represent the set of prices that are already known. In this example these are cells A2 to E2, and therefore "A2:E2" without the quote marks.

  • Calculate the first predicted price. The formula in cell F2 should first look something like "=FORECAST (F1, A2:E2, A1:E1)" without quotes. Press enter. In this example the predicted price for year 6 is calculated, which is equal to 15.

  • Auto Fill the remaining unknown prices by guiding the mouse cursor over the bottom right corner of the first predicted cell (in this example, F2). When the cursor changes to a cross-hair, left-click and drag over the remaining empty cells (in this case, cells G2 and H2), and release. This calculates the predicted prices for the remaining years, which in this example is equal to 16 and 17.

Related Searches

References

  • Photo Credit Housing Market Boom image by Paul Heasman from Fotolia.com
Promoted By Zergnet

Comments

You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!