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.5

KURUMITO

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

補足

ありがとうございます。

出来ました。

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

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

回答No.4

KURUMITO

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)

補足

ありがとうございます。

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

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

回答No.3

KURUMITO

C列でマイナスの時間を'を付けて入力している場合には文字列になっていますので直接計算には使うことができませんね。
Ｂ2セルには次の式を入力して下方にオートフィルドラッグしてはいかがでしょう。
=IF(A2="","",(D2+E2+IF(LEFT(C2,1)="-",-TIMEVALUE(MID(C2,2,10)),C2))/A2)

補足

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

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

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

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

回答No.2

grumpy_the_dwarf

負の時刻を入力する時は、「'-h:m」じゃなくて「=0-"h;m"」として
下さい。そうすればちゃんと数値になって、普通にaverage関数が使
えます。

お礼

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

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

回答No.1

goo39

一例です。

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

補足

ありがとうございます。

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