Days on the Market - Conditional Average Days Between 2 Dates

[hawaean]

Hi Excel Fam,

Here's the challenge I'm stuck on. I have a list of products with a start date and an end date [when available]. I'd like to calculate the average days between the two days for the different categories. When the item is still on the shelf, the end date should be today().

How can I get the average days on the market for:
1. Can Goods
2. Produce

I'd like to avoid any helper columns if possible.

Book1
	A	B	C	D
1		Shelf Date	Sale Date	Type
2	Product A	13-Jan	3-Feb	Can Good
3	Product B	2-May	4-Jun	Can Good
4	Product C	26-Apr	21-May	Can Good
5	Product D	2-Nov	8-Jan	Can Good
6	Product E	15-Mar	30-Mar	Produce
7	Product F	1-Jun		Can Good
8	Product G	25-Jan	2-Feb	Produce
9	Product H	7-Feb	12-Feb	Produce
10	Product I	6-Apr	13-May	Can Good
11	Product J	5-Jan	24-Mar	Can Good
12	Product K	18-Apr	1-Jun	Can Good
13	Product L	6-Jun		Produce
14	Product M	21-Feb	3-Mar	Produce
15	Product N	30-May	6-Jun	Produce
16	Product O	18-Jan	8-Mar	Can Good
Sheet1

 

[AhoyNC]

Try:
This is an array formula and needs to be entered with CTRL-SHIFT-ENTER unless you are using Excel 365. It helps to let us know what version of Excel you are using.

Book1
	A	B	C	D	E	F	G
1		Shelf Date	Sale Date	Type		Type	Average
2	Product A	1/13/21	2/3/21	Can Good		Can Good	40.777778
3	Product B	5/2/21	6/4/21	Can Good		Produce	8.8333333
4	Product C	4/26/21	5/21/21	Can Good
5	Product D	11/2/20	1/8/21	Can Good
6	Product E	3/15/21	3/30/21	Produce
7	Product F	6/1/21		Can Good
8	Product G	1/25/21	2/2/21	Produce
9	Product H	2/7/21	2/12/21	Produce
10	Product I	4/6/21	5/13/21	Can Good
11	Product J	1/5/21	3/24/21	Can Good
12	Product K	4/18/21	6/1/21	Can Good
13	Product L	6/6/21		Produce
14	Product M	2/21/21	3/3/21	Produce
15	Product N	5/30/21	6/6/21	Produce
16	Product O	1/18/21	3/8/21	Can Good
Sheet2

Cell Formulas
Range	Formula
G2:G3	G2	=AVERAGE(IF($D$2:$D$16=F2,IF($C$2:$C$16="",TODAY(),$C$2:$C$16)-$B$2:$B$16))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[hawaean]

Thank you. I use Office 365, so this worked without the array trigger. I appreciate the help!