スキルアップ・問題解決なら、モーグにおまかせ！

Ｂ列開始時間とＣ列終了時間を使って
Ａ列スタッフの重複時間を抽出し、平均と最大値を出しています。
(E2:L9)は
1番目は2番目以降すべてと比べて被り時間を出し、
2番目は3番目以降すべてと比べて被り時間を出し、とその後すべてと比べています。
E2=MAX(0,IF(MIN($C$2,$C3)-MAX($B$2,$B3)=0,1/86400,MIN($C$2,$C3)-MAX($B$2,$B3)))
E3=MAX(0,IF(MIN($C$2,$C4)-MAX($B$2,$B4)=0,1/86400,MIN($C$2,$C4)-MAX($B$2,$B4)))
E4=MAX(0,IF(MIN($C$2,$C5)-MAX($B$2,$B5)=0,1/86400,MIN($C$2,$C5)-MAX($B$2,$B5)))
.....
F3=MAX(0,IF(MIN($C$3,$C4)-MAX($B$3,$B4)=0,1/86400,MIN($C$3,$C4)-MAX($B$3,$B4)))
F4=MAX(0,IF(MIN($C$3,$C5)-MAX($B$3,$B5)=0,1/86400,MIN($C$3,$C5)-MAX($B$3,$B5)))
.....
平均=AVERAGEIF(E2:L9,"<>0",E2:L9)
最大=MAX(E2:L9)
 
と計算しています。
(E2:L9)のように、一つ一つ比べないと抽出はできないものなのでしょうか？
もっと一度に計算できる方法（SUMPRODUCTなど使って）がわかる方、何卒ご教示よろしくお願いします。
※同じ時間に開始と終了が重複した場合は1秒とします。
 
A　B C D E F G H I J K L
　開始時間 終了時間　　1との重複　2と重複　3と重複　4と重複　5と重複　6と重複　7と重複　8と重複
1　0:01:00　0:02:30　2　0:00:20                            
2　0:01:10　0:01:30　3　0:00:30　0:00:00                        
3　0:02:00　0:02:40　4　0:00:50　0:00:00　0:00:40                    
4　0:01:40　0:02:40　5　0:01:10　0:00:20　0:00:10　0:00:30                
5　0:00:10　0:02:10　6　0:00:50　0:00:10　0:00:10　0:00:30　0:00:50            
6　0:01:20　0:02:10　7　0:00:20　0:00:10　0:00:00　0:00:00　0:00:20　0:00:01        
7　0:01:00　0:01:20　8　0:00:00　0:00:00　0:00:00　0:00:00　0:00:00　0:00:00　0:00:00    
8　0:01:50　0:01:30　9　0:00:20　0:00:01　0:00:00　0:00:10　0:00:20　0:00:20　0:00:00　0:00:00
9　0:01:30　0:01:50　　　平均    最大                        
            0:00:25    0:01:10                        

平均は0:00:15ですよ。
 
=LET(x,B2:C10,a,TAKE(x,,1),b,TAKE(x,,-1),c,TOROW(a),d,TOROW(b),i,SEQUENCE(ROWS(a)),j,TRANSPOSE(i),e,IF(i>j,IFERROR(1*TEXT(IF(d<b,d,b)-IF(c>a,c,a),"標準;0;"),"0:00:01"*1),""),VSTACK({"平均","最大"},HSTACK(AVERAGE(e),MAX(e))))

現在のように重複時間を一覧で表示したいときは
E2
=LET(x,B2:C10,a,TAKE(x,,1),b,TAKE(x,,-1),c,TOROW(a),d,TOROW(b),i,SEQUENCE(ROWS(a)),j,TRANSPOSE(i),IF(i>j,IFERROR(1*TEXT(IF(d<b,d,b)-IF(c>a,c,a),"標準;;"),"0:00:01"*1),""))
 
どこかのセルに
=AVERAGE(E2#)
=MAX(E2#)

んなっとさん！ありがとうございます。
E2に入力したら、#NAME?がでます。
計算範囲はあっていますが、何がおかしいのでしょうか？？？

新関数が使えない場合、
　ファイル
→アカウント
→Microsoft 365 Insider
→Microsoft 365 Insiderに参加
→新規登録してMicrosoft 365の新しいリリースをいち早く入手します　にチェック
→Microsoft 365 Insiderを選択してください：最新チャネル(プレビュー版)
→このプログラムへの参加を〜　にチェック
→OK
といった設定が必要かもしれません。
 
0:00:00を除いた平均なんですね。0:00:25で正しいようです。よく読んでいませんでした。
新関数が使える設定にしてから以下のように変更。
=LET(x,B2:C10,a,TAKE(x,,1),b,TAKE(x,,-1),c,TOROW(a),d,TOROW(b),i,SEQUENCE(ROWS(a)),j,TOROW(i),
e,TOROW(IF(i>j,IFERROR(1*TEXT(IF(d<b,d,b)-IF(c>a,c,a),"標準;0;"),"0:00:01"*1),"")),
f,FILTER(e,e<>0),VSTACK({"平均","最大"},HSTACK(SUM(f)/COUNT(f),MAX(f))))
 
 
※一覧にして確認したい場合
E2
=LET(x,B2:C10,a,TAKE(x,,1),b,TAKE(x,,-1),c,TOROW(a),d,TOROW(b),i,SEQUENCE(ROWS(a)),j,TOROW(i),IF(i>j,IFERROR(1*TEXT(IF(d<b,d,b)-IF(c>a,c,a),"標準;0;"),"0:00:01"*1),""))
 
　　　　　 B　　　　 C　D　　　　E　　　　F　　　　G
 1　開始時間　終了時間　　　　　　　　　　　　　　　
 2　 0:01:00　 0:02:30　　　　　　　　　　　　　　　
 3　 0:01:10　 0:01:30　　 0:00:20　　　　　　　　　
 4　 0:02:00　 0:02:40　　 0:00:30　0:00:00　　　　　
 5　 0:01:40　 0:02:40　　 0:00:50　0:00:00　0:00:40
 6　 0:00:10　 0:02:10　　 0:01:10　0:00:20　0:00:10
 7　 0:01:20　 0:02:10　　 0:00:50　0:00:10　0:00:10
 8　 0:01:00　 0:01:20　　 0:00:20　0:00:10　0:00:00
 9　 0:01:50　 0:01:30　　 0:00:00　0:00:00　0:00:00
10　 0:01:30　 0:01:50　　 0:00:20　0:00:01　0:00:00
11　　　　　　　　　　　　　　　　　　　　　　　　　
12　　　　　　　　　　　　　　　　　　　　　　　　　
13　　　　　　　　　　　　　　　　　　　　　　　　　
14　　　　　　　　　　　　　　　　　　　　　　　　　
15　　　　　　　　　　　　　　平均　　 最大　　　　　
16　　　　　　　　　　　　 0:00:25　0:01:10　　　　　
 
平均
=AVERAGEIF(E2#,"<>0",E2#)
最大
=MAX(E2#)

最初の式これでよかった。
 
=LET(x,B2:C10,a,TAKE(x,,1),b,TAKE(x,,-1),c,TOROW(a),d,TOROW(b),i,SEQUENCE(ROWS(a)),j,TOROW(i),
e,TOROW(IF(i>j,IFERROR(1*TEXT(IF(d<b,d,b)-IF(c>a,c,a),"標準;0;"),"0:00:01"*1),"")),
f,FILTER(e,e<>0),VSTACK({"平均","最大"},HSTACK(AVERAGE(f),MAX(f))))

んなっとさん！ありがとうございます。
Microsoft 365
で入力したら、今度は、#スピル！　と出ました。
この式をE2からE8までコピーしたらいいのでしょうか？
なんとかやってみます。
 
ありがとうございました。

見たこともないような関数。勉強不足でお恥ずかしい。
大変勉強になりました。
ありがとうございました。