how do i use averageifs function?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lee - 04 Feb 2008 22:11 GMT

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.

Reply to this Message

T. Valko - 04 Feb 2008 22:22 GMT

Try this:

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

Signature

Biff
Microsoft Excel MVP

>I am trying to use the averageifs function to determine the average
> temperature given a specific date. When referenceing the dates to useI am
[quoted text clipped - 22 lines]
> data is less than the reference (when treated as a serial number). No the
> dates/times are not listed as text.

Reply to this Message

T. Valko - 04 Feb 2008 22:33 GMT

Or, since you only have one criteria you could use:

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

Signature

Biff
Microsoft Excel MVP

> Try this:
>
[quoted text clipped - 29 lines]
>> the
>> dates/times are not listed as text.

Reply to this Message