I can't reference a cell as the criteria in the AVERAGEIFS functio
 

I'm trying to use the AVERAGEIFS function to average a range of numbers if
the numbers are greater than the value in one cell and less than the value in
another cell. However when I try to reference specific cells as the criteria
in the formula I get a #DIV/0! error.

The formula reads: =AVERAGEIFS(B3:B15,B3:B15,"B18",B3:B15,"<B19")

IN this case B3:B15 a
23
31
17
35
41
30
23
34
29
26
29
20
34

And B18 is 22 and B19 is 35

If I replace B18 with 22 and B19 with 35 in the forumla,
i.e. =AVERAGEIFS(B3:B15,B3:B15,"22",B3:B15,"<35")
it calculates correctly with no error.

Why can't a refence those cells as the criteria??? Thanks in advance!

I can't reference a cell as the criteria in the AVERAGEIFS functio
 

You need to use
=AVERAGEIFS(B3:B15,B3:B15,B18,B3:B15,B19)

and enter 22 and <19 in B18 and B19 respectively...

"Evan" wrote:

I'm trying to use the AVERAGEIFS function to average a range of numbers if
the numbers are greater than the value in one cell and less than the value in
another cell. However when I try to reference specific cells as the criteria
in the formula I get a #DIV/0! error.

The formula reads: =AVERAGEIFS(B3:B15,B3:B15,"B18",B3:B15,"<B19")

IN this case B3:B15 a
23
31
17
35
41
30
23
34
29
26
29
20
34

And B18 is 22 and B19 is 35

If I replace B18 with 22 and B19 with 35 in the forumla,
i.e. =AVERAGEIFS(B3:B15,B3:B15,"22",B3:B15,"<35")
it calculates correctly with no error.

Why can't a refence those cells as the criteria??? Thanks in advance!

I can't reference a cell as the criteria in the AVERAGEIFS functio
 

Try it like this:

=AVERAGEIFS(B3:B15,B3:B15,""&B18,B3:B15,"<"&B19)

--
Biff
Microsoft Excel MVP


"Evan" wrote in message
...
I'm trying to use the AVERAGEIFS function to average a range of numbers if
the numbers are greater than the value in one cell and less than the value
in
another cell. However when I try to reference specific cells as the
criteria
in the formula I get a #DIV/0! error.

The formula reads: =AVERAGEIFS(B3:B15,B3:B15,"B18",B3:B15,"<B19")

IN this case B3:B15 a
23
31
17
35
41
30
23
34
29
26
29
20
34

And B18 is 22 and B19 is 35

If I replace B18 with 22 and B19 with 35 in the forumla,
i.e. =AVERAGEIFS(B3:B15,B3:B15,"22",B3:B15,"<35")
it calculates correctly with no error.

Why can't a refence those cells as the criteria??? Thanks in advance!