Try this:

=AVERAGEIFS(Sheet1!$B$2:$B$849,Sheet1!$A$2:$A$849, "<"&M130)

--
Biff
Microsoft Excel MVP


"Lee" wrote in message
...
I am trying to use the averageifs function to determine the average
temperature given a specific date. When referenceing the dates to useI am
linking to another cell. Apperently it think the cell link is text, not a
link. how can I fix this...Ex

=AVERAGEIFS(Sheet1!$B$2:$B$849,Sheet1!$A$2:$A$849, "<M130")

where m130 = 1/31/08 12:30 PM

here is the data in sheet 1

column a colum b
Time TemperatureF
1/29/08 12:04 AM 49.00
1/29/08 12:16 AM 49.00
1/29/08 12:27 AM 49.00
1/29/08 12:34 AM 49.00
1/29/08 12:44 AM 50.00
1/29/08 12:57 AM 50.00
1/29/08 1:04 AM 49.00
1/29/08 1:13 AM 48.00
1/29/08 1:26 AM 48.00

With this I get a #DIV/0! error. which I know is wrong because the above
data is less than the reference (when treated as a serial number). No the
dates/times are not listed as text.