# Thread: Projection Formula Problem (Excel 2000/2003)

1. ## Projection Formula Problem (Excel 2000/2003)

Hi

I would like some help with a projection formula please,

Many thanks

2. ## Re: Projection Formula Problem (Excel 2000/2003)

What are the numbers in L5, M5, etc.? Are they monthly amounts, or cumulative amounts?

3. ## Re: Projection Formula Problem (Excel 2000/2003)

Hi Hans

They are monthly amounts.

4. ## Re: Projection Formula Problem (Excel 2000/2003)

And how would you like the projection?

Should we assume that monthly fiuctuations are random, or that there is a systematic trend (in the latter case, the prediction for April would be 250, for May 300 etc.)

5. ## Re: Projection Formula Problem (Excel 2000/2003)

Hi Hans

Then monthly fiuctuations are random some months may have no sales at all , but would need to be included in the calculation.

Many thanks

6. ## Re: Projection Formula Problem (Excel 2000/2003)

If there are no sales in a month, will the corresponding cell be left blank, or will a 0 be entered?

Hi Hans

Many Thanks

8. ## Re: Projection Formula Problem (Excel 2000/2003)

Try this formula:
<code>
=12*SUM(L5:W5)/MATCH(9.99999999999999E+307,L5:W5)
</code>
9.99999999999999E+307 is the highest number you can enter in a cell.
MATCH(9.99999999999999E+307,L5:W5) looks for this number in the range L5:W5 and of course cannot find it, so it returns the index of the last filled-in cell. In your sample workbook, it would return 3 because the last filled-in month is March. It doesn't matter if intermediary cells are left blank.
SUM(L5:W5)/MATCH(9.99999999999999E+307,L5:W5) is the average amount over the months up to the last filled-in one.
The year projection is 12 times this average.

Note: if there are no sales for the last months of the year, you should enter a 0. If for example December is left blank, it is impossible to tell whether this is because there were no sales, or because no data have become available yet.

9. ## Re: Projection Formula Problem (Excel 2000/2003)

Hi Hans

That works for me,

Once again I am in your debt.

Grateful Thanks