I need a formula for deriving a running average over a period of 52
weeks that will not figure in the zeros for the weeks that have not
arrived. It needs to be a formula that figures only the weeks that have
come to past, as well as, as they come to past. Each week it will change
due to new figures coming for the completed week.
Thank you.


--
lsmft
------------------------------------------------------------------------
lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=526915

Use nothing instead of zeros in future weeks, that way they won't be
included
or if they are derived from formulas use =IF(future,"",formula)


--

Regards,

Peo Sjoblom

"lsmft" wrote in message
...

I need a formula for deriving a running average over a period of 52
weeks that will not figure in the zeros for the weeks that have not
arrived. It needs to be a formula that figures only the weeks that have
come to past, as well as, as they come to past. Each week it will change
due to new figures coming for the completed week.
Thank you.


--
lsmft
------------------------------------------------------------------------
lsmft's Profile:
http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=526915

If you want to use columns and rows, you can do it this way:

Example --

The weeks are numbered. The entries are made next to their respective
weeks.
The Balance is calculated by a simple formula like =C3+D2
or, if you don't want the balance to calculate when there is a blank in
column C, use =IF($C3="","",INDIRECT("d"&ROW()-1)+$C3)
The calculate the average by using =IF($C3=0,"",$D3/$B3)
Therefore, you would get a table that looked like this below given the
entries in column C

Col B Col C Col D Col E
Weeks Entry Balance Average

1 10 10 10
2 20 30 15
3 30 60 20
4 40 100 25
5 50 150 30
6 60 210 35
7 70 280 40
8 80 360 45
9 90 450 50
10 0 450
11 0 450
12 0 450
13
14


Jack


"lsmft" wrote in message
...

I need a formula for deriving a running average over a period of 52
weeks that will not figure in the zeros for the weeks that have not
arrived. It needs to be a formula that figures only the weeks that have
come to past, as well as, as they come to past. Each week it will change
due to new figures coming for the completed week.
Thank you.


--
lsmft
------------------------------------------------------------------------
lsmft's Profile:
http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=526915

Thanks very much,
The situation that I have is:
I am given the Year To Date amount each week.
So on my worksheet, I have in Col. "A" top to bottom, numbers 1-52
representing the week of the year.
Col. "B" starting with Cell "B-1" is the YTD Weekly Income as of the
first week of the year, Cell "B-2" being YTD as of week two. Cell "C-1"
will be the first individual week's income. Cell "C-2" is the second
individual weeks income figured by subtracting Cell "C-1" from Cell
"B-2". (Cell C2=B2-C1) click and drag down the column. Hence the rest
of the unarrived weeks = zeros. In another area of the worksheet, I
have a Summation area with totals and averages etc. In this area is
where I want a cell listed as "Avg. Wkly. Inc." It will average all
Individual Wkly Incomes from Cell "C-1" to Cell "C-52", zeros and all,
but I don't want the zeros figured in as part of the running average,
and this cell's figure will naturally fluctuate as the year progresses.

Thanks again for your help


--
lsmft
------------------------------------------------------------------------
lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=526915

=AVERAGE(IF(C1:C52<0,C1:C52))

HTH
Kostis Vezerides

Thank you so much


--
lsmft
------------------------------------------------------------------------
lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=526915