Excel 平均値負の値を表示したい

下記B2～B5セルに、C～Eの平均時間を表示したいです。

1904年から計算するにはチェックが入っています。
マイナスデータは『'-0:**』と入力しています。
E3～G6セルの書式設定は、h:mm:ssです。
D3～D6セルの関数は『=(SUBSTITUTE(TEXT(SUMPRODUCT(SUBSTITUTE(TEXT(E10:U10,"[h]:mm")&"/60",":"," ")+0),"0!:00/60"),"/60",))/C10』が入っています。

以上、よろしくお願い致します。

No.2 ベストアンサー 回答者： grumpy_the_dwarf 回答日時： 2009/06/10 19:22

負の時刻を入力する時は、「'-h:m」じゃなくて「=0-"h;m"」として

下さい。そうすればちゃんと数値になって、普通にaverage関数が使
えます。

この回答へのお礼

セルにマイナス時間を入力する時は、例えば『'-0:20』と直接に入力していました。それを『=0-"0:20"』と入力する方法で、B列の値が正確に表示されました。
以前『=0-"0:20"』と入力する方法も試してみたのですが、打つ文字数は増える為、他の方法を探していたら解らなくなってしまいました。

ありがとうございました。

お礼日時：2009/06/11 09:24

No.5 回答者： KURUMITO 回答日時： 2009/06/14 10:52

もともと文字列が入ったセルの処理では数式が長くなることは避けられません。

セルがＥ列からさらに右側に伸びた場合でも対応できる式として次のような式を考えました。ただし、マイナスの時刻となるセルは１行につき一カ所の場合です。
次の式をＢ１セルに入力し、下方にオートフィルドラッグします。
=IF(A1="","",(SUM(C1:E1)-IF(COUNTIF(C1:E1,"-*")=0,0,MID(INDEX(C1:E1,1,MATCH("-*",C1:E1,0)),2,10)))/A1)
Ｅ列からさらに右になる場合にはＥ１をJ1などに変えます。

この回答への補足

ありがとうございます。

出来ました。

>マイナスの時刻となるセルは１行につき一カ所の場合です。
複数のマイナスが対象となったら良いのですが・・・

いろいろと考えて頂きありがとうございました。

補足日時：2009/06/15 08:23

No.4 回答者： KURUMITO 回答日時： 2009/06/13 08:16

C列がマイナスでない場合でも問題がなく使える式を前回に示したのですが。

ところで、Ｃ列ばかりでなくＤ列やＥ列でもマイナスの可能性があるのでしたら、次の式をＢ１セルに入力して下方にオートフィルドラッグすればよいでしょう。
=IF(A1="","",(IF(C1="",0,IF(ISNUMBER(C1),C1,-TIMEVALUE(MID(C1,2,4))))+IF(D1="",0,IF(ISNUMBER(D1),D1,-TIMEVALUE(MID(D1,2,10))))+IF(E1="",0,IF(ISNUMBER(E1),E1,-TIMEVALUE(MID(E1,2,10)))))/A1)

この回答への補足

ありがとうございます。

はい。Ｃ列だけでは無いです。列は増えていきます。
教えて頂いた式で出来ましたが、列が増える毎に追加しなければならないのと、Ｂ列の式が長くなってしまいます。

ＳＵＭのように範囲が指定出来る式には、どんな方法があるのか
ご存知でしたら教えてください。

補足日時：2009/06/14 06:37

No.3 回答者： KURUMITO 回答日時： 2009/06/12 18:07

C列でマイナスの時間を'を付けて入力している場合には文字列になっていますので直接計算には使うことができませんね。

Ｂ2セルには次の式を入力して下方にオートフィルドラッグしてはいかがでしょう。
=IF(A2="","",(D2+E2+IF(LEFT(C2,1)="-",-TIMEVALUE(MID(C2,2,10)),C2))/A2)

この回答への補足

ありがとうございます。出来ました。

でも、この式はC列がマイナスの時だけの式ですよね。

この先データが増え、マイナスがいろいろなセルに
ある場合はどのような式にしたら良いのか教えてください。

よろしくお願い致します。

補足日時：2009/06/13 05:44

No.1 回答者： goo39 回答日時： 2009/06/10 17:27

一例です。

B2=IF(LEFT(C2,1)="-",SUM(D2:E2)-TIMEVALUE(RIGHT(C2,LEN(C2)-1)),SUM(C2:E2))/A2

この回答への補足

ありがとうございます。

しかし、B2セルにはマイナスが表示できませんでした。
どこが駄目なのでしょうか・・・・
(また、いろいろなセルにマイナスが発生します。)

補足日時：2009/06/11 09:33