Function to return Previous Data

[ARayburn]

I have a spreadsheet in which there are 53 weeks in columns. As the year goes on, the weeks fill with data. At the end of the columns there will be numerous functions. I need a function that will replace the range within these functions and only use the previous 6 cells with data (non-blank cells), or maybe have a separate cell with the previous amount of weeks to use and have this function refer to it. Attached is an example. Any help is greatly appreciated.

Prev6.xlsx

[benishiryo]

hi ARayburn, try this array formula:
=AVERAGE(BE3:INDEX(E3:BE3,LARGE((E3:BE3<>"")*($E$2:$BE$2),6)))
...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Ghozi Alkatiri]

may be this is you desired
use array formula
{=AVERAGE(OFFSET(D3,,MAX(IF(LEN(E3:BE3),E2:BE2))-5,,6))}

[ARayburn]

You are great! Thank you, it is working for simple functions however this is what I am trying to do. I need to calculate a rate of sale excluding any major deviations. This is where it gets a little complicated. I basically:
1. Calculated the STDEV for the 6 cells.
2. Calculated the AVERAGE of those 6 cells.
3. Did a SUMIF function stating to add those 6 cells that fall within the lower limit of the standard deviation and the upper limite. I.E. a data set has a STDEV of 75 and an AVERAGE of 900. I am only calculating cells with numbers 825<x<975.
4. After adding those numbers I do a COUNTIF function dividing #3 by the number of cells that fall within the same category.

Here is what I have:
=(SUMIFS(AR3:AW3,AR3:AW3,"<"&(AVERAGE(AR3:AW3)+STDEV(AR3:AW3)),AR3:AW3,">"&(AVERAGE(AR3:AW3)-STDEV(AR3:AW3)))/COUNTIFS(AR3:AW3,"<"&AVERAGE(AR3:AW3)+STDEV(AR3:AW3),AR3:AW3,">"&AVERAGE(AR3:AW3)-STDEV(AR3:AW3)))

I know it is pretty complicated but this would be great if I could figure it out. I basically need to replace the red ranges with the formulas you have come up with, however when I do so it won't work.