Results 1 to 9 of 9

20060615, 07:19 #1
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
Projection Formula Problem (Excel 2000/2003)
Hi
I would like some help with a projection formula please,
Please see attached
Many thanks
BraddyIf you are a fool at forty, you will always be a fool

20060615, 09:27 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Projection Formula Problem (Excel 2000/2003)
What are the numbers in L5, M5, etc.? Are they monthly amounts, or cumulative amounts?

20060615, 09:29 #3
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Projection Formula Problem (Excel 2000/2003)
Hi Hans
They are monthly amounts.
Thanks for the reply
BraddyIf you are a fool at forty, you will always be a fool

20060615, 09:33 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.)

20060615, 09:37 #5
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
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
BraddyIf you are a fool at forty, you will always be a fool

20060615, 09:47 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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?

20060615, 09:58 #7
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Projection Formula Problem (Excel 2000/2003)
Hi Hans
Sorry about this, the cell will be left blank,
Many Thanks
BraddyIf you are a fool at forty, you will always be a fool

20060615, 10:10 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Projection Formula Problem (Excel 2000/2003)
Try this formula:
<code>
=12*SUM(L5:W5)/MATCH(9.99999999999999E+307,L5:W5)
</code>
SUM(L5:W5) adds all available data.
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 filledin cell. In your sample workbook, it would return 3 because the last filledin 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 filledin 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.

20060615, 10:53 #9
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Projection Formula Problem (Excel 2000/2003)
Hi Hans
That works for me,
Once again I am in your debt.
Grateful Thanks
BraddyIf you are a fool at forty, you will always be a fool