AVERAGE RANGE CELLS DD:HH:MM FORMAT

LawNapier

New Member
Joined
May 18, 2020
Messages
26
Office Version
  1. 365
Platform
  1. Windows
I am trying to average the below in DD:HH:MM format. The cells format is set as "custom > DD:HH:MM".

I want to only average the time for the the rows that have "APPLE" and "RIPE" in them using the Cell reference instead of the actual words "APPLE" and "RIPE".

I've tried AVERAGEIFS and various other solutions from many forums and can't get anything to work. Hoping there is a genius here that has a solution. Thanks in advance.



A​
BC
1APPLERIPE00:01:23
2APPLEROTTEN00:15:33
3ORANGERIPE00:14:47
4PEARRIPE01:00:48
5APPLERIPE06:09:05
6APPLERIPE00:14:47
7APPLERIPE00:15:33
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
I am trying to average the below in DD:HH:MM format. The cells format is set as "custom > DD:HH:MM".
[....]
I've tried AVERAGEIFS and various other solutions from many forums and can't get anything to work.

What do you mean by "can't get anything to work". What does AVERAGEIFS return?

And what formula(s) do you use?

The first problem might be with the values in column C. What does =ISNUMBER(C1) return: TRUE or FALSE?

If FALSE, the data (at least in C1) are text, regardless of the cell format. And if you manage to coerce Excel to interpret the data as numeric, 06:09:05 is usually interpreted as 6h 9m 5s, for example.

If ISNUMBER returns TRUE for all of column C, a proper AVERAGEIFS should return a valid number. You might simply need to format it properly: Custom dd:hh:mm again, or some variant of dd:hh:mm:ss.000 . The formula would be:

AVERAGEIFS($C$1:$C$7, $A$1:$A$7, A1, $B$1:$B$7, B1)

However, "dd" is not a valid time specifier. It will work only by coincidence when the number of days is 31 or less.

"dd" really formats the day of the month, not a number of days.

When integer part of the numeric time is 0 through 31, that is interpreted as Jan 0 through 31.

But when integer part is 32, that is interpreted is Feb 1. So Excel formats "dd" as 1 instead of 32.
 
Upvote 0
What do you mean by "can't get anything to work". What does AVERAGEIFS return?

And what formula(s) do you use?

The first problem might be with the values in column C. What does =ISNUMBER(C1) return: TRUE or FALSE?

If FALSE, the data (at least in C1) are text, regardless of the cell format. And if you manage to coerce Excel to interpret the data as numeric, 06:09:05 is usually interpreted as 6h 9m 5s, for example.

If ISNUMBER returns TRUE for all of column C, a proper AVERAGEIFS should return a valid number. You might simply need to format it properly: Custom dd:hh:mm again, or some variant of dd:hh:mm:ss.000 . The formula would be:

AVERAGEIFS($C$1:$C$7, $A$1:$A$7, A1, $B$1:$B$7, B1)

However, "dd" is not a valid time specifier. It will work only by coincidence when the number of days is 31 or less.

"dd" really formats the day of the month, not a number of days.

When integer part of the numeric time is 0 through 31, that is interpreted as Jan 0 through 31.

But when integer part is 32, that is interpreted is Feb 1. So Excel formats "dd" as 1 instead of 32.


Hi - I ran =ISNUMBER(C1) and got "FALSE". I experimented and used the AVERAGEIFS() function on a column that was formatted HH:MM and it seems to work, however if I use it on a column formatted as in my example above as DD:HH:MM I get a return of #DIV/0!.
 
Upvote 0
Everything you say points to your data being in a text format (your custom format of dd:hh:mm is redundant, text in the cell will override the format).

What exactly is in C1?

Does it contain a formula, if so what is that formula, and where is it getting the data from?

Is the data imported? Data copied from sources such as a web page can often include hidden characters which can be problematic.
 
Upvote 0
Everything you say points to your data being in a text format (your custom format of dd:hh:mm is redundant, text in the cell will override the format).

What exactly is in C1?

Does it contain a formula, if so what is that formula, and where is it getting the data from?

Is the data imported? Data copied from sources such as a web page can often include hidden characters which can be problematic.


Jasonb75 -- Thanks for your response.

Based on suggestions by other forums I gave up on trying to average time duration measuring DD:HH:MM and settling on [H]:MM format . I'm hearing Excel can't handle days calculations well.

To mimic an OR statement, I've come up with the below and get the results needed.

However, lets say the AVERAGEIFS statement for PEARS returns no records that match the criteria, then I get a #DIV/0! result. Do you know how I can handle if a query does not find records and presents a "0" in the averaging process?


=SUM(

AVERAGEIFS(C:C,
A:A,"="&"APPLE",
B:B,"="&"RIPE"),

AVERAGEIFS(C:C,
A:A,"="&"PEAR",
B:B,"="&"RIPE"),


)
 
Upvote 0
You all closed my other post which had another issue I am running into concerning #DIV/0!, which was separate from this post. I used the same fruit scenerio so you problably thought it was the same problem. These were two different problems. The solution provided in this post by FormR did not work. So I figured it out finally on my own from some other forums and posted the solution above. However ran into another separate averaging #DIV/0! problem, so posted it in a separate thread since it was a different problem.
Since you closed my other post. Please can you now assess the #DIV/0! problem I have outlined above? Thanks.
 
Upvote 0
Please post links to the other forums that you posted in (as per the forum rules) so that we can see what has been tried and answered before we spend time on the question.
I'm hearing Excel can't handle days calculations well.
What makes you think this?
 
Upvote 0
Please post links to the other forums that you posted in (as per the forum rules) so that we can see what has been tried and answered before we spend time on the question.

What makes you think this?

Hi Mark858 - Based on other forum comments and that when I change the format from DD:HH:MM to [H]:MM format the average works fine. So settling on that format.

The only problem I have now is say the AVERAGEIFS statement for PEAR below returns no records that match the criteria, then I get a #DIV/0! result. Do you know how I can handle if a query does not find records and presents a "0" in the averaging process?


=SUM(

AVERAGEIFS(C:C,
A:A,"="&"APPLE",
B:B,"="&"RIPE"),

AVERAGEIFS(C:C,
A:A,"="&"PEAR",
B:B,"="&"RIPE"))
 
Upvote 0
I didn't close your post, I merely pointed you towards FormR's reply in your other thread because they appeared to be the same.
The fact that your thread was closed implies that a moderator was of the same opinion.

As Mark has pointed out, there is a rule asking for links to any posts in other forums where you have asked the same question (all dedicated excel forums that I know of have this rule, so you really should be posting links to your Mr Excel questions in those forums as well).
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top