ハンディキャップの計算を自動で

質問

恐れ入ります。ゴルフのハンディキャップの計算を自動でできないか考えています。
下記のように数字が１０個あります。
自動で数の少ない順番で５つを選択して、１つのセルにその計算結果だけど表示させたいです。
計算方法は、
{{(78+79+79+79+82)/5}-72}*0.96 そして、-72　した後に四捨五入
というようにしたいですが、このような複雑な式を１つのセルに作ることは可能でしょうか。

B2 87
B4 97
B6 79
B8 78
B10 79
B12 89
B14 82
B16 83
B18 79
B20 85

●で数の少ない順番で５つ
B8 78
B6 79
B10 79
B18 79
B14 82

以下の計算になるはずなのですが。
(1)78+79+79+79+82)/5　　→79.4
(2)79.4*0.96　　　　　　　→76.2
(3)76.2-72　　　　　　　　→4.2
(4)四捨五入　　　　　　　 →4

もし、上記以外でゴルフの標準に更に近い計算方法を知っている方がいらっしゃいましたら是非教えて頂ければ幸いです。

めぐみ

回答No.3

mt2015 · ベストアンサー

ANo.2です。
計算の結果マイナスの値になった時に0にしたいならこんな感じで0か計算結果の大きい方を表示します。
=MAX(0,ROUND(AVERAGE(SMALL(B2:B20,{1,2,3,4,5}))*0.96-72,0))

お礼

mt2015さま

ご回答頂きましてありがとうございました。
完璧でした。
しかもこんなに短い式で。
本当に恐れ入りました。
この度は適切に素早く素晴らしいご回答を頂きまして本当にありがとうございました！
めぐみ

回答No.5

asciiz

＞ハンディキャップに0以下が存在しないので、
＞-1とか-2というマイナスの数字が出た場合は、すべて0としたいのです。

IF関数を使うと条件文と計算値で同じものを書かなきゃいけないから長くなる…
それだったら書式設定でゼロに見せかけるか…? などと考えていましたが、
mt2015さんの回答No.3で完璧ですね^^; 私もまだまだです。

回答No.4

tsubu-yuki

私はゴルフには明るくないのですが・・

> 上記以外でゴルフの標準に更に近い計算方法を知っている方
ゴルフのカテゴリに行った方が詳しい人が多いと思うのですが、
日本ゴルフ協会（公式ハンディキャップ）
http://www.jga.or.jp/jga/html/handicap/about.html
によると、もっと複雑な計算をするようです。

その他、クラブ・コースにおけるハンデ、
コンペなどで用いる略式ハンデ、
などなどいろいろとあるらしいですが
どこに「標準」を置けばよろしいですか？

回答No.2

mt2015

こんな感じにしてみました
=ROUND(AVERAGE(SMALL(B2:B20,{1,2,3,4,5}))*0.96-72,0)

↓コレで下位5つの平均（＝79.4）が出せます
AVERAGE(SMALL(B2:B20,{1,2,3,4,5}))

この結果に0.96をかけて-72したものをRound関数で小数点以下0桁で四捨五入しています。

回答No.1

asciiz

ご質問の表(?)の数値がくっついてしまっていてよくわからないのですが…

B2セルに87
B4セルに97
(以下同様)って感じなんでしょうか。

まあそうだとして、「n番目に小さいもの」を取り出す、『SMALL関数』があります。
=SMALL(範囲, 順番) →指定範囲の中で、(順番)番目に小さい数を取り出す。

なので、範囲がB2:B20だとすると、その範囲の中から小さいもの5つを取り出すのは、

=SMALL(B2:B20,5)+SMALL(B2:B20,4)+SMALL(B2:B20,3)+SMALL(B2:B20,2)+MIN(B2:B20)
(質問の表から計算すると397)

となりますね。※SMALL(範囲, 1) の代わりに、範囲の中で一番小さい値を返す MIN関数にしました

5で割って、72引いて、0.96かけてはそのまま。

=((SMALL(B2:B20,5)+SMALL(B2:B20,4)+SMALL(B2:B20,3)+SMALL(B2:B20,2)+MIN(B2:B20))/5-72)*0.96
(質問の表から計算すると7.104)

四捨五入はROUND関数、=ROUND(値, 小数点桁数)。整数に丸めるなら小数点桁数は0です。

=ROUND(((SMALL(B2:B20,5)+SMALL(B2:B20,4)+SMALL(B2:B20,3)+SMALL(B2:B20,2)+MIN(B2:B20))/5-72)*0.96,0)
(質問の表から計算すると7)

あれ?
ご質問内の一行式だと72引いてから0.96かけてますが、ご質問の文章からすると0.96かけてから72引くんでしょうか。
だとすると、

5で割って、0.96かけて、72を引く。

=(SMALL(B2:B20,5)+SMALL(B2:B20,4)+SMALL(B2:B20,3)+SMALL(B2:B20,2)+MIN(B2:B20))/5*0.96-72
(質問の表から計算すると4.224)

そして四捨五入。

=ROUND((SMALL(B2:B20,5)+SMALL(B2:B20,4)+SMALL(B2:B20,3)+SMALL(B2:B20,2)+MIN(B2:B20))/5*0.96-72,0)
(質問の表から計算すると4)

ゴルフしないのでどっちが正しいのかよくわからないのですが(汗
一行で書けましたので、セル一個で計算できます。

補足

ASCIIZさま

大変失礼しました。
ご指摘の通り２つの異なる要求していました。
こちらから質問させていただいているのに大変失礼いたしました。
１つ１つ丁寧に教えてくださり、また、２つの式まで書いていただきまして本当にありがとうございました！
こんな短時間にこれだけのことができるなんて尊敬します！
----------------------------------------------
=ROUND(((SMALL(B2:B20,5)+SMALL(B2:B20,4)+SMALL(B2:B20,3)+SMALL(B2:B20,2)+MIN(B2:B20))/5-72)*0.96,0)
(質問の表から計算すると7)
----------------------------------------------

それで、ひとつ大事なことを記載するのを忘れていました。
ハンディキャップに0以下が存在しないので、-1とか-2というマイナスの数字が出た場合は、すべて0としたいのです。
以下の数字使用するとご指導頂きました式を活用すると-1と出てしまいますが、マイナスが出る場合はすべて0にするにはどのようにしたらよいでしょか？

B2,77
B4,97
B6,73
B8,72
B10,75
B12,79
B14,71
B16,71
B18,71
B20,70

何度も大変申し訳ありません。
ただ、素早い対応をいただきまして心より感謝しております。
何卒よろしくお願いいたします。

めぐみ