Average last 3 entries in a row exluding not data cella

**sling1** · 04-20-2013, 03:26 PM

I need only the last three weeks entries to be averaged. My formula is not working. Any help is appreciated.
Steve

**AZ-XL** · 04-20-2013, 04:16 PM

Put formula into c3 cell

=AVERAGEIF(OFFSET(E3,0,LARGE(IF(OFFSET(E3,0,0,1,COUNTA($2:$2)-4)>0,COLUMN(OFFSET(E3,0,0,1,COUNTA($2:$2)-4))-4,""),3)-1,1,COUNTA($2:$2)-4-LARGE(IF(OFFSET(E3,0,0,1,COUNTA($2:$2)-4)>0,COLUMN(OFFSET(E3,0,0,1,COUNTA($2:$2)-4))-4,""),3)+1),"<>0") Ctrl+Shift++Enter

The formula above arranged to add more weeks. It will always average last-three-non zero weeks.

Uploading file to analyse

If your problem is solved Click star

**Jacc** · 04-20-2013, 04:35 PM

Wow! Another crazy formula! I was just gonna post a link to daddylonglegs version here: http://www.excelforum.com/excel-form...=1#post3171269

I put together this UDF that seems to work. Goes either right or left.

Please Login or Register  to view this content.

**sling1** · 04-20-2013, 06:57 PM

Originally Posted by Jacc

Wow! Another crazy formula! I was just gonna post a link to daddylonglegs version here: http://www.excelforum.com/excel-form...=1#post3171269

I put together this UDF that seems to work. Goes either right or left.

Loving UDF! Is there a way it can consider data that does not have a total of three weeks to average? Two weeks of data would return that average. One week of data would return that score. That would help in in the beginning of the year until competitors have three weeks to average.

**Tony Valko** · 04-20-2013, 09:32 PM

Try this array formula** entered in C3 and copied down:

=IF(COUNT(E3:AB3),AVERAGE(AB3:INDEX(E3:AB3,LARGE(IF(ISNUMBER(E3:AB3),COLUMN(E3:AB3)),MIN(3,COUNT(E3:AB3)))-COLUMN(E3)+1)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.