Formula? - Averaging last 4 values

[shawn212]

Hi,
I have my data set up the following way - the columns refer to th
month and the rows are the different projects I'm tracking. Projec
"A" has data in months Jan-Oct (10 values), while Project "B" has dat
in months Jan, March, July, August & Sept (5 values)-the missing month
are blank. Is there a way get the average of the last 4 values fo
each project? Maybe using a count function?

thank

 

[Frank Kabel]

Hi
if your values are in A1:J1 use the following array
formula (entered with CTRL+SHIFT+ENTER)
=AVERAGE(OFFSET(J1,0,0,1,-(COLUMN(J1)-LARGE(IF(ISNUMBER
(A1:J1),COLUMN(A1:J1)),4)+1)))

 

[Jason Morin]

One way, assuming project A value start in B2:

=AVERAGE(OFFSET(B2,,COUNT(B2:IV2)-1,,-4))

HTH
Jason
Atlanta, GA

 

[Frank Kabel]

Hi Jason
won't work if the OP has blank months in between

 

[Jason Morin]

OP didn't state that there would be blanks in between.

 

[Frank Kabel]

Hi Jason
though we will never know for sure if the OP does not come back
he stated
.....while Project "B" has data in months Jan, March, July, August &
Sept (5 values)-the missing months
are blank...