ITPEC IP October 2020 Question 18

Topic: SoftwareAlso asked in March 2018, April 2022 and October 2025

Spreadsheet software is used to predict sales according to the weather. The table is a worksheet into which the probability for the weather type (sunny, cloudy, rainy) on the day to be predicted and the predicted sales amount by weather type for each product is entered. An expression is entered in cell E4 to calculate the predicted sales amount on the day for Product A, and this is copied to cells E5 through E6 and used. Under these conditions, which of the following is an appropriate expression to be entered in cell E4? Here, to obtain the predicted sales amount on the day for each product, the probability for each weather type is multiplied by the predicted sales amount for each of weather type, and then these values are added up.

ABCDE123456Weather typeSunnyCloudyRainyProbability ofweather type0.50.30.2Product namePredictedsales amountwhen sunnyPredictedsales amountwhen cloudyPredictedsales amountwhen rainyPredictedsales amounton the dayProduct A300,000100,00080,000Product B250,000280,000300,000Product C100,000250,000350,000

Explanation

Row 2 fixed, product row relative — in B$2*B4+C$2*C4+D$2*D4 the probabilities stay put while the product row moves down with each copy.

E4 needs each weather probability (row 2) times Product A's sales for that weather (row 4), added up. When E4 is copied to E5 and E6, the row 2 references must not move, but row 4 must become 5 and then 6. A $ before the row number fixes the row: B$2 stays row 2, while B4 becomes B5 and B6.

  • •E4: 0.5 × 300,000 + 0.3 × 100,000 + 0.2 × 80,000 = 196,000
  • •E5 becomes B$2*B5+C$2*C5+D$2*D5: 0.5 × 250,000 + 0.3 × 280,000 + 0.2 × 300,000 = 269,000

Answer (b)

Why not others:

  • •(a) — nothing is fixed, so in E5 the probability references slide to row 3 (B3*B5), which holds headings, not probabilities
  • •(c) — the $ sits on the wrong parts: $B2 fixes the column, so its row slides to 3, while B$4 pins Product A's row, so every copy multiplies headings by Product A's sales
  • •(d) — fixing every reference makes E5 and E6 copy E4 exactly, showing Product A's 196,000 for all three products

Key rule: When copying down a column, put $ before the row number of the cell that must stay the same (B$2) and leave the rows that should follow the copy relative.

Software · AI-written, may contain errors

Practice this exam

This question comes from an official ITPEC past paper. ITPEC Practice is an independent study tool and is not affiliated with ITPEC. See the official IP past-paper collection or Report an issue.