Dynamic Average Question

[Greg]

Hi All,

I am looking to write a formula that will average the total number of
hours worked for the last three months, but do not want to include any
months with zero values. In other words, the last three months could
actually be January, March and July (or they could be consecutive). I
was thinking of an average in conjunction with a counta formula, but
can't think of a way to have it stop and average when it hits 3 months.
Any Ideas? Let me know if I should explain further.

Example:

Jan. 150 hrs.
Feb. 0 hrs.
Mar. 110 hrs.
Apr. 0 hrs.
May 0 hrs.
June 160 hrs.

In this example it would skip Feb. Apr. May, but if July had data, it
would need to drop Jan. and use Mar., June and July.

 

[Vito]

Assuming your values are in column B:

Try:

=AVERAGE(IF(INDEX(B1:B100,SUMPRODUCT(LARGE(ROW(B1:B100)*(B1:B100<>0),3))):B100,INDEX(B1:B100,SUMPRODUCT(LARGE(ROW(B1:B100)*(B1:B100<>0),3))):B100))

Confirmed with CTRL+SHIFT+ENTER....not just ENTER (this is an arra
formula).

Adjust ranges to accomodate all future entries (include header row).


If you have Excel 2003, then you can convert your current lis
(assuming it's range is B1:B10 (including header row) to a List vi
Data|List and then use this formula

=AVERAGE(IF(INDEX(B1:B10,SUMPRODUCT(LARGE(ROW(B1:B10)*(B1:B10<>0),3))):INDIRECT("B"&MATCH(9.9999999E+307,B1:B10)),INDEX(B1:B10,SUMPRODUCT(LARGE(ROW(B1:B10)*(B1:B10<>0),3))):INDIRECT("B"&MATCH(9.9999999E+307,B1:B10))))

also confirmed with the CSE key combo.

With List feature, when you add more records, the formula will updat
it's range automatically

 

[Domenic]

Here's another way...

=AVERAGE(IF(B2:B100>0,IF(ROW(B2:B100)>=LARGE(IF(B2:B100,ROW(B2:B100)),3),
B2:B100)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. This assumes
that Column B contains your numbers, and does not include the text 'hrs'.

Hope this helps!