AVERAGEIFS function not working in some of the data sheets

The problem is that column E is not formatted as date, you can see that when you place a formula in column V

Please Login or Register  to view this content.

You get the value 0

If you now format one value in column E as date and retype it you'll see it works, so you've got some work to do.

An average cannot be calculated in divide by 0

Great John, I think the OP did not understand me when I said the formatting the cells was not enough

Originally Posted by JohnTopley

Look at Column D date conversion (as per my post)


=DATEVALUE(C2&"/"&B2&"/"&A2)

Use D instead of E in the formula or copy D to E (values)

ok, I didn't get it the first time, but after reviewing your file I found a loop error every month for dates between the 12th of the month till the end of it, then normal values starting from day one 1st of the month till the 12th of it, and the error loops again ...

and when applying it to my file it gives #VALUE error after the 12th day till the end of the data column.

Originally Posted by JohnTopley

So is it working or not?

no, not working for my side ... (attached is the file you fixed), I have added another year data and applied the same function but no luck.

I copied the formula down with NO errors: I used column D in your formula.

PLUs MAX/MIN

=AGGREGATE(14,6,$J$2:$J$366/($D$2:$D$366>=$T2)/($D$2:$D$366<=EOMONTH($T2,0)),1)

=AGGREGATE(14,6,$K$2:$K$366/($D$2:$D$366>=$T2)/($D$2:$D$366<=EOMONTH($T2,0)),1)