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.

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,SUMPROD UCT(LARGE(ROW(B1:B100)*(B1:B100<0),3))):B100))

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

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


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

=AVERAGE(IF(INDEX(B1:B10,SUMPRODUCT(LARGE(ROW(B1:B 10)*(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.999999 9E+307,B1:B10))))

also confirmed with the CSE key combo.

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


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=558513

Here's another way...

=AVERAGE(IF(B2:B1000,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!

In article . com,
"Greg" wrote:

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.