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

E

Evan

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 are:
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!
 
S

Sheeloo

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Payment calculation 1
Averageifs - criteria is cell is not blank 1
how do i use averageifs function? 2
Use reference text from one cell in another function call 3
AVERAGEIFS: Reference a cell in a criteria range 3
AVERAGEIFS with multiple logical arguments for a range 1
Changing the value in a cell (but a bit more complex than that <G>) 3
how to get the median of cells that meet a given criteria in excel 1

Top