You're welcome. Thanks for the feedback!

Biff

"Thomas [PBD]" wrote in message
...
Thank you, that was exactly what I was looking for.

The values would however never be zero because it is being based on
information that has to have values in it.

"T. Valko" wrote:

Ok, try this:

Even if you start by adding 3 columns of last years data there will still
be
less than 3 values to average until those last 3 weeks from the previous
year are filled.

I'll assume it will look like this:

W50...W51...W52...W1...W2...W3...etc

With those headers starting in B1 and data in B2:

=IF(SUMPRODUCT(--(ISNUMBER(B2:K2)),--(B2:K2<0))<3,"",AVERAGE(OFFSET(B2,,SUMPRODUCT(--(ISNUMBER(B2:K2)),--(B2:K2<0))-1,,-3)))

Adjust for the end of the range. I tested up to column K.

This will leave the cell blank until W52 (previous year) is filled.

Now, this leads me to ask, if the values can be either positive or
negative
can they also be 0? I did note that you said:

(note: there will never be a zero field for the week value.)

I'm just trying to account for all the possibilities!

Biff

"Thomas [PBD]" wrote in message
...
Yes. Every once in a while there might be some negative numbers, it
shouldnt
be very often.

PS. Sorry about the multiple postings, I kept getting the "Cannot
display
page" error, so I figured that it wasnt going to post the messages a
half
dozen times.

"T. Valko" wrote:

Will there be any negative numbers?

Biff

"Thomas [PBD]" wrote in message
...
Very good question, I didnt think too much about what would happen
in
the
future with it. I guess I am going to have to keep 3 weeks from the
last
years data. That should solve the problem. As of right now, we are
in
week
18, so I didnt have to worry about it. I will create three new
columns
for
last year then.

"T. Valko" wrote:

What do you want to happen when there is not 3 weeks of data to
average?

Biff

"Thomas [PBD]" wrote in
message
...
I have been designated to create a spreadsheet which tracks a lot
of
information, and does a trend analysis for only three weeks worth
of
information. So far, I have set up the sheet horizontally.

So... Row 1 = Week 1, Week 2, Week 3, Week 4, etc.
Row 2 = values for each week. (ie 1, 2, 3, 4, etc) which are
being
based
on
a different sheet inside the same file, so they are linked to
each
other.
Therefore, if the other sheet contains no data for the new week,
a 0
is
made
(note: there will never be a zero field for the week value.)

With that said. I now have to find a way to only do a three week
analysis,
where it averages the three weeks before the zeros.

Say we are right now in week 5. This means that weeks 1-4 have
values,
and
weeks 5 - 52 have zeros. I would need to average weeks 2, 3 & 4
together.
And thus, this would continually update itself after new
information
has
been
entered into the other data table. When we are in week 6; 3, 4,
& 5
would
average together.

Any help would be greatly appreciated.