MS Excel formula issue

smuggler.ie

Have sheet where two cells contain formula that read range and depending on time range, calculate average of data range:

=AVERAGEIFS(O2:O7,B2:B7,"<17:00:00",B2:B7,">=02:00:00") - this work OK

=AVERAGEIFS(O2:O7,B2:B7,"<02:00:01",B2:B7,">=16:59:59") - this return #DIV/0!


Can anyone point where is my mistake or excel limitation?





Edit: Column B format is "time"

The Continental Op

I use Excel but I'm not an expert.

First thing I noticed that none of the data in column B are number.

I don't see 16:10:00 is a number.

If you add up all the "numbers" in column B do you get an error if so then you'll never get an average.

Edit> Forget that just shows how little I know

Zaney

Looks to me like a flaw in the logic. A number can’t be less than 02:00 and greater than 16:59:59. Is it a case of needing an if them else type statement?

The Continental Op

Zaney wrote: »

Looks to me like a flaw in the logic. A number can’t be less than 02:00 and greater than 16:59:59. Is it a case of needing an if them else type statement?

Its time isn't it? Hrs:Minutes:Seconds.

Can you average time?

Convert to seconds average then convert back?

Nice when Google backs you up https://www.extendoffice.com/documents/excel/2615-excel-average-timestamps.html#:~:text=We%20can%20calculate%20the%20time,to%20the%20Range%20B2%3AB6.

johnb25

Zaney wrote: »

Looks to me like a flaw in the logic. A number can’t be less than 02:00 and greater than 16:59:59. Is it a case of needing an if them else type statement?

Do the times run over different days? If yes then you need to incorporate that into the formula.

smuggler.ie

As per formula, days cells not in the equation.
Here is screenshot of formula that works - as you see, it takes second part of 24 hour clock range

smuggler.ie

to mine knowledge, excel does not see time as "time" its just decimal number
02:00:01 to excel is like 0.0833449074074074

The Continental Op

What version of Excel is this I'm on 2016 and where you have a comma in the formula I get a ;

Typing out your data and then creating the function both work.

If I try and change the ; to commas the expression is invalid.

Sorry if all I'm doing is showing my ignorance

smuggler.ie

The Continental Op wrote: »

What version of Excel is this I'm on 2016 and where you have a comma in the formula I get a ;

Typing out your data and then creating the function both work.

If I try and change the ; to commas the expression is invalid.

Sorry if all I'm doing is showing my ignorance

No ignorance if it works, i suspect there is some not right with my excel (2016)
could you post formula or screenshot so i can try replicate?

The Continental Op

To double check what is your format for the column O?

smuggler.ie

The Continental Op wrote: »

To double check what is your format for the column O?

number

The Continental Op

But I'm sure there must be a rookie error in there somewhere

Edit> Course there was had 16:10:00 instead of 06:10:00 but both work.

No that doesn't work it should have been =AVERAGEIFS(D1:D6;B1:B6;"<02:00:01";B1:B6;">=16:59:59") and I have
=AVERAGEIFS(D1:D6;B1:B6;"<02:00:01";D1:D6;">=16:59:59")

Sorry for any confusion.

Muddy Fox

Is it not just that no number can be less than 2 and greater or equal to 5?

(i.e. the only number that is less than 2 is 1 and this cannot be greater than or equal to 5 also)

And therefore this if function can never be satisfied?

Sherfin

Was just going to post same as Fox

AVERAGEIFS returns #DIV/0! if no cells in meet criteria.

smuggler.ie

Muddy Fox wrote: »

Is it not just that no number can be less than 2 and greater or equal to 5?

(i.e. the only number that is less than 2 is 1 and this cannot be greater than or equal to 5 also)

And therefore this if function can never be satisfied?

Doh!

smuggler.ie

Does this fulfill mathematical logic or i need to go for more coffee ?

=AVERAGEIFS(O2:O7,B2:B7,">17:00:01",B2:B7,"<23:59:59")

still #

Edit: never mind, time column does not have satisfying figures

smuggler.ie

Thanks everyone for input.
Could not manage to averageifs other range of the "clock", ended up with :

=(SUMIFS(O2:O7,B2:B7,">=17:00:00",B2:B7,"<=23:59:59")+SUMIFS(O2:O7,B2:B7,">=00:00:00",B2:B7,"<02:00:00"))/(COUNTIFS(B2:B7,">=17:00:00",B2:B7,"<=23:59:59")+COUNTIFS(B2:B7,">=00:00:00",B2:B7,"<02:00:00"))

Works, but convoluted. If any one has better idea, let me know.

Sherfin

Do you need the <=23:59:59 and >=00:00:00 ?

If you do something like
(sum(>17)+sum<2)/(count(>17)+count(<2)

Still not pretty but possibly easier to read?

smuggler.ie

Sherfin wrote: »

Do you need the <=23:59:59 and >=00:00:00 ?

If you do something like
(sum(>17)+sum<2)/(count(>17)+count(<2)

Still not pretty but possibly easier to read?

Thanks, that helped

=(SUMIFS(O2:O7,B2:B7,">=17:00:00")+SUMIFS(O2:O7,B2:B7,"<02:00:00"))/(COUNTIFS(B2:B7,">=17:00:00")+COUNTIFS(B2:B7,"<02:00:00"))