Average values in only last 6 columns

[DonFlak]

I have a sheet with data in columns.

The field starts in D2 and continues column by column with a new
column each week.

Currently data is populated through AL2 but will continue all the way
to CL2. In CN2 I need a formula that will calculate the average of
the last 6 entries.

Currently that would be AG2:AL2 but next week that would need to be
AH2:AM2 and so one.

The columns will populate as data loads and I need this dynamic
average to automatically update.

Your assistance is greatly appreciated

Don Flak

 

[Bob Phillips]

=AVERAGE(INDEX(D2:CL2,,MAX(IF(D2:CL2<>"",COLUMN(D2:CL2)-COLUMN(D2)-5)))INDEX(D2:CL2,,MAX(IF(D2:CL2<>"",COLUMN(D2:CL2)-COLUMN(D2)+1)))))

this is an array formula, so commit with Ctrl-SHift-Enter


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Teethless mama]

=AVERAGE(OFFSET(A2,,COUNT(A2:CL2),1,-7))

 

[DonFlak]

=AVERAGE(INDEX(D2:CL2,,MAX(IF(D2:CL2<>"",COLUMN(D2:CL2)-COLUMN(D2)-5)))IN­DEX(D2:CL2,,MAX(IF(D2:CL2<>"",COLUMN(D2:CL2)-COLUMN(D2)+1)))))

this is an array formula, so commit with Ctrl-SHift-Enter

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)












- Show quoted text -

Thanks so much Bob, that worked perfectly

Don

 

[DonFlak]

=AVERAGE(INDEX(D2:CL2,,MAX(IF(D2:CL2<>"",COLUMN(D2:CL2)-COLUMN(D2)-5)))IN­DEX(D2:CL2,,MAX(IF(D2:CL2<>"",COLUMN(D2:CL2)-COLUMN(D2)+1)))))

this is an array formula, so commit with Ctrl-SHift-Enter

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)












- Show quoted text -

Oops, I spoke too soon.

I am getting the average of the last 7 values instead of 6

Specifically the data looks like

Column / Cell
AF4 AG4 AH4 AI4 AJ4
AK4 AL4 CN4
WEEK 29 WEEK 30 WEEK 31 WEEK 32 WEEK 33 WEEK 34 WEEK 35 6
Week Avg.

3.80% 0.60% -1.00% -1.30% -1.40% -5.40% -1.70% -0.91%

Here's the exact formula I am using

=AVERAGE(INDEX(D4:CL4,,MAX(IF(D4:CL4<>"",COLUMN(D4:CL4)-
COLUMN(D4)-5)))INDEX(D4:CL4,,MAX(IF(D4:CL4<>"",COLUMN(D4:CL4)-
COLUMN(D4)+1)))))

What am I do wrong?

 

[Bob Phillips]

Did you array enter it? I get -0.91% using that formula.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

=AVERAGE(INDEX(D2:CL2,,MAX(IF(D2:CL2<>"",COLUMN(D2:CL2)-COLUMN(D2)-5)))IN­DEX(D2:CL2,,MAX(IF(D2:CL2<>"",COLUMN(D2:CL2)-COLUMN(D2)+1)))))

this is an array formula, so commit with Ctrl-SHift-Enter

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)












- Show quoted text -

Oops, I spoke too soon.

I am getting the average of the last 7 values instead of 6

Specifically the data looks like

Column / Cell
AF4 AG4 AH4 AI4 AJ4
AK4 AL4 CN4
WEEK 29 WEEK 30 WEEK 31 WEEK 32 WEEK 33 WEEK 34 WEEK 35 6
Week Avg.

3.80% 0.60% -1.00% -1.30% -1.40% -5.40% -1.70% -0.91%

Here's the exact formula I am using

=AVERAGE(INDEX(D4:CL4,,MAX(IF(D4:CL4<>"",COLUMN(D4:CL4)-
COLUMN(D4)-5)))INDEX(D4:CL4,,MAX(IF(D4:CL4<>"",COLUMN(D4:CL4)-
COLUMN(D4)+1)))))

What am I do wrong?

 

[DonFlak]

Did you array enter it? I get -0.91% using that formula.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)






Oops, I spoke too soon.

I am getting the average of the last 7 values instead of 6

Specifically the data looks like

Column / Cell
   AF4            AG4        AH4          AI4           AJ4
AK4          AL4                    CN4
WEEK 29 WEEK 30 WEEK 31 WEEK 32 WEEK 33 WEEK 34 WEEK 35          6
Week Avg.

3.80% 0.60% -1.00% -1.30% -1.40% -5.40% -1.70%              -0.91%

Here's the exact formula I am using

=AVERAGE(INDEX(D4:CL4,,MAX(IF(D4:CL4<>"",COLUMN(D4:CL4)-
COLUMN(D4)-5)))INDEX(D4:CL4,,MAX(IF(D4:CL4<>"",COLUMN(D4:CL4)-
COLUMN(D4)+1)))))

What am I do wrong?- Hide quoted text -

- Show quoted text -

Yes, I did use the array, the problem is the -0.91 is not the correct
answer for the 6 week average. the correct answer would be -1.70.
The -0.91 is the 7 week average. That's why I included the week 29
value because somehow it is being included in the calculation.

 

[Bob Phillips]

Okay, spotted my error

=AVERAGE(INDEX(D4:CL4,,MAX(IF(D4:CL4<>"",COLUMN(D4:CL4)-COLUMN(D4)+1))-5):
INDEX(D4:CL4,,MAX(IF(D4:CL4<>"",COLUMN(D4:CL4)-COLUMN(D4)+1))))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

Did you array enter it? I get -0.91% using that formula.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)






Oops, I spoke too soon.

I am getting the average of the last 7 values instead of 6

Specifically the data looks like

Column / Cell
AF4 AG4 AH4 AI4 AJ4
AK4 AL4 CN4
WEEK 29 WEEK 30 WEEK 31 WEEK 32 WEEK 33 WEEK 34 WEEK 35 6
Week Avg.

3.80% 0.60% -1.00% -1.30% -1.40% -5.40% -1.70% -0.91%

Here's the exact formula I am using

=AVERAGE(INDEX(D4:CL4,,MAX(IF(D4:CL4<>"",COLUMN(D4:CL4)-
COLUMN(D4)-5)))INDEX(D4:CL4,,MAX(IF(D4:CL4<>"",COLUMN(D4:CL4)-
COLUMN(D4)+1)))))

What am I do wrong?- Hide quoted text -

- Show quoted text -

Yes, I did use the array, the problem is the -0.91 is not the correct
answer for the 6 week average. the correct answer would be -1.70.
The -0.91 is the 7 week average. That's why I included the week 29
value because somehow it is being included in the calculation.

 

[DonFlak]

Okay, spotted my error

=AVERAGE(INDEX(D4:CL4,,MAX(IF(D4:CL4<>"",COLUMN(D4:CL4)-COLUMN(D4)+1))-5):
INDEX(D4:CL4,,MAX(IF(D4:CL4<>"",COLUMN(D4:CL4)-COLUMN(D4)+1))))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)












Yes, I did use the array, the problem is the -0.91 is not the correct
answer for the 6 week average.  the correct answer would be -1.70.
The -0.91 is the 7 week average.  That's why I included the week 29
value because somehow it is being included in the calculation.- Hide quoted text -

- Show quoted text -

That did the trick. Thanks so much.