Array Formula Problem

[bw]

=AVERAGE(IF(MONTH(B$5:B$200)=J2,H$5:H$200))

The above array formula is used in 12 different cells, for each month of the
year. All of the formulas generate the proper values, except for Month 1
(January).

For January, and January ONLY, this formula only works when the range is the
exact same size as the last date in the worksheet.

For example:
Row 65 contains the last date (in Column B), Rows greater than 65 contain
null values for the date (Column B), and null values to average (Column H).
So when J2 has a value of 1, the formula only works when I change it to the
last row as follows:

=AVERAGE(IF(MONTH(B$5:B$65)=J2,H$5:H$65))

Can someone give me a hint as to what may be the problem here?

Thanks,
Bernie
p.s.
Data is sorted by date (column B), and the formula works for all months
except January.

 

[Ron Rosenfeld]

=AVERAGE(IF(MONTH(B$5:B$200)=J2,H$5:H$200))

The above array formula is used in 12 different cells, for each month of the
year. All of the formulas generate the proper values, except for Month 1
(January).

For January, and January ONLY, this formula only works when the range is the
exact same size as the last date in the worksheet.

For example:
Row 65 contains the last date (in Column B), Rows greater than 65 contain
null values for the date (Column B), and null values to average (Column H).
So when J2 has a value of 1, the formula only works when I change it to the
last row as follows:

=AVERAGE(IF(MONTH(B$5:B$65)=J2,H$5:H$65))

Can someone give me a hint as to what may be the problem here?

Thanks,
Bernie
p.s.
Data is sorted by date (column B), and the formula works for all months
except January.

1. What exactly happens if you don't alter the formula?
2. If you select cells below row 65, and examine the formula bar,is it empty
or is there some formula there? If so, what is the formula?


--ron

 

[Bob Phillips]

Bernie,

That is because the empty cells are being treated as 01/01/1900, i.e they
pass the month 1 test. You need to test for blanks as well

=AVERAGE(IF((MONTH(B$5:B$200)=J2)*(B$5:B$200<>""),H$5:H$200))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Peo Sjoblom]

It's because Excel sees blank as a zero (put 0 in a cell and format it as
mm/dd/yyyy and you'll see it will be January 0 1900 the day Excel dates
started) so you must have blank cells in there

=AVERAGE(IF((MONTH(B$5:B$200)=J2)*(ISNUMBER(B$5:B$200)),H$5:H$200))

should take care of it

 

[Max]

Try instead, array-entered:
=AVERAGE(IF(($B$5:$B$200<>"")*(MONTH(B$5:B$200)=J2),H$5:H$200))
to avoid the problems faced with null values within B5:B200 evaluating to
TRUE

 

[bw]

Wow! Thanks guys...
I never would never have been able to solve this "mysterious problem"
without your help.

Thanks again,
Bernie