Rolling average across columns with criteria

I am urgently needing assistance have been trying to use previous assistance but does not appear to work for this.

I have the following data and would like to be able to avearge across the row for each employee. I would like the managers to be able to selet from validation list the average period ie. 3,4,5.

I have attached a sample of the existing report

Any assistance is greatly appreciated

Lee

i want to know why this formula has been used in excel

If the most recent month with data is in cell z2 (you need to idenfify the most recent month with data, to do the preceding months in the Moving average)

Then the formula in cell z8 is

=AVERAGE(OFFSET($A4,0,MATCH($z$2,$j$8:$v$8,0)-$z$4+1,1,$z$4))

Does that help

Regards

Dav

Originally Posted by Dav

If the most recent month with data is in cell z2 (you need to idenfify the most recent month with data, to do the preceding months in the Moving average)

Then the formula in cell z8 is

=AVERAGE(OFFSET($A4,0,MATCH($z$2,$j$8:$v$8,0)-$z$4+1,1,$z$4))

Does that help

Regards

Dav

Hi Dav

Have tried pasting this and it keep returning n/a# am I missing something

Thx

Lee

no my transposing from another sheet failed!

=SUM(OFFSET($I8,0,MATCH($Z$2,$J$6:$V$6,0)-$Z$4+1,1,$Z$4))

should be what you want as long as the date has been put in z2

sorry about that

regards

Dav

Hi Dav

Thanks a million work like a dream.

Lee