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:


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

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!