EXCELで予測値を導き出す方法(複合バージョン)

質問

複雑になってしまいますが、みなさま、よろしくお願いします。

A列1～9行
50
70
30
60
10
90
50
60
20

A列10行
75もしくは76

以上のような数値とします。
(列はアルファベット、行は数字)

B10
[式]
=AVERAGE(A1:A10)

C10
[式]
=STDEVP(A1:A10)

D10
=B10+C10

と式を入れ、
A列10行に「７５」を入れますと、D10に表示される数字は75.38です。
A列10行に「７６」を入れますと、D10に表示される数字は75.58です。

つまり、７６で、はじめてD10の数字を超えます。

現在のやり方ですと、少しずつ数字を増減させて数字を導き出しているのですが、
このはじめて超える数字、この場合だと76を一発で出す関数式はどのようにすればいいのでしょうか？

○参考までに
昨日、同じような質問をしました。
http://oshiete1.goo.ne.jp/qa3270906.html

前回は、A列10行が空欄で数値が出るのですが、
今回は、A列10行に仮想的に数値を入れ、B10を出しておかないとD10が導き出せないことに気づきました。

B11
=ROUNDUP(AVERAGE(A1:A9),0)

C11
=ROUNDUP(STDEVP(A1:A9),0)

D11
=B11+C11

という感じに作ってみたのですが、B11の時点で49(B10では51.6)となり、最終のD列が一致しませんでした。

回答No.2

cafe_au_lait · ベストアンサー

No.1さんのおっしゃるように反復計算にチェックを入れた上で、
A10:=ROUNDUP(AVERAGE(A1:A10)+STDEVP(A1:A10),0)

あるいは、ソルバーを使ってみるのもいいかもしれません。

目的セル：$A$10
目標値：最小値
変化させるセル：$A$10
制約条件：
$A$10 = 整数
$A$10 >= $D$10

※アドインを追加する必要があります。

参考URL：

http://www.moug.net/skillup/fclm/fclm06-01.htm

お礼

アドバイスのおかげで無事解決に至りました。
参考サイトもすごくためになりました＾＾
ご回答ありがとうございました。

回答No.1

mshr1962

A10=SUMPRODUCT(MAX(ROW(A1:A100)*(ROW(A1:A100)<D10)))+1
としてください。
※循環参照になるので「ツール」「オプション」「計算方法」にて
□反復計算にチェックする必要があります。

補足

式を試した見たところ、D10を参照しておりますので、私の探している式とは異なります。

私の説明不足のためで申し訳ありません。
正確には、A列10行が空欄の状態で、今回のケースでいう７６を導き出したいのです。

すばやいご回答に感謝はしております。