ExcelBanter - Average values in only last 6 columns

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Average values in only last 6 columns (https://www.excelbanter.com/excel-worksheet-functions/189976-average-values-only-last-6-columns.html)

[email protected]

Average values in only last 6 columns

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 values in only last 6 columns

=AVERAGE(INDEX(D2:CL2,,MAX(IF(D2:CL2<"",COLUMN(D2 :CL2)-COLUMN(D2)-5))):(INDEX(D2:CL2,,MAX(IF(D2:CL2<"",COLUMN(D2:CL 2)-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)

wrote in message
...
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

Teethless mama

Average values in only last 6 columns

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

" wrote:

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

[email protected]

Average values in only last 6 columns

[email protected]

Average values in only last 6 columns

On Jun 4, 9:58*am, "Bob Phillips" wrote:
=AVERAGE(INDEX(D2:CL2,,MAX(IF(D2:CL2<"",COLUMN(D2 :CL2)-COLUMN(D2)-5))):(IN*DEX(D2:CL2,,MAX(IF(D2:CL2<"",COLUMN(D2:C L2)-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)

wrote in message

...

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- Hide quoted text -

- 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:CL 4)-
COLUMN(D4)+1)))))

What am I do wrong?

Bob Phillips

Average values in only last 6 columns

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)

wrote in message
...
On Jun 4, 9:58 am, "Bob Phillips" wrote:
=AVERAGE(INDEX(D2:CL2,,MAX(IF(D2:CL2<"",COLUMN(D2 :CL2)-COLUMN(D2)-5))):(IN*DEX(D2:CL2,,MAX(IF(D2:CL2<"",COLUMN(D2:C L2)-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)

wrote in message

...

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- Hide quoted text -

- 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:CL 4)-
COLUMN(D4)+1)))))

What am I do wrong?

[email protected]

Average values in only last 6 columns

On Jun 4, 2:40*pm, "Bob Phillips" wrote:
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)

wrote in message

...
On Jun 4, 9:58 am, "Bob Phillips" wrote:

=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)

wrote in message

...

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- Hide quoted text -

- 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:CL 4)-
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

Average values in only last 6 columns

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)

wrote in message
...
On Jun 4, 2:40 pm, "Bob Phillips" wrote:
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)

wrote in message

...
On Jun 4, 9:58 am, "Bob Phillips" wrote:

=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)

wrote in message

...

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- Hide quoted text -

- 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:CL 4)-
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.

[email protected]

Average values in only last 6 columns

On Jun 5, 10:54*am, "Bob Phillips" wrote:
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)

wrote in message

...
On Jun 4, 2:40 pm, "Bob Phillips" wrote:

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)

wrote in message

...
On Jun 4, 9:58 am, "Bob Phillips" wrote:

=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)

wrote in message

....

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- Hide quoted text -

- 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:CL 4)-
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.- Hide quoted text -

- Show quoted text -

That did the trick. Thanks so much.

All times are GMT +1. The time now is 03:30 AM.