Another way to write your formula:

=AVERAGE(IF(--($D$2:$D$5988<1000000),$H$2:$H$5988))
(still ctrl-shift-entered)

But don't you get results that are misleading if you have empty cells?

This looks like it would do the same:
=SUMIF($D$2:$D$5988,"<"&1000000,$H$2:$H$5988)/COUNTIF($D$2:$D$5988,"<"&1000000)

And you could add that check for dividing by 0:

=IF(COUNTIF($D$2:$D$5988,"<"&1000000)=0,"no data",
SUMIF($D$2:$D$5988,"<"&1000000,$H$2:$H$5988)/COUNTIF($D$2:$D$5988,"<"&1000000))

And I'd check for div/0 errors in the original range, too.


cj21 wrote:

=AVERAGE(IF(--(LEFT(TEXT($D$2:$D$5988,"00000000"),2)=TEXT(M3,"00 ")),$H$2:$H$5988))

This is my formula. It is a bit complicated but there is not a mistake.
I have used it for other data sets which are exactly the same and it
works.

Chris

--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=500178

--

Dave Peterson