AVERAGEIF specific rows

[Guest]

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.

 

[T. Valko]

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

Biff

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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]

Will there be any negative numbers?

Biff

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.

 

[Guest]

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]

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

 

[Guest]

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]

You're welcome. Thanks for the feedback!

Biff

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.