Average/Offset Function

[kmprocto]

Sir:



I have 15 columns with several hundred rows of data. The last seven columns (I:O), as of now, are weekly hours for employees. Column P is a total for the most recent six weeks. The sum function below always pulls in the most recent six weeks. Column Q is an average column. My second formula below is not working because it is including the total column value. how can I have the formula skip column P when taking the average in column Q? Any help would be greatly appreciated.



=SUM(OFFSET(P8,0,-6,1,6))

=AVERAGE(OFFSET(Q8,0,-7,1,7))

[DBY]

Hello
I think you just need to use the same parameters as in your sum function and not change them to 7 as in your average function. e.g.

=AVERAGE(OFFSET(P1,0,-6,1,6))

I assume you might be adding columns as your P1 reference is not absolute.

Hope this helps.

[kmprocto]

Yes that works great! Thanks for the help