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.