AverageIFS between values dictated by cells

[Marq555]

Dear all

I cannot make an averageifs formula to work, the formula is: AVERAGEIFS(D$1:D$24,$B$1:$B$24,$B30,$A$1:$A$24,">="&$A$30,$A$1:$A$24,"<="&$A$33) as it returns the #DIV/0! error. I have just spent 2,5 hours trying to google a solution and ended up here.

Could someone please take a look at the attached file (the formula is in cells D30:D33) and give me a hand with the problem above?


Kind regards


Marek

[Jonmo1]

Your times in A1:A24 & A30 & A33 & Q1:Q6 are NOT really time values.
They're just text strings that look like time values.

Repeat this on each set of numbers.

Copy any blank cell
Highlight one of the ranges, say A1:A24
Right click - Paste Special - Values - Add
Format the cells as TIME, instead of General or Text

Repeat for each cell containing a time string.

[Czeslaw]

Ctrl + h, find the ":" and replace with ":".

[Marq555]

Guys, you are missing the point.
If the formula in column "C" works why doesn't the one in column "B"
Formula in column C is based on 1 criteria whereas in the one in column B on 2, extended criterias.

This is not a formatting issue, I want the formula in column B to calculate average between values displayed in cells A30 and A33 inclusive.


Regards

[sandy666]

change A column format to Custom hh:mm and re-type hours. and it will start "averageifs-ing"

Sample_sandy.xlsx

btw. in C it works because you have no operators < or >. in D text cannot be < or >, if you leave there = it will works but without < , >

[Marq555]

Fantastic, so it was the formatting issue.

Thank you all for your answers.


Regards

[sandy666]

I'm glad I could help