How to Calculate P Value in Excel

By Nina Nixon

Calculating the "P" (Project) Value in Excel helps you to foretell shopper trends, inventory supply needs or sales revenues. One technique used to calculate this value is the "Forecast" formula. It is based on the "X_Known" and "Y_Known" value ranges and the average gap between each set of known factors to obtain a realistic probable figure. A little experimentation will show you how Excel can help forecast what you need based on the Xs and Ys that you already know.

Step 1

Create a table and then click on cell E4. Next, click on the "Insert Function" key. Scroll to the "Forecast" function and click it.

Step 2

Enter D4 for the "X" value. This is the input area, where numerical values will be added once they become available for you to enter.

Step 3

Enter "Known_Y's" cell range as "C3:C7." These figures represent the actual Revenues from Shoes Sold in 2008 in this example.

Step 4

Enter "Known_X's" cell range as "B3:B7." These figures represent the actual Revenues from Shoes Sold in 2007 in this example. Next, click "OK" to exit.

Step 5

Copy cell E4 and paste into cells E5, E6 and E7.

Step 6

Select cell E5 and change the formula to read "=FORECAST(D5,C3:C7;B3:B7)". The range must match the same as E4, with the exception of the input cell, which in this case is D5.

Step 7

Select cell E6 and change the formula to read "=FORECAST(D6,C3:C7;B3:B7)". The range must match the same as E4, with the exception of the input cell, which in this case is D6.

Step 8

Select cell E7 and change the formula to read "=FORECAST(D7,C3:C7;B3:B7)". The range must match the same as E4, with the exception of the input cell, which in this case is D7.

Step 9

Delete the contents of cells D4, D5, D6, and D7. Notice that without any figures, the average for the upcoming year goes down dramatically.

Step 10

Enter the actual "Revenues from Shoes Sold for the 1st Qtr 2009" as "17,000." The new forecast for 1st Qtr 2010 is automatically calculated based on average known values. Repeat this process for the other quarters when new known figures become available.

Tips & Warnings

  • Highlight the "Input Areas" in the tables you create to differentiate them from other cells containing formulas. This will help deter overwriting accidents that could increase the likelihood of formula errors due to rushed re-calculations.
  • Create a backup of your spreadsheet because recreating formulas may not be very rewarding.