AVERAGEIF specific rows
 

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.

AVERAGEIF specific rows
 

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.

AVERAGEIF specific rows
 

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.

AVERAGEIF specific rows
 

Oh, I didnt think about that, I guess I am going to have to keep 3 records
from last year in order to populate it. I wasnt thinking about that when I
was creating it, but I will create three columns for last years information.
As of right now, we are in week 18, so I have enough information for it.

"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.

AVERAGEIF specific rows
 

Oh, I didnt think about that, I guess I am going to have to keep 3 records
from last year in order to populate it. I wasnt thinking about that when I
was creating it, but I will create three columns for last years information.
As of right now, we are in week 18, so I have enough information for it.

"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.

AVERAGEIF specific rows
 

Oh, I didnt think about that, I guess I am going to have to keep 3 records
from last year in order to populate it. I wasnt thinking about that when I
was creating it, but I will create three columns for last years information.
As of right now, we are in week 18, so I have enough information for it.

"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.

AVERAGEIF specific rows
 

Oh, I didnt think about that, I guess I am going to have to keep 3 records
from last year in order to populate it. I wasnt thinking about that when I
was creating it, but I will create three columns for last years information.
As of right now, we are in week 18, so I have enough information for it.

"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.

AVERAGEIF specific rows
 

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.

AVERAGEIF specific rows
 

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.

AVERAGEIF specific rows
 

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.

AVERAGEIF specific rows
 

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.

AVERAGEIF specific rows
 

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.