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

 

[Fluff]

No just this forum.

Ok, thanks for that.

 

[joeu2004]

=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 don't have time to digest all of your response, and it has been noted that you have several other threads that might be addressing parts of these questions.

But let me address this formula, taken out of context.

Obviously, the problem is: the TEXT function returns text, not a numeric value. And the usual fix (to write --TEXT...) will not work because Excel will interpret d:h:m as hours, minutes and seconds, not days, hours and minutes.

Why are you using TEXT(...,"dd:hh:mm"), in the first place? Are you trying to "round" to the minute? Or you trying to control the appearance?

If the latter, the simple remedy is the following:

=IF(I98769<>"closed complete", NOW()-F98769, IF(G98769="", H98769-F98769, G98769-F98769))

formatted as Custom d:hh:mm . (See my previous comments about the risks of using the "d" specifier in this manner.)

Note: I am assuming that your calculation is correct, in the first place. NOW returns the current date as well as time of day (precise to the 1/100th second). So for the calculation to be correct, G98769:H98769 must also contain a date as well as time of day. Is that right?

If you are also trying to round to the minute, that can be done several ways. For example:

=IF(I98769<>"closed complete", MROUND(NOW()-F98769, "0:1"), MROUND(IF(G98769="", H98769-F98769, G98769-F98769), "0:1"))

Note: In that context, "0:1" is converted to the numeric value of 1 minute, which is a decimal fraction. I usually deprecate the use of MROUND with a non-integer in the second parameter. But it is probably good enough for your purposes.

 

[joeu2004]

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.
[....]
E4: =SUMPRODUCT(SUMIFS(C:C, A:A, {"apple";"pear"}, B:B, "ripe")) / SUMPRODUCT(COUNTIFS(A:A, {"apple";"pear"}, B:B, "ripe"))

I believe this question was answered in one of your other threads. (That's why we don't like you to post multiple threads for essentially the same question or parts of the same question.)

But for completeness, I would suggest the following.

Book1
	A	B	C	D	E	F
1	APPLE	RIPE	0:01:23		0:02:28:17.500	expected
2	APPLE	ROTTEN	0:15:33		0:05:44:40.500	wrong!
3	ORANGE	RIPE	0:14:47		0:05:44:40.500	wrong!
4	PEAR	RIPE	1:00:48		0:02:28:17.500	correct!
5	APPLE	RIPE	6:09:05		0:02:28:17.500	correct!
6	APPLE	RIPE	0:14:47
7	APPLE	RIPE	0:15:33		apple
8	PEAR	ROTTEN	4:05:06		pear
9	PEAR	RIPE	7:08:09
Sheet2

The new formula is in E5, to wit:

=SUMPRODUCT(SUMIFS(C:C, A:A, E7:E8, B:B, "ripe")) / SUMPRODUCT(COUNTIFS(A:A, E7:E8, B:B, "ripe"))

where E7 contains the string "apple", and E8 contains the string "pear".

If your cell references are not in a contiguous range, you could replace E7:E8 with CHOOSE({1,2},A1,A4).

(Aside.... You might ask about using a pivot table for this calculation. I'm not a pivot table person. But it might be ideally suited for your purpose.)

 

[LawNapier]

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

Hi Joeu2004 - I asked the very same question about cell references in the array portion. No one seems to have an answer to that. As for the multiple threads that is not true and was a misunderstanding. I had posted another thread asking about a different problem using fruit as an example and people made some assumptions. Thank you for your help though.

 

[jasonb75]

No one seems to have an answer to that.

Going back through the thread, I'm going to assume that this refers to post 16 (I may have missed a few detours across the various threads) so have no idea if this is what you need or if I need to catch up on what has been going on.

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

It might be easier to follow if you provide us with a copy of your workbook containing just the relevant formulas and data (remove the personal / confidential stuff or use fictional equivalents but keep it accurate) so that we can see exactly how the times are generated by the formulas. You can't upload it directly to the forum, you would need to use a file sharing service then post the link.

 

[joeu2004]

Hi Joeu2004 - I asked the very same question about cell references in the array portion. No one seems to have an answer to that.

But I did. Right? See post #23 in this thread.

If that does not answer your question, please provide a concrete example (data and formulas; it is best to use XL2BB) that fails with my suggestions.