wrote:
I tried to use
=AVERAGEIFS(B2:B16,A2:A16,"=D2",A2:A16,"<=E2")
but the = isn't seeing the value in the cell D2 so it returns zero.

That's not the problem. The problem is that the cell references D2 and E2
should not be within the quotes. Try:

=AVERAGEIFS(B2:B16,A2:A16,"=" & D2,A2:A16,"<=" & E2)