ＡＶＥＲＡＧＥ（ＩＦ

質問

EXCEL2003ですが、２行目にB列からK列までに数値が入っています。
その中で100以下の数値の平均をL2に表示させるべく、下記式をL2に貼り付けましたが、＃VALUE!の表示です。
=AVERAGE(IF(B2:K2<101,B2:K2,""))
下記式でも同様でした。
=AVERAGE(IF(B2:K2<101,B2:K2))
加減乗除は分かるのですが、IF関数は良く分かりません。
どうぞ宜しくご教示下さい。

回答No.5

KURUMITO · ベストアンサー

解答Ｎｏ4です。
二つの方法ともＢ2からＫ2セルの間に100以下の数値が入力されていなければ＃DIV/0!の表示が出ます。次のように式をしてください。
=IF(COUNTIF(B2:K2,"<=100")=0,"",SUMIF(B2:K2,"<=100",B2:K2)/COUNTIF(B2:K2,"<=100"))
または次の式を入力して、その後にCtrl＋Shiftを押しながらEnterキーで確定します。
=IF(COUNTIF(B2:K2,"<=100")=0,"",AVERAGE(IF((B2:K2<=100)*(B2:K2<>""),B2:K2,"")))

お礼

有難う御座いました。
お蔭様で無事解決致しました。このような単純な計算でも、色々考慮すべき事項があるのですね。
どうもお世話になりました。

回答No.4

KURUMITO

解答Ｎｏ3です。
=SUMIF(B2:K2,"<=100",B2:K2)/COUNT(B2:K2)
の式ですが101以上の数値を平均に加えないのでしたら次の式になりますね。
=SUMIF(B2:K2,"<=100",B2:K2)/COUNTIF(B2:K2,"<=100")
なお、配列関数を使っての式ですがつぎのような式にして空白のセルを数えないようにします。その後にCtrl＋Shiftを押しながらEnterキーで確定します。
=AVERAGE(IF((B2:K2<=100)*(B2:K2<>""),B2:K2,""))

回答No.3

KURUMITO

#VALUE!の表示は引数の種類が正しくない場合に表示されるものでお示しのようなケースでは考えられないのですが。
次の式を入力してはどうでしょう。
=SUMIF(B2:K2,"<=100",B2:K2)/COUNT(B2:K2)
この式はセルに入力された数値の平均を求めるもので空白のセルは考慮されていません。

補足

有難う御座いました。
入力しましたが、条件に合う100以下の数値合計を全データ数10で割った結果が出てるようです。良く分かりませんがCOUNTの100以下の条件付けがされて無いようです。
=AVERAGE(IF(B2:K2<101,B2:K2))を｛、｝で囲っても駄目なのでしょうか？試しましたが不可でした。
実際のデータ表でやってみましたが、今度は＃DIV/0!のエラー表示です。確定する前に#VALUE!の表示でしたが、shift,ctrl,enterで
＃DIV/0!のエラー表示です。念のため書式が文字列では無く数値である確認はしました。

回答No.2

kybo

式自体はほぼあってます。確定の仕方が間違っているだけで。
100以下は「<=100」なので

=AVERAGE(IF(B2:K2<=100,B2:K2,""))

とし、配列数式になるので、CtrlキーとShiftキーを押しながらEnterキーで確定します。（普通にEnterしただけでは駄目）

空白を０としてみなさない場合は、

=AVERAGE(IF((B2:K2<=100)*(B2:K2<>""),B2:K2,""))

として、Ctrl+Shift+Enterで確定します。

補足

有難う御座いました。
配列数式の意味が分かりませんでした。
現状からF2を押し、Ctrlキー、Shift、Enterで数値が出ました。
L2をみると、｛｝で囲ってありますが、これが配列数式が演算式として確定されたという事でしょうか？

回答No.1

KURUMITO

式そのものは間違っていませんが式を貼り付けたとの表現が気になりますね。お示しの式を入力してもエラー表示がされますか？

お礼

有難う御座いました。
式の確定法が間違ってました。