Average Dates based off of shared values in another column

[RCarillo]

Hi all,

I'm new to the forum and Excel and couldn't find this specific thing on search (or how to word my search), so here goes:

I'm trying to figure out how to compute the average date inspection date of a building based off of the inspection date of each asset in a building for example:

Bldg Number	Mat/Equip	Inspection Date	Average Inspection Date per Building
100	B202001 WINDOWS<strike></strike>	1/2/2018<strike></strike>	<strike></strike>
100	B203001 SOLID DOORS<strike></strike>	<strike></strike>1/2/2018<strike></strike>	<strike></strike>
100	B203004 OVERHEAD AND ROLL-UP DOORS<strike></strike>	<strike></strike>1/5/2017	<strike></strike>
106	D502002 LIGHTING EQUIPMENT<strike></strike>	<strike></strike><strike></strike>8/17/2016<strike></strike>	<strike></strike>
106	D503001 TELECOMMUNICATIONS SYSTEMS<strike></strike>	10/21/2011<strike></strike>	<strike></strike>
etc.	etc.	<strike></strike>etc.		<strike></strike>

<tbody>
</tbody>

My ultimate goal is to sort the buildings in ascending order by the last column.
The original spreadsheet has over 14,000 lines, so I'm look for a way to automate the calculations, if possible.

 

[dave2018]

Put the following formula into D2, but press Ctrl+Shift+Enter when entering the formula.
curly brackets {} should pop up at the edges of the formula bar when entered correctly. You cannot add them manually!!!

=TEXT(AVERAGE(IFERROR(INDEX($A$2:$C$25000,SUBSTITUTE(N(IF(1,IF($A$2:$A$25000=$A2,ROW($A$2:$A$25000)-1,0))),0,""),3),"")),"m/d/yyyy")

Drag that down your data until the end. (or double click the fill handle to send it down).

Does that help?

 

[RCarillo]

Yes, Dave, that exactly what I need. It took me a minute to figure out exactly how to get it work with my actual spreadsheet, but it turned out fine. Thanks for the quick solution!