Excelで平均を出す方法を教えてください。

質問

あるオンラインゲームでのダメージ期待値計算をしたいのですが、平均の出し方がわかりません。
最大ダメージがB2に最小ダメージがB3に入っていて、この二つの平均を出したいのです。
=AVERAGE(B2:B3)
と入れたいのですが、ダメージにはカンスト値があって、999,999でストップしてしまうのです。
B2に1,200,000　B3に800,000と入っていた場合　普通に計算すれば1,000,000になるのですが
カンスト値があるので999,999よりも高いダメージはすべて999,999になるのです
でも、800,000～999,999では間違っていますよね
どのようにして期待値を出せばいいのでしょうか
カテゴリが間違っていたらすみません

回答No.2

keithin · ベストアンサー

＞平均の出し方がわかりません。

添付図：
単に算数の問題です。

MAX値がリミットより上か下かで計算式が分かれますが，とりあえずご質問の状況に関して言えば
例えば：
=((B2+B3)*(B2-B3)/2-(B2-999999)*(B2-999999)/2)/(B2-B3)
といった具合に「台形（五角形？）の面積を求めて幅で割る」といった具合の計算になります。

お礼

ありがとうございます。
なるほど、台形　よくわかりました
一番わかりやすかったのでベストアンサーに選ばせていただきます

回答No.5

某HN クロメート(Chromate)（@CoalTar）

出したいのは期待値であって単純な平均ではないですよね
制限があるのでデータとして有効な値は999,999未満となります。
確率統計論において最大・最小値で平均を得るなんて誤差が大きすぎます。
そのような簡易な手法もあるのですが、このように制限がある場合、無意味です。

そこで、有効なデータである999,999未満のデータを使い、近似直線を求めます。
その直線から平均となるであろう値、999,999以上を予測した値を含めた平均、つまり、期待値を求めます。
もちろん999,999未満のデータが少なければ誤差も大きくなります。

サンプルデータとして
A1セルに 800000 として基準となる値(切片)を入れました
A2セル =A$1+INT(RAND()*600000) A41セルまで下へオートフィル
データ数を40個と仮定しました
つまり、正解の期待値は800000+600000/2で1,100,000近辺

B2セルには実際に得られるデータとして
=IF(A2>=999999,999999,A2)
B41まで下へオートフィル
このデータを使って1,100,000近辺になるような計算をします

C2セルに有効なデータのみを表示させるように
=IF(B2>=999999,"",B2)
下へオートフィル

D2セルにX値として
=IF(C2="","",RANK(C2,$C$2:$C$41,1))
(重複を考えるとRANK関数は好ましくないですね(^^;)

そこで近似直線の傾き =SLOPE(C2:C41,D2:D41)
切片 =INTERCEPT(C2:C41,D2:D41)

直線においてX=1からX=40の中間のY値が期待値、
つまり、X=20.5の時のYの値が期待値となる

=SLOPE(C2:C41,D2:D41)*20.5+INTERCEPT(C2:C41,D2:D41)
でできそう。(40個のデータの場合)

ところで、カンスト値ってなんですか？

お礼

カンスト値というのはダメージが計算上1,200,000だとしても999,999までしか出ない限界値のことです
わざわざ画像まで用意していただいて、ありがとうございます。
少し難解な部分もありましたがよくわかりました。

回答No.4

imogasi

お礼欄を読んでもまだよく質問内容がわからないが
Ａ２：Ａ６で
1
2
4
5
8
とあると、３を限度として
１
２
３
３
３
３
の平均を出したいのか
=AVERAGE(IF(A2:A6<=3,A2:A6,3))ト入れて
Ｓｈｉｆｔ，ＣＴＲＬ、ＥＮＴＥＲの３つのキーを
同時押しする。配列数式。
結果
２．４(＝(1+2+3+3+3)/5)
---
SUMPRODUCTなら
=SUMPRODUCT(((A2:A6>3)*3)+(A2:A6<=3)*(A2:A6))/COUNT(A2:A6)

お礼

私は勉強不足なため、配列数式というのがよくわかりませんが、最小値と最大値の間のすべての数字の平均を出したいのです
ありがとうございました

回答No.3

MackyNo1

＞少し数字を小さくして例えると3を限界値とした場合の1～5の平均だとすると1,2,3,3,3の平均を出したいということです。

平均したい数値がA1:A5セルに入力されているなら、以下の式を入力してCtrl＋Shift+Enterで確定してください。

=AVERAGE(IF(A1:A5>3,3,A1:A5))

お礼

ありがとうございます
平均を出したい数値はB2とB3の間の数字全てなのです…

回答No.1

mt2008

意味を取り違えているかもしれませんが、B2とB3の平均が999999以下ならそのまま表示、それ以上なら999999と表示する……と、言う理解でよろしいでしょうか？
それでしたら

=MIN(999999,AVERAGE(B2,B3))

お礼

ありがとうございます
私に物事を伝える才能がないので、意味を取り違えさせてしまいました。すみません。
少し数字を小さくして例えると
3を限界値とした場合の1～5の平均だとすると
1,2,3,3,3の平均を出したいということです。