This question's explanation and your answers so far.
支店ごとの月別の売上データを評価する。各月の各支店の“評価”欄に，該当支店の売上額がA～C支店の該当月の売上額の平均値を下回る場合に文字“×”を，平均値以上であれば文字“○”を表示したい。セルC3に入力する式として，適切なものはどれか。ここで，セルC3に入力した式は，セルD3，セルE3，セルC5～E5，セルC7～E7に複写して利用するものとする。 単位 百万円
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 月 | 項目 | A支店 | B支店 | C支店 |
| 2 | 7月 | 売上額 | 1,500 | 1,000 | 3,000 |
| 3 | 評価 | ||||
| 4 | 8月 | 売上額 | 1,200 | 1,000 | 1,000 |
| 5 | 評価 | ||||
| 6 | 9月 | 売上額 | 1,700 | 1,500 | 1,300 |
| 7 | 評価 |
The comparison value is $C2, and because the column is fixed, even when copied to D3 and E3 it keeps pointing to column C of Branch A and does not follow each branch. In addition, the average range C2：E2 is a relative reference, so copying rightward shifts it to D2：F2 and it does not give the average of the three branches for that month. Furthermore, the IF results are in the order “○”“×”, so it outputs “○” when the value is below the average, which is also the reverse of the condition.
The order “×”“○” itself matches the condition, but the way the references are set is wrong. The comparison value $C2 has its column fixed and does not follow each branch, and the average range C2：E2 is a relative reference, so its column shifts when copied rightward and it cannot correctly reference the average of the three branches for that month.
The way the references are set (the comparison value C2 is relative, and the average range $C2：$E2 has its column fixed) is correct, but the IF results are in the order “○”“×”. When C2 is below the average (the condition holds), it returns “○”, which is the reverse of the requirement that being below gives “×”.
正解 Cell C3 is the evaluation cell for Branch A in July. The sales amount to compare is C2 directly above it (Branch A, July), and the comparison baseline is the average of the three branches for that month (C2 to E2). The formula is copied rightward to D3 and E3, and downward to C5 to E5 and C7 to E7. The comparison value C2 must follow the sales of each branch and each month, so both its column and row stay as relative references (C3 to D3 becomes D2, C3 to C5 becomes C4). On the other hand, the average range must always take the three branches in columns C to E of that month no matter where it is copied, so only the column is made an absolute reference with $, written as $C2：$E2 (copying rightward does not shift the column, and copying downward moves only the row down so it points to the three branches of that month). When the condition C2＜average holds, meaning it is below the average, it returns “×”; when it does not hold, meaning it is at or above the average, it returns “○”. Therefore it is IF(C2＜AVERAGE($C2：$E2)，“×”，“○”), so エ is appropriate.