AVERAGE RANGE CELLS DD:HH:MM FORMAT

[LawNapier]

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​	B	C
1	APPLE	RIPE	00:01:23
2	APPLE	ROTTEN	00:15:33
3	ORANGE	RIPE	00:14:47
4	PEAR	RIPE	01:00:48
5	APPLE	RIPE	06:09:05
6	APPLE	RIPE	00:14:47
7	APPLE	RIPE	00:15:33

 

[LawNapier]

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).

jasonb75 - no problem. it probably gets confusing. I did follow the guidelines and posted my solution here so not sure what Mark858 is getting at. Can you guys give me any insights on the #DIV/0! problem above?

 

[MARK858]

I did follow the guidelines and posted my solution here so not sure what Mark858 is getting at

Not the guidelines, the forum rules which explains why in the rule. I will leave it to a Moderator/Admin to explain more if needed.

Can you guys give me any insights on the #DIV/0! problem above?

Need to know what you need as a result if Pear or Apple is missing.

 

[joeu2004]

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!.

I see a lot of incorrect conclusions here. Please pay close attention to the details.

-----

The #DIV/0 error arises because AVERAGEIFS does not find any numeric data to average.

That is because either the data is text, or there is no row that meets all of the conditions, or both.

In your case, it is probably because the data is text.

Nevertheless, AVERAGEIFS can always return #DIV/0. So it is always prudent to write:

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

That returns zero when there is no data to average.

Note: In general, it is better to enter limited ranges like $C$1:$C$100 instead of whole-column ranges like C:C. The latter form might cause Excel to process as much as 1+ million rows of data and to create internal temp arrays of as much as 1+ million entries. You can change $100 to $1000, $10000, etc -- any reasonable number of rows that covers the most data that you reasonably expect to ever have.

-----

The format of a cell has nothing to do with it, __if__ the values are numeric.

AVERAGEIFS works just fine if the numeric data is formatted as dd:hh:mm or [h]:mm -- although "dd" is a risky format specifier to use, as I explained.

The problem is: ISNUMBER returns FALSE.

So the values are text, not numeric, despite appearances and despite the cell format.

You need to correct that. Not by changing the cell format, but by changing the data.

If you enter data in the form x:y:z, Excel interprets that as x hours y minutes z seconds, __not__ x days y hours z minutes.

But that is still numeric data. It is just the wrong numeric data. You would need to correct that anyway.

However, somehow your data was entered as text, even if the cells are formatted as numeric. It happens!

One way to correct both problems is:

1. In a parallel column, enter formulas of the following form, assuming that numeric data was entered in the form x:y:z that you intended to represent x days y hours z minutes.

=IF(ISNUMBER(C1), INT(C1*24)+MOD(C1*24,1)*60/24, LEFT(C1,FIND(":",C1)-1) + MID(C1,FIND(":",C1)+1,5))

formatted as d:hh:mm , if you insist.

2. Format column C as d:hh:mm , if you insist.

3. Copy the parallel column, and paste-special-value into column C.

4. Delete the values in the parallel column.

 

[Fluff]

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 .

Does this mean that you have asked this question on other sites as well?

 

[joeu2004]

To mimic an OR statement, I've come up with the below and get the results needed.
[....]
=SUM(
AVERAGEIFS(C:C,
A:A,"="&"APPLE",
B:B,"="&"RIPE"),

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

First, you can write simply "apple" instead of "="&"apple" or "=apple". But originally, you said that you wanted cell references, not strings. So, use A1 and A4 instead of "apple" and "pear".

Second and most importantly: in general, the sum of the averages is not the same as the average of the "sum" (combined values for "apple" and "pear").

This is demonstrated below, as well as a suggested solution.

Book1
	A	B	C	D	E	F
1	APPLE	RIPE	0:01:23		1:12:07:21.500	expected
2	APPLE	ROTTEN	0:15:33		2:14:10:04.500	wrong!
3	ORANGE	RIPE	0:14:47		2:14:10:04.500	wrong!
4	PEAR	RIPE	1:00:48		1:12:07:21.500	correct!
5	APPLE	RIPE	6:09:05
6	APPLE	RIPE	0:14:47
7	APPLE	RIPE	0:15:33
8	PEAR	ROTTEN	4:05:06
9	PEAR	RIPE	7:08:09
Sheet2

Formulas:
E1: =AVERAGE(C1, C4, C5:C7, C9)
E2: =SUM(AVERAGEIFS(C:C, A:A, "apple", B:B, "ripe"), AVERAGEIFS(C:C, A:A, "pear", B:B, "ripe"))
E3: =SUMPRODUCT(AVERAGEIFS(C:C, A:A, {"apple";"pear"}, B:B, "ripe"))
E4: =SUMPRODUCT(SUMIFS(C:C, A:A, {"apple";"pear"}, B:B, "ripe")) / SUMPRODUCT(COUNTIFS(A:A, {"apple";"pear"}, B:B, "ripe"))

Note that the cells in column C are formatted as d:hh:mm , and the cells in column E are formatted as d:hh:mm:ss.000 . See my previous caveats about using the "d" format specifier in this way.

E1 shows the expected result by manually selecting the appropriate cells.

E2 is your incorrect formula. E3 demonstrates that the SUMPRODUCT form returns the same result, albeit still incorrect.

E4 shows the recommended alternative. It is easy to expand the array constant {"apple";"pear"} to include others.

Is it okay to use strings like "apple"? Or do you still want to use cell references instead, as you said originally?

(That complicates things. I don't want to confuse you with complications, if they are unnecessary.)

PS.... As I noted previously, it would be better write $A$1:$A$100 instead of A:A. Similarly for other ranges.

 

[LawNapier]

I see a lot of incorrect conclusions here. Please pay close attention to the details.

-----
The #DIV/0 error arises because AVERAGEIFS does not find any numeric data to average.

That is because either the data is text, or there is no row that meets all of the conditions, or both.

In your case, it is probably because the data is text.

Nevertheless, AVERAGEIFS can always return #DIV/0. So it is always prudent to write:

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

That returns zero when there is no data to average.

Note: In general, it is better to enter limited ranges like $C$1:$C$100 instead of whole-column ranges like C:C. The latter form might cause Excel to process as much as 1+ million rows of data and to create internal temp arrays of as much as 1+ million entries. You can change $100 to $1000, $10000, etc -- any reasonable number of rows that covers the most data that you reasonably expect to ever have.

-----

The format of a cell has nothing to do with it, __if__ the values are numeric.

AVERAGEIFS works just fine if the numeric data is formatted as dd:hh:mm or [h]:mm -- although "dd" is a risky format specifier to use, as I explained.

The problem is: ISNUMBER returns FALSE.

So the values are text, not numeric, despite appearances and despite the cell format.

You need to correct that. Not by changing the cell format, but by changing the data.

If you enter data in the form x:y:z, Excel interprets that as x hours y minutes z seconds, __not__ x days y hours z minutes.

But that is still numeric data. It is just the wrong numeric data. You would need to correct that anyway.

However, somehow your data was entered as text, even if the cells are formatted as numeric. It happens!

One way to correct both problems is:

1. In a parallel column, enter formulas of the following form, assuming that numeric data was entered in the form x:y:z that you intended to represent x days y hours z minutes.

=IF(ISNUMBER(C1), INT(C1*24)+MOD(C1*24,1)*60/24, LEFT(C1,FIND(":",C1)-1) + MID(C1,FIND(":",C1)+1,5))

formatted as d:hh:mm , if you insist.

2. Format column C as d:hh:mm , if you insist.

3. Copy the parallel column, and paste-special-value into column C.

4. Delete the values in the parallel column.

------------------------------------------------------------------------------------------------------------------------------

Joeu2004 - Thanks for the response. That is a lot to unpack so here are my responses.

COMMENT 1:
The below is the entire AVERAGEIFS formula that I am using. If you look at the rows colored red you can see I am trying to mimic an OR operation.
Is this the correct way to do this with an AVERAGEIFS formula?
By running each AVERAGEIFS separately, the first three AVERAGEIFS find rows that match the criteria, but the last AVERAGIFS finds no rows that match. So there are some rows that match. Shouldn't the formula average the rows that it found instead of returning "0"?

COMMENT 2:
I added the IFERROR() wrapper as you suggested. I now get "0" as the result when I run the formula. As outlined above, if I have confirmed the first three AVERAGEIFS find
rows that match criteria and the last one doesn't, there are still rows to average. I'm wondering why it is returning "0".

COMMENT 3:
'RAW DATA'!$O4:$O150000 is the column that I am averaging. For all the column ranges, I reduced the column sizes down from X:X as you suggested.

COMMENT 4:
ISNUMBER returns FALSE on cells in 'RAW DATA'!$O4:$O150000. These cells have the below formula in them to provide the row DD:HH:MM value. It is checking for some conditions based on cell values to determine how to calculate the final result DD:HH:MM result.

=IF(I98769<>"closed complete",TEXT(NOW()-F98769,"DD:HH:MM"),IF(G98769="",TEXT(H98769-F98769,"DD:HH:MM"),TEXT(G98769-F98769,"DD:HH:MM")))

I understand what you want me to do with the parallel column to get the column back to the correct numeric state behind the scenes, but that will overwrite the above formula..so I am a bit stuck there.

=IF(ISNUMBER(C1), INT(C1*24)+MOD(C1*24,1)*60/24, LEFT(C1,FIND(":",C1)-1) + MID(C1,FIND(":",C1)+1,5))


SUBJECT PROBLEM FORMULA
--------------------------------------------------------------------

=IFERROR(SUM(

AVERAGEIFS('RAW DATA'!$O4:$O150000,
'RAW DATA'!$E4:$E150000,"="&$B$5,
'RAW DATA'!$K4:$K150000,"="&"INC",
'RAW DATA'!$L4:$L150000,"="&$AG$6,
'RAW DATA'!$N4:$N150000,"="&$AH$4),

AVERAGEIFS('RAW DATA'!$O4:$O150000,
'RAW DATA'!$E4:$E150000,"="&$B$6,
'RAW DATA'!$K4:$K150000,"="&"INC",
'RAW DATA'!$L4:$L150000,"="&$AG$6,
'RAW DATA'!$N4:$N150000,"="&$AH$4),

AVERAGEIFS('RAW DATA'!$O4:$O150000,
'RAW DATA'!$E4:$E150000,"="&$B$7,
'RAW DATA'!$K4:$K150000,"="&"INC",
'RAW DATA'!$L4:$L150000,"="&$AG$6,
'RAW DATA'!$N4:$N150000,"="&$AH$4),

AVERAGEIFS('RAW DATA'!$P4:$P150000,
'RAW DATA'!$E4:$E150000,"="&$B$8,
'RAW DATA'!$K4:$K150000,"="&"INC",
'RAW DATA'!$L4:$L150000,"="&$AG$6,
'RAW DATA'!$N4:$N150000,"="&$AH$4)

),0)

 

[Fluff]

@LawNapier
Can you please answer post#14

 

[LawNapier]

Does this mean that you have asked this question on other sites as well?

No just this forum. Your people indicate that Excel handling of DD is problematic.

 

[LawNapier]

Not the guidelines, the forum rules which explains why in the rule. I will leave it to a Moderator/Admin to explain more if needed.


Need to know what you need as a result if Pear or Apple is missing.

Mark858 - We are at post #16 so that is where we are at now. I'm not sure what you are asking.

 

[LawNapier]

First, you can write simply "apple" instead of "="&"apple" or "=apple". But originally, you said that you wanted cell references, not strings. So, use A1 and A4 instead of "apple" and "pear".

Second and most importantly: in general, the sum of the averages is not the same as the average of the "sum" (combined values for "apple" and "pear").

This is demonstrated below, as well as a suggested solution.

Book1
	A	B	C	D	E	F
1	APPLE	RIPE	0:01:23		1:12:07:21.500	expected
2	APPLE	ROTTEN	0:15:33		2:14:10:04.500	wrong!
3	ORANGE	RIPE	0:14:47		2:14:10:04.500	wrong!
4	PEAR	RIPE	1:00:48		1:12:07:21.500	correct!
5	APPLE	RIPE	6:09:05
6	APPLE	RIPE	0:14:47
7	APPLE	RIPE	0:15:33
8	PEAR	ROTTEN	4:05:06
9	PEAR	RIPE	7:08:09
Sheet2

Formulas:
E1: =AVERAGE(C1, C4, C5:C7, C9)
E2: =SUM(AVERAGEIFS(C:C, A:A, "apple", B:B, "ripe"), AVERAGEIFS(C:C, A:A, "pear", B:B, "ripe"))
E3: =SUMPRODUCT(AVERAGEIFS(C:C, A:A, {"apple";"pear"}, B:B, "ripe"))
E4: =SUMPRODUCT(SUMIFS(C:C, A:A, {"apple";"pear"}, B:B, "ripe")) / SUMPRODUCT(COUNTIFS(A:A, {"apple";"pear"}, B:B, "ripe"))

Note that the cells in column C are formatted as d:hh:mm , and the cells in column E are formatted as d:hh:mm:ss.000 . See my previous caveats about using the "d" format specifier in this way.

E1 shows the expected result by manually selecting the appropriate cells.

E2 is your incorrect formula. E3 demonstrates that the SUMPRODUCT form returns the same result, albeit still incorrect.

E4 shows the recommended alternative. It is easy to expand the array constant {"apple";"pear"} to include others.

Is it okay to use strings like "apple"? Or do you still want to use cell references instead, as you said originally?

(That complicates things. I don't want to confuse you with complications, if they are unnecessary.)

PS.... As I noted previously, it would be better write $A$1:$A$100 instead of A:A. Similarly for other ranges.

Joeu2004 - Thanks again for another response.

The below formula looks like the way I need to go, but I still need the values within the array brackets to be cell references instead of hard coded text strings. I responded to your Post #16 before I read post #15 so my apologies.

E4: =SUMPRODUCT(SUMIFS(C:C, A:A, {"apple";"pear"}, B:B, "ripe")) / SUMPRODUCT(COUNTIFS(A:A, {"apple";"pear"}, B:B, "ripe"))