Forecast function maybe?

[HammerJoe]

Hi,

I would kindly ask for help about a problem I've been working on.
I am using excel 2003.

I keep track of the # of tasks done on a daily basis.
something like this:

-----A----------------B
1---Nov/01---------5
2---Nov/02---------3

...And so on.

What I want is to forecast at the end of the month the total number of
tasks accomplished.
I know that the month has 21 working weekdays, but right now I only
have data for two days.
As days goes by more rows are entered.
Right now I am using a pretty simple formula
=sum(b1:b21)*21/count(b1:b21) wich gives me the result 84.
But it is not very accurate at all.
I tried to use Trend, Forecast, Linest but I cant get them to work.

Any help would be much appreciated.
Thanks

 

[Bernard Liengme]

Unless you are working with a very mechanical system (one apple falls from a
tree each day, how many apples at the end of the week?), NOTHING can predict
how many tasks you will do in 21 days given only 2 days' data. Well, that's
not really true: Madam X's crystal ball might work!
best wishes

 

[Gary''s Student]

You are using the correct formula. What you are seeing is very common:

instability early in the month.

The traditional way of removing the instability is to use a running average.
For example, today we have data for Nov 1,2,3. The average should include
the last 18 days in October and the first 3 days in November. On Nov 11th
use the last 13 days in October and the first 8 days in November.

By the end of Nov. use only Nov. data.

This will improve the stability of the sequence of estimates.

 

[HammerJoe]

Thanks for the reply.
It does make sense.

Now I havre to figure a way to put it into action in Excel.
This kinda throws a wrench on my spreedsheet.

I guess that instead of just keeping the current month of data I need
to start keeping two months worth of information.
Is the index function the correct one to get the 21 days of data within
the two month period?

Can I ask for help on something else then?
Would it be possible without using VBA (I know nothing about it) to
basically move the table one row up by when entering on row 25?
The idea would be for Excell to automatically get rid of of the first
row which is not longer needed for the calculation by moving the others
up one row?

Might as well ask this as well, I would like for the spreedsheet to
automatically copy the total number of tasks for the month to another
sheet when a new month starts so I can do other things with that info?


I think I know how to do it, but the way I am thinking it is clumsy and
I am sure with Excell there is a simple way to do things./

Thanks for all the help.