PUNGGOL TUITION · PRACTICAL MASTERY GUIDE

A wide export can overwhelm a learner who needs only Name, Score and Status. CHOOSECOLS creates a focused view by relative column position, and it can reorder or repeat fields without changing the source table.

CHOOSECOLS uses CHOOSECOLS(array, col_num1, [col_num2], …). Positive positions count from the start, negative positions count from the end, and argument order becomes result order. Zero or an absolute position beyond the source width returns #VALUE!.

This guide treats Excel CHOOSECOLS as a reasoning skill. It begins with the governing model, moves through worked examples and diagnostic routes, and ends with transfer cases, practice with explained answers, parent decisions and FAQs. The examples are proposed home or lesson activities; they are not claims about a physical Punggol branch, school affiliation, timetable, class size, fee or guaranteed result.

For a Punggol family, the practical aim is simple: a learner should be able to use a short study block before schoolwork, after CCA, or during a weekend library session without needing a large project. Each chapter is designed so the smallest useful example can be completed, discussed and saved as a test fixture.

The official technical reference for this article is Official Microsoft Support documentation for CHOOSECOLS. Where the feature is version-sensitive or still developing, the article states that boundary explicitly. Examples were independently checked against the runtime or specification named in the reference section.

Choose a reading route

If the immediate concern is “I can copy the syntax but I cannot explain the result”, use the first route. If a real project is already failing, use the diagnostic route. Parents can use the final route to decide whether more practice, a smaller prerequisite or a different example is needed. For the wider learning framework, see How Studying Works.

Start from the model: Chapters 1–4. Begin with Return one chosen column and continue in order to Repeat a column.

Diagnose a confusing result: Chapters 5–8. Begin with Choose the last column and continue in order to Reject zero.

Build reliable examples: Chapters 9–12. Begin with Reject a position beyond the width and continue in order to Choose columns after filtering rows.

Transfer to real work: Chapters 13–16. Begin with Sort selected fields carefully and continue in order to Use LET to name positions.

Decide and teach: Chapters 17–20. Begin with Predict spill width and continue in order to Choose CHOOSECOLS for arbitrary positions.

Open the full 20-chapter index

1. 1. Return one chosen column
2. 2. Return several columns
3. 3. Reorder columns
4. 4. Repeat a column
5. 5. Choose the last column
6. 6. Choose last columns in reverse order
7. 7. Mix positive and negative positions
8. 8. Reject zero
9. 9. Reject a position beyond the width
10. 10. Preserve all source rows
11. 11. Select fields from a table
12. 12. Choose columns after filtering rows
13. 13. Sort selected fields carefully
14. 14. Choose a column by header name
15. 15. Choose several named headers
16. 16. Use LET to name positions
17. 17. Predict spill width
18. 18. Diagnose a blocked spill
19. 19. Check version compatibility
20. 20. Choose CHOOSECOLS for arbitrary positions

CHAPTER 1 OF 20 · Back to contents

1. Return one chosen column

The short answer: Return one chosen column is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: CHOOSECOLS returns specified relative column positions from an array.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “CHOOSECOLS returns specified relative column positions from an array.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the return one chosen column example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

=CHOOSECOLS({1,2,3;4,5,6},2)

Expected result and reasoning. The result is {2;5}. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. The number is relative to the supplied array, not the worksheet. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls return one chosen column?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for return one chosen column
• Variable to hold constant: the surrounding setup, so the comparison isolates return one chosen column.
• Variable to change: choose column 1
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For return one chosen column, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a CCA attendance export. Transfer return one chosen column into a CCA attendance export. Preserve “CHOOSECOLS returns specified relative column positions from an array.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “CHOOSECOLS returns specified relative column positions from an array.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a library-book record. Transfer return one chosen column into a library-book record. Preserve “CHOOSECOLS returns specified relative column positions from an array.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “CHOOSECOLS returns specified relative column positions from an array.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a school-event roster. Transfer return one chosen column into a school-event roster. Preserve “CHOOSECOLS returns specified relative column positions from an array.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “CHOOSECOLS returns specified relative column positions from an array.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a subject-planning workbook. Transfer return one chosen column into a subject-planning workbook. Preserve “CHOOSECOLS returns specified relative column positions from an array.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “CHOOSECOLS returns specified relative column positions from an array.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for return one chosen column, predict the complete array, then choose column 1. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is {2;5}. This follows from the rule: CHOOSECOLS returns specified relative column positions from an array. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of return one chosen column before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain return one chosen column, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Contents · Full contents · Next: Return several columns →

CHAPTER 2 OF 20 · Back to contents

2. Return several columns

The short answer: Return several columns is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: Additional col_num arguments return several columns in the stated order.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “Additional col_num arguments return several columns in the stated order.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the return several columns example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

=CHOOSECOLS({1,2,3;4,5,6},1,3)

Expected result and reasoning. The result is {1,3;4,6}. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. The function does not automatically include columns between 1 and 3. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls return several columns?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for return several columns
• Variable to hold constant: the surrounding setup, so the comparison isolates return several columns.
• Variable to change: choose 3,1
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For return several columns, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a revision-topic list. Transfer return several columns into a revision-topic list. Preserve “Additional col_num arguments return several columns in the stated order.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Additional col_num arguments return several columns in the stated order.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a household budget sheet. Transfer return several columns into a household budget sheet. Preserve “Additional col_num arguments return several columns in the stated order.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Additional col_num arguments return several columns in the stated order.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a survey response table. Transfer return several columns into a survey response table. Preserve “Additional col_num arguments return several columns in the stated order.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Additional col_num arguments return several columns in the stated order.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a data-cleaning checklist. Transfer return several columns into a data-cleaning checklist. Preserve “Additional col_num arguments return several columns in the stated order.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Additional col_num arguments return several columns in the stated order.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for return several columns, predict the complete array, then choose 3,1. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is {1,3;4,6}. This follows from the rule: Additional col_num arguments return several columns in the stated order. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of return several columns before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain return several columns, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Return one chosen column · Full contents · Next: Reorder columns →

CHAPTER 3 OF 20 · Back to contents

3. Reorder columns

The short answer: Reorder columns is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: Argument order determines result order.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “Argument order determines result order.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the reorder columns example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

=CHOOSECOLS({1,2,3;4,5,6},3,1)

Expected result and reasoning. The result is {3,1;6,4}. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Source order is not preserved when a different order is requested. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls reorder columns?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for reorder columns
• Variable to hold constant: the surrounding setup, so the comparison isolates reorder columns.
• Variable to change: use 2,3,1
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For reorder columns, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a library-book record. Transfer reorder columns into a library-book record. Preserve “Argument order determines result order.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Argument order determines result order.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a school-event roster. Transfer reorder columns into a school-event roster. Preserve “Argument order determines result order.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Argument order determines result order.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a subject-planning workbook. Transfer reorder columns into a subject-planning workbook. Preserve “Argument order determines result order.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Argument order determines result order.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a CCA attendance export. Transfer reorder columns into a CCA attendance export. Preserve “Argument order determines result order.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Argument order determines result order.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for reorder columns, predict the complete array, then use 2,3,1. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is {3,1;6,4}. This follows from the rule: Argument order determines result order. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of reorder columns before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain reorder columns, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Return several columns · Full contents · Next: Repeat a column →

CHAPTER 4 OF 20 · Back to contents

4. Repeat a column

The short answer: Repeat a column is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: The same col_num can be requested more than once.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “The same col_num can be requested more than once.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the repeat a column example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

=CHOOSECOLS({1,2,3;4,5,6},1,3,1)

Expected result and reasoning. The result is {1,3,1;4,6,4}. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Duplicates are not automatically removed. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls repeat a column?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for repeat a column
• Variable to hold constant: the surrounding setup, so the comparison isolates repeat a column.
• Variable to change: repeat column 2
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For repeat a column, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a household budget sheet. Transfer repeat a column into a household budget sheet. Preserve “The same col_num can be requested more than once.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “The same col_num can be requested more than once.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a survey response table. Transfer repeat a column into a survey response table. Preserve “The same col_num can be requested more than once.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “The same col_num can be requested more than once.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a data-cleaning checklist. Transfer repeat a column into a data-cleaning checklist. Preserve “The same col_num can be requested more than once.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “The same col_num can be requested more than once.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a revision-topic list. Transfer repeat a column into a revision-topic list. Preserve “The same col_num can be requested more than once.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “The same col_num can be requested more than once.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for repeat a column, predict the complete array, then repeat column 2. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is {1,3,1;4,6,4}. This follows from the rule: The same col_num can be requested more than once. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of repeat a column before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain repeat a column, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Reorder columns · Full contents · Next: Choose the last column →

CHAPTER 5 OF 20 · Back to contents

5. Choose the last column

The short answer: Choose the last column is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: A negative col_num counts from the end of the array.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “A negative col_num counts from the end of the array.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the choose the last column example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

=CHOOSECOLS({1,2,3;4,5,6},-1)

Expected result and reasoning. The result is {3;6}. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Negative does not reverse values. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls choose the last column?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for choose the last column
• Variable to hold constant: the surrounding setup, so the comparison isolates choose the last column.
• Variable to change: use -2
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For choose the last column, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a school-event roster. Transfer choose the last column into a school-event roster. Preserve “A negative col_num counts from the end of the array.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “A negative col_num counts from the end of the array.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a subject-planning workbook. Transfer choose the last column into a subject-planning workbook. Preserve “A negative col_num counts from the end of the array.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “A negative col_num counts from the end of the array.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a CCA attendance export. Transfer choose the last column into a CCA attendance export. Preserve “A negative col_num counts from the end of the array.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “A negative col_num counts from the end of the array.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a library-book record. Transfer choose the last column into a library-book record. Preserve “A negative col_num counts from the end of the array.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “A negative col_num counts from the end of the array.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for choose the last column, predict the complete array, then use -2. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is {3;6}. This follows from the rule: A negative col_num counts from the end of the array. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of choose the last column before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain choose the last column, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Repeat a column · Full contents · Next: Choose last columns in reverse order →

CHAPTER 6 OF 20 · Back to contents

6. Choose last columns in reverse order

The short answer: Choose last columns in reverse order is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: Multiple negative positions retain the requested order.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “Multiple negative positions retain the requested order.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the choose last columns in reverse order example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

=CHOOSECOLS({1,2,3,4;5,6,7,8},-1,-2)

Expected result and reasoning. The result is {4,3;8,7}. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Last two in source order would require -2,-1. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls choose last columns in reverse order?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for choose last columns in reverse order
• Variable to hold constant: the surrounding setup, so the comparison isolates choose last columns in reverse order.
• Variable to change: swap the arguments
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For choose last columns in reverse order, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a survey response table. Transfer choose last columns in reverse order into a survey response table. Preserve “Multiple negative positions retain the requested order.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Multiple negative positions retain the requested order.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a data-cleaning checklist. Transfer choose last columns in reverse order into a data-cleaning checklist. Preserve “Multiple negative positions retain the requested order.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Multiple negative positions retain the requested order.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a revision-topic list. Transfer choose last columns in reverse order into a revision-topic list. Preserve “Multiple negative positions retain the requested order.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Multiple negative positions retain the requested order.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a household budget sheet. Transfer choose last columns in reverse order into a household budget sheet. Preserve “Multiple negative positions retain the requested order.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Multiple negative positions retain the requested order.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for choose last columns in reverse order, predict the complete array, then swap the arguments. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is {4,3;8,7}. This follows from the rule: Multiple negative positions retain the requested order. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of choose last columns in reverse order before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain choose last columns in reverse order, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Choose the last column · Full contents · Next: Mix positive and negative positions →

CHAPTER 7 OF 20 · Back to contents

7. Mix positive and negative positions

The short answer: Mix positive and negative positions is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: Positive and negative positions may identify columns from opposite edges.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “Positive and negative positions may identify columns from opposite edges.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the mix positive and negative positions example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

=CHOOSECOLS({1,2,3,4;5,6,7,8},1,-1)

Expected result and reasoning. The result is {1,4;5,8}. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. The signs do not sort the columns. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls mix positive and negative positions?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for mix positive and negative positions
• Variable to hold constant: the surrounding setup, so the comparison isolates mix positive and negative positions.
• Variable to change: use -1,1
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For mix positive and negative positions, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a subject-planning workbook. Transfer mix positive and negative positions into a subject-planning workbook. Preserve “Positive and negative positions may identify columns from opposite edges.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Positive and negative positions may identify columns from opposite edges.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a CCA attendance export. Transfer mix positive and negative positions into a CCA attendance export. Preserve “Positive and negative positions may identify columns from opposite edges.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Positive and negative positions may identify columns from opposite edges.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a library-book record. Transfer mix positive and negative positions into a library-book record. Preserve “Positive and negative positions may identify columns from opposite edges.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Positive and negative positions may identify columns from opposite edges.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a school-event roster. Transfer mix positive and negative positions into a school-event roster. Preserve “Positive and negative positions may identify columns from opposite edges.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Positive and negative positions may identify columns from opposite edges.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for mix positive and negative positions, predict the complete array, then use -1,1. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is {1,4;5,8}. This follows from the rule: Positive and negative positions may identify columns from opposite edges. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of mix positive and negative positions before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain mix positive and negative positions, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Choose last columns in reverse order · Full contents · Next: Reject zero →

CHAPTER 8 OF 20 · Back to contents

8. Reject zero

The short answer: Reject zero is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: A col_num with absolute value zero returns #VALUE!.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “A col_num with absolute value zero returns #VALUE!.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the reject zero example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

=CHOOSECOLS({1,2;3,4},0)

Expected result and reasoning. The result is #VALUE!. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Zero is not the first column. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls reject zero?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for reject zero
• Variable to hold constant: the surrounding setup, so the comparison isolates reject zero.
• Variable to change: replace 0 with 1
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For reject zero, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a data-cleaning checklist. Transfer reject zero into a data-cleaning checklist. Preserve “A col_num with absolute value zero returns #VALUE!.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “A col_num with absolute value zero returns #VALUE!.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a revision-topic list. Transfer reject zero into a revision-topic list. Preserve “A col_num with absolute value zero returns #VALUE!.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “A col_num with absolute value zero returns #VALUE!.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a household budget sheet. Transfer reject zero into a household budget sheet. Preserve “A col_num with absolute value zero returns #VALUE!.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “A col_num with absolute value zero returns #VALUE!.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a survey response table. Transfer reject zero into a survey response table. Preserve “A col_num with absolute value zero returns #VALUE!.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “A col_num with absolute value zero returns #VALUE!.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for reject zero, predict the complete array, then replace 0 with 1. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is #VALUE!. This follows from the rule: A col_num with absolute value zero returns #VALUE!. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of reject zero before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain reject zero, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Mix positive and negative positions · Full contents · Next: Reject a position beyond the width →

CHAPTER 9 OF 20 · Back to contents

9. Reject a position beyond the width

The short answer: Reject a position beyond the width is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: A col_num whose absolute value exceeds the source column count returns #VALUE!.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “A col_num whose absolute value exceeds the source column count returns #VALUE!.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the reject a position beyond the width example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

=CHOOSECOLS({1,2,3;4,5,6},4)

Expected result and reasoning. Because the source has only three columns, requesting position 4 returns #VALUE!. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Worksheet column 4 is irrelevant to a three-column array. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls reject a position beyond the width?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for reject a position beyond the width
• Variable to hold constant: the surrounding setup, so the comparison isolates reject a position beyond the width.
• Variable to change: use 3
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For reject a position beyond the width, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a CCA attendance export. Transfer reject a position beyond the width into a CCA attendance export. Preserve “A col_num whose absolute value exceeds the source column count returns #VALUE!.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “A col_num whose absolute value exceeds the source column count returns #VALUE!.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a library-book record. Transfer reject a position beyond the width into a library-book record. Preserve “A col_num whose absolute value exceeds the source column count returns #VALUE!.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “A col_num whose absolute value exceeds the source column count returns #VALUE!.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a school-event roster. Transfer reject a position beyond the width into a school-event roster. Preserve “A col_num whose absolute value exceeds the source column count returns #VALUE!.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “A col_num whose absolute value exceeds the source column count returns #VALUE!.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a subject-planning workbook. Transfer reject a position beyond the width into a subject-planning workbook. Preserve “A col_num whose absolute value exceeds the source column count returns #VALUE!.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “A col_num whose absolute value exceeds the source column count returns #VALUE!.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for reject a position beyond the width, predict the complete array, then use 3. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. Because the source has only three columns, requesting position 4 returns #VALUE!. This follows from the rule: A col_num whose absolute value exceeds the source column count returns #VALUE!. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of reject a position beyond the width before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain reject a position beyond the width, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Reject zero · Full contents · Next: Preserve all source rows →

CHAPTER 10 OF 20 · Back to contents

10. Preserve all source rows

The short answer: Preserve all source rows is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: CHOOSECOLS selects columns and retains the array’s row count.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “CHOOSECOLS selects columns and retains the array’s row count.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the preserve all source rows example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:E10 has 9 rows
=CHOOSECOLS(A2:E10,1,5)

Expected result and reasoning. The result has 9 rows and 2 columns. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Expecting two rows confuses column count with row count. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls preserve all source rows?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for preserve all source rows
• Variable to hold constant: the surrounding setup, so the comparison isolates preserve all source rows.
• Variable to change: choose three columns
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For preserve all source rows, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a revision-topic list. Transfer preserve all source rows into a revision-topic list. Preserve “CHOOSECOLS selects columns and retains the array’s row count.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “CHOOSECOLS selects columns and retains the array’s row count.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a household budget sheet. Transfer preserve all source rows into a household budget sheet. Preserve “CHOOSECOLS selects columns and retains the array’s row count.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “CHOOSECOLS selects columns and retains the array’s row count.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a survey response table. Transfer preserve all source rows into a survey response table. Preserve “CHOOSECOLS selects columns and retains the array’s row count.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “CHOOSECOLS selects columns and retains the array’s row count.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a data-cleaning checklist. Transfer preserve all source rows into a data-cleaning checklist. Preserve “CHOOSECOLS selects columns and retains the array’s row count.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “CHOOSECOLS selects columns and retains the array’s row count.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for preserve all source rows, predict the complete array, then choose three columns. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result has 9 rows and 2 columns. This follows from the rule: CHOOSECOLS selects columns and retains the array’s row count. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of preserve all source rows before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain preserve all source rows, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Reject a position beyond the width · Full contents · Next: Select fields from a table →

CHAPTER 11 OF 20 · Back to contents

11. Select fields from a table

The short answer: Select fields from a table is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: Structured references can supply an automatically resizing source array.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “Structured references can supply an automatically resizing source array.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the select fields from a table example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

=CHOOSECOLS(Table1,1,3)

Expected result and reasoning. The first and third Table1 columns spill outside the table. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. A spilling formula should not be placed inside the table body. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls select fields from a table?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for select fields from a table
• Variable to hold constant: the surrounding setup, so the comparison isolates select fields from a table.
• Variable to change: add a table row
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For select fields from a table, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a library-book record. Transfer select fields from a table into a library-book record. Preserve “Structured references can supply an automatically resizing source array.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Structured references can supply an automatically resizing source array.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a school-event roster. Transfer select fields from a table into a school-event roster. Preserve “Structured references can supply an automatically resizing source array.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Structured references can supply an automatically resizing source array.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a subject-planning workbook. Transfer select fields from a table into a subject-planning workbook. Preserve “Structured references can supply an automatically resizing source array.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Structured references can supply an automatically resizing source array.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a CCA attendance export. Transfer select fields from a table into a CCA attendance export. Preserve “Structured references can supply an automatically resizing source array.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Structured references can supply an automatically resizing source array.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for select fields from a table, predict the complete array, then add a table row. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The first and third Table1 columns spill outside the table. This follows from the rule: Structured references can supply an automatically resizing source array. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of select fields from a table before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain select fields from a table, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Preserve all source rows · Full contents · Next: Choose columns after filtering rows →

CHAPTER 12 OF 20 · Back to contents

12. Choose columns after filtering rows

The short answer: Choose columns after filtering rows is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: FILTER can select records first and CHOOSECOLS can then select fields.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “FILTER can select records first and CHOOSECOLS can then select fields.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the choose columns after filtering rows example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

=CHOOSECOLS(FILTER(A2:D20,D2:D20="Open"),1,3)

Expected result and reasoning. Columns 1 and 3 of Open records return. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Choosing columns first may remove the status column needed by FILTER. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls choose columns after filtering rows?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for choose columns after filtering rows
• Variable to hold constant: the surrounding setup, so the comparison isolates choose columns after filtering rows.
• Variable to change: reverse the nesting and inspect references
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For choose columns after filtering rows, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a household budget sheet. Transfer choose columns after filtering rows into a household budget sheet. Preserve “FILTER can select records first and CHOOSECOLS can then select fields.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “FILTER can select records first and CHOOSECOLS can then select fields.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a survey response table. Transfer choose columns after filtering rows into a survey response table. Preserve “FILTER can select records first and CHOOSECOLS can then select fields.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “FILTER can select records first and CHOOSECOLS can then select fields.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a data-cleaning checklist. Transfer choose columns after filtering rows into a data-cleaning checklist. Preserve “FILTER can select records first and CHOOSECOLS can then select fields.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “FILTER can select records first and CHOOSECOLS can then select fields.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a revision-topic list. Transfer choose columns after filtering rows into a revision-topic list. Preserve “FILTER can select records first and CHOOSECOLS can then select fields.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “FILTER can select records first and CHOOSECOLS can then select fields.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for choose columns after filtering rows, predict the complete array, then reverse the nesting and inspect references. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. Columns 1 and 3 of Open records return. This follows from the rule: FILTER can select records first and CHOOSECOLS can then select fields. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of choose columns after filtering rows before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain choose columns after filtering rows, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Select fields from a table · Full contents · Next: Sort selected fields carefully →

CHAPTER 13 OF 20 · Back to contents

13. Sort selected fields carefully

The short answer: Sort selected fields carefully is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: If a sort key is removed, retain or compute it before using SORTBY.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “If a sort key is removed, retain or compute it before using SORTBY.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the sort selected fields carefully example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

=SORTBY(CHOOSECOLS(A2:C10,1,2),C2:C10,-1)

Expected result and reasoning. Columns 1 and 2 return ordered by aligned column 3 scores. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. All ranges must still describe the same rows. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls sort selected fields carefully?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for sort selected fields carefully
• Variable to hold constant: the surrounding setup, so the comparison isolates sort selected fields carefully.
• Variable to change: filter rows before sorting
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For sort selected fields carefully, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a school-event roster. Transfer sort selected fields carefully into a school-event roster. Preserve “If a sort key is removed, retain or compute it before using SORTBY.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “If a sort key is removed, retain or compute it before using SORTBY.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a subject-planning workbook. Transfer sort selected fields carefully into a subject-planning workbook. Preserve “If a sort key is removed, retain or compute it before using SORTBY.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “If a sort key is removed, retain or compute it before using SORTBY.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a CCA attendance export. Transfer sort selected fields carefully into a CCA attendance export. Preserve “If a sort key is removed, retain or compute it before using SORTBY.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “If a sort key is removed, retain or compute it before using SORTBY.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a library-book record. Transfer sort selected fields carefully into a library-book record. Preserve “If a sort key is removed, retain or compute it before using SORTBY.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “If a sort key is removed, retain or compute it before using SORTBY.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for sort selected fields carefully, predict the complete array, then filter rows before sorting. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. Columns 1 and 2 return ordered by aligned column 3 scores. This follows from the rule: If a sort key is removed, retain or compute it before using SORTBY. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of sort selected fields carefully before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain sort selected fields carefully, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Choose columns after filtering rows · Full contents · Next: Choose a column by header name →

CHAPTER 14 OF 20 · Back to contents

14. Choose a column by header name

The short answer: Choose a column by header name is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: XMATCH can convert a header label into the relative position CHOOSECOLS needs.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “XMATCH can convert a header label into the relative position CHOOSECOLS needs.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the choose a column by header name example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A1:D5 has headers Name,Class,Score,Note
=CHOOSECOLS(A2:D5,XMATCH("Score",A1:D1))

Expected result and reasoning. The Score column returns. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Using worksheet COLUMN numbers overstates the source array position. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls choose a column by header name?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for choose a column by header name
• Variable to hold constant: the surrounding setup, so the comparison isolates choose a column by header name.
• Variable to change: look up Note
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For choose a column by header name, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a survey response table. Transfer choose a column by header name into a survey response table. Preserve “XMATCH can convert a header label into the relative position CHOOSECOLS needs.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “XMATCH can convert a header label into the relative position CHOOSECOLS needs.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a data-cleaning checklist. Transfer choose a column by header name into a data-cleaning checklist. Preserve “XMATCH can convert a header label into the relative position CHOOSECOLS needs.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “XMATCH can convert a header label into the relative position CHOOSECOLS needs.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a revision-topic list. Transfer choose a column by header name into a revision-topic list. Preserve “XMATCH can convert a header label into the relative position CHOOSECOLS needs.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “XMATCH can convert a header label into the relative position CHOOSECOLS needs.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a household budget sheet. Transfer choose a column by header name into a household budget sheet. Preserve “XMATCH can convert a header label into the relative position CHOOSECOLS needs.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “XMATCH can convert a header label into the relative position CHOOSECOLS needs.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for choose a column by header name, predict the complete array, then look up Note. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The Score column returns. This follows from the rule: XMATCH can convert a header label into the relative position CHOOSECOLS needs. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of choose a column by header name before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain choose a column by header name, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Sort selected fields carefully · Full contents · Next: Choose several named headers →

CHAPTER 15 OF 20 · Back to contents

15. Choose several named headers

The short answer: Choose several named headers is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: HSTACK of separate named selections or an array of positions can build a requested field set.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “HSTACK of separate named selections or an array of positions can build a requested field set.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the choose several named headers example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

=CHOOSECOLS(A2:D5,XMATCH({"Name","Score"},A1:D1))

Expected result and reasoning. Name and Score columns return in requested order. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. A missing header can introduce #N/A before CHOOSECOLS runs. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls choose several named headers?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for choose several named headers
• Variable to hold constant: the surrounding setup, so the comparison isolates choose several named headers.
• Variable to change: change Score to Missing
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For choose several named headers, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a subject-planning workbook. Transfer choose several named headers into a subject-planning workbook. Preserve “HSTACK of separate named selections or an array of positions can build a requested field set.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “HSTACK of separate named selections or an array of positions can build a requested field set.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a CCA attendance export. Transfer choose several named headers into a CCA attendance export. Preserve “HSTACK of separate named selections or an array of positions can build a requested field set.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “HSTACK of separate named selections or an array of positions can build a requested field set.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a library-book record. Transfer choose several named headers into a library-book record. Preserve “HSTACK of separate named selections or an array of positions can build a requested field set.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “HSTACK of separate named selections or an array of positions can build a requested field set.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a school-event roster. Transfer choose several named headers into a school-event roster. Preserve “HSTACK of separate named selections or an array of positions can build a requested field set.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “HSTACK of separate named selections or an array of positions can build a requested field set.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for choose several named headers, predict the complete array, then change Score to Missing. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. Name and Score columns return in requested order. This follows from the rule: HSTACK of separate named selections or an array of positions can build a requested field set. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of choose several named headers before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain choose several named headers, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Choose a column by header name · Full contents · Next: Use LET to name positions →

CHAPTER 16 OF 20 · Back to contents

16. Use LET to name positions

The short answer: Use LET to name positions is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: LET can make calculated column positions readable and reusable.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “LET can make calculated column positions readable and reusable.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the use let to name positions example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

=LET(h,A1:D1,d,A2:D10,CHOOSECOLS(d,XMATCH("Name",h),XMATCH("Score",h)))

Expected result and reasoning. The Name and Score fields return. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. LET names do not change relative positions. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls use let to name positions?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for use let to name positions
• Variable to hold constant: the surrounding setup, so the comparison isolates use let to name positions.
• Variable to change: reverse the two XMATCH calls
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For use let to name positions, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a data-cleaning checklist. Transfer use let to name positions into a data-cleaning checklist. Preserve “LET can make calculated column positions readable and reusable.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “LET can make calculated column positions readable and reusable.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a revision-topic list. Transfer use let to name positions into a revision-topic list. Preserve “LET can make calculated column positions readable and reusable.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “LET can make calculated column positions readable and reusable.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a household budget sheet. Transfer use let to name positions into a household budget sheet. Preserve “LET can make calculated column positions readable and reusable.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “LET can make calculated column positions readable and reusable.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a survey response table. Transfer use let to name positions into a survey response table. Preserve “LET can make calculated column positions readable and reusable.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “LET can make calculated column positions readable and reusable.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for use let to name positions, predict the complete array, then reverse the two XMATCH calls. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The Name and Score fields return. This follows from the rule: LET can make calculated column positions readable and reusable. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of use let to name positions before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain use let to name positions, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Choose several named headers · Full contents · Next: Predict spill width →

CHAPTER 17 OF 20 · Back to contents

17. Predict spill width

The short answer: Predict spill width is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: Result width equals the number of requested col_num arguments, including repeats.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “Result width equals the number of requested col_num arguments, including repeats.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the predict spill width example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

=CHOOSECOLS(A2:E6,1,5,1)

Expected result and reasoning. The result has 5 rows and 3 columns. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Unique requested positions are not the width when repeats exist. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls predict spill width?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for predict spill width
• Variable to hold constant: the surrounding setup, so the comparison isolates predict spill width.
• Variable to change: remove the repeated 1
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For predict spill width, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a CCA attendance export. Transfer predict spill width into a CCA attendance export. Preserve “Result width equals the number of requested col_num arguments, including repeats.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Result width equals the number of requested col_num arguments, including repeats.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a library-book record. Transfer predict spill width into a library-book record. Preserve “Result width equals the number of requested col_num arguments, including repeats.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Result width equals the number of requested col_num arguments, including repeats.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a school-event roster. Transfer predict spill width into a school-event roster. Preserve “Result width equals the number of requested col_num arguments, including repeats.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Result width equals the number of requested col_num arguments, including repeats.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a subject-planning workbook. Transfer predict spill width into a subject-planning workbook. Preserve “Result width equals the number of requested col_num arguments, including repeats.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Result width equals the number of requested col_num arguments, including repeats.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for predict spill width, predict the complete array, then remove the repeated 1. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result has 5 rows and 3 columns. This follows from the rule: Result width equals the number of requested col_num arguments, including repeats. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of predict spill width before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain predict spill width, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Use LET to name positions · Full contents · Next: Diagnose a blocked spill →

CHAPTER 18 OF 20 · Back to contents

18. Diagnose a blocked spill

The short answer: Diagnose a blocked spill is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: The result needs a clear rectangle for all retained rows and requested columns.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “The result needs a clear rectangle for all retained rows and requested columns.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the diagnose a blocked spill example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

G2: =CHOOSECOLS(A2:E6,1,5)

Expected result and reasoning. G2:H6 must be clear. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Checking only G2 misses downstream obstructions. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls diagnose a blocked spill?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for diagnose a blocked spill
• Variable to hold constant: the surrounding setup, so the comparison isolates diagnose a blocked spill.
• Variable to change: place a note in H4
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For diagnose a blocked spill, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a revision-topic list. Transfer diagnose a blocked spill into a revision-topic list. Preserve “The result needs a clear rectangle for all retained rows and requested columns.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “The result needs a clear rectangle for all retained rows and requested columns.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a household budget sheet. Transfer diagnose a blocked spill into a household budget sheet. Preserve “The result needs a clear rectangle for all retained rows and requested columns.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “The result needs a clear rectangle for all retained rows and requested columns.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a survey response table. Transfer diagnose a blocked spill into a survey response table. Preserve “The result needs a clear rectangle for all retained rows and requested columns.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “The result needs a clear rectangle for all retained rows and requested columns.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a data-cleaning checklist. Transfer diagnose a blocked spill into a data-cleaning checklist. Preserve “The result needs a clear rectangle for all retained rows and requested columns.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “The result needs a clear rectangle for all retained rows and requested columns.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for diagnose a blocked spill, predict the complete array, then place a note in H4. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. G2:H6 must be clear. This follows from the rule: The result needs a clear rectangle for all retained rows and requested columns. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of diagnose a blocked spill before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain diagnose a blocked spill, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Predict spill width · Full contents · Next: Check version compatibility →

CHAPTER 19 OF 20 · Back to contents

19. Check version compatibility

The short answer: Check version compatibility is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: CHOOSECOLS is documented for Microsoft 365 and Excel 2024 families.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “CHOOSECOLS is documented for Microsoft 365 and Excel 2024 families.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the check version compatibility example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

=CHOOSECOLS({1,2,3},2)

Expected result and reasoning. A supported version returns 2; an unsupported version may show #NAME? or _xlfn.CHOOSECOLS. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Availability is not repaired by changing data. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls check version compatibility?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for check version compatibility
• Variable to hold constant: the surrounding setup, so the comparison isolates check version compatibility.
• Variable to change: open in a supported version
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For check version compatibility, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a library-book record. Transfer check version compatibility into a library-book record. Preserve “CHOOSECOLS is documented for Microsoft 365 and Excel 2024 families.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “CHOOSECOLS is documented for Microsoft 365 and Excel 2024 families.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a school-event roster. Transfer check version compatibility into a school-event roster. Preserve “CHOOSECOLS is documented for Microsoft 365 and Excel 2024 families.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “CHOOSECOLS is documented for Microsoft 365 and Excel 2024 families.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a subject-planning workbook. Transfer check version compatibility into a subject-planning workbook. Preserve “CHOOSECOLS is documented for Microsoft 365 and Excel 2024 families.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “CHOOSECOLS is documented for Microsoft 365 and Excel 2024 families.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a CCA attendance export. Transfer check version compatibility into a CCA attendance export. Preserve “CHOOSECOLS is documented for Microsoft 365 and Excel 2024 families.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “CHOOSECOLS is documented for Microsoft 365 and Excel 2024 families.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for check version compatibility, predict the complete array, then open in a supported version. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. A supported version returns 2; an unsupported version may show #NAME? or _xlfn.CHOOSECOLS. This follows from the rule: CHOOSECOLS is documented for Microsoft 365 and Excel 2024 families. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of check version compatibility before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain check version compatibility, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Diagnose a blocked spill · Full contents · Next: Choose CHOOSECOLS for arbitrary positions →

CHAPTER 20 OF 20 · Back to contents

20. Choose CHOOSECOLS for arbitrary positions

The short answer: Choose CHOOSECOLS for arbitrary positions is a decision about array shape and meaning, not a longer formula to memorise. The governing rule is: Use CHOOSECOLS for selected, reordered or repeated columns; use TAKE or DROP for contiguous edge slices.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies this contract: “Use CHOOSECOLS for selected, reordered or repeated columns; use TAKE or DROP for contiguous edge slices.”. A small grid exposes the relevant dimensions and spill result without unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the choose choosecols for arbitrary positions example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

=CHOOSECOLS({A,B,C,D},1,3)

Expected result and reasoning. The result is A then C. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. TAKE cannot select separated columns without including B. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which source dimension, sign, position or width controls choose choosecols for arbitrary positions?”

• Evidence to collect: source cells, exact formula, Excel version, predicted dimensions, observed spill and the rule for choose choosecols for arbitrary positions
• Variable to hold constant: the surrounding setup, so the comparison isolates choose choosecols for arbitrary positions.
• Variable to change: solve first two columns with TAKE
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For choose choosecols for arbitrary positions, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a household budget sheet. Transfer choose choosecols for arbitrary positions into a household budget sheet. Preserve “Use CHOOSECOLS for selected, reordered or repeated columns; use TAKE or DROP for contiguous edge slices.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Use CHOOSECOLS for selected, reordered or repeated columns; use TAKE or DROP for contiguous edge slices.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a survey response table. Transfer choose choosecols for arbitrary positions into a survey response table. Preserve “Use CHOOSECOLS for selected, reordered or repeated columns; use TAKE or DROP for contiguous edge slices.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Use CHOOSECOLS for selected, reordered or repeated columns; use TAKE or DROP for contiguous edge slices.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a data-cleaning checklist. Transfer choose choosecols for arbitrary positions into a data-cleaning checklist. Preserve “Use CHOOSECOLS for selected, reordered or repeated columns; use TAKE or DROP for contiguous edge slices.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Use CHOOSECOLS for selected, reordered or repeated columns; use TAKE or DROP for contiguous edge slices.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a revision-topic list. Transfer choose choosecols for arbitrary positions into a revision-topic list. Preserve “Use CHOOSECOLS for selected, reordered or repeated columns; use TAKE or DROP for contiguous edge slices.” while identifying which rows, columns, values or error states change. Record the source grid, prediction, result and Excel version. Start by naming the observable evidence, then apply the chapter rule “Use CHOOSECOLS for selected, reordered or repeated columns; use TAKE or DROP for contiguous edge slices.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the fixture for choose choosecols for arbitrary positions, predict the complete array, then solve first two columns with TAKE. Explain the contrast. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is A then C. This follows from the rule: Use CHOOSECOLS for selected, reordered or repeated columns; use TAKE or DROP for contiguous edge slices. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask for a sketch of choose choosecols for arbitrary positions before calculation and one controlled variation after it. A correct grid without the governing rule is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain choose choosecols for arbitrary positions, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Check version compatibility · Full contents · Parent guide →

Parent guide: what mastery looks like

A learner has not mastered Excel CHOOSECOLS merely because one polished example runs. Look for four behaviours: they can state the rule in plain language; predict before executing; isolate one changed condition; and explain why a transfer case is the same problem or a different one. Those behaviours make progress visible without requiring a parent to become a specialist.

Use a ten-minute check-in. Ask the learner to choose one chapter, reconstruct its example from memory, and narrate the causal chain. Then change one input. If the learner can update the prediction and justify the change, stop. More repetitions are not automatically better. Save the two-case pair and return to it after a few days.

If every answer is hesitant, reduce the scope. Return to the earliest chapter whose rule cannot be stated accurately. If explanations are fluent but real tasks still fail, move to a capstone because the missing skill is likely transfer or diagnosis. If frustration rises, separate typing errors from conceptual errors and solve only one category in that session.

Capstone practice with explained routes

Capstone 1: Rebuild the smallest trustworthy fixture

Scenario. Create a disposable workbook demonstrating selecting and reordering array fields.

Route. Record source, dimensions, formula, prediction and output. Begin with a written prediction, collect the smallest sufficient evidence, and explain which observation would make you revise the conclusion.

Explained resolution. Another learner can reproduce the result and explain the rule. A strong submission includes the result, the causal chain and a controlled contrasting case. It does not hide uncertainty: if platform, version or environment matters, state that boundary.

Capstone 2: Diagnose a believable wrong explanation

Scenario. The output is correct but the explanation is wrong.

Route. Change the condition the wrong explanation says matters. Begin with a written prediction, collect the smallest sufficient evidence, and explain which observation would make you revise the conclusion.

Explained resolution. The contrast exposes the misconception. A strong submission includes the result, the causal chain and a controlled contrasting case. It does not hide uncertainty: if platform, version or environment matters, state that boundary.

Capstone 3: Handle a version boundary

Scenario. Two installations disagree.

Route. Capture versions, platform, exact formula and workbook state. Begin with a written prediction, collect the smallest sufficient evidence, and explain which observation would make you revise the conclusion.

Explained resolution. Bind the claim to documented availability. A strong submission includes the result, the causal chain and a controlled contrasting case. It does not hide uncertainty: if platform, version or environment matters, state that boundary.

Capstone 4: Design a parent check-in

Scenario. A parent has ten minutes.

Route. Ask for one rule, sketch, prediction and changed condition. Begin with a written prediction, collect the smallest sufficient evidence, and explain which observation would make you revise the conclusion.

Explained resolution. The learner demonstrates causal understanding. A strong submission includes the result, the causal chain and a controlled contrasting case. It does not hide uncertainty: if platform, version or environment matters, state that boundary.

Capstone 5: Transfer into a larger workbook

Scenario. The tiny fixture works but the real workbook does not.

Route. Compare dimensions, value types, spill cells and references one at a time. Begin with a written prediction, collect the smallest sufficient evidence, and explain which observation would make you revise the conclusion.

Explained resolution. The first changed dependency becomes the next focus. A strong submission includes the result, the causal chain and a controlled contrasting case. It does not hide uncertainty: if platform, version or environment matters, state that boundary.

Capstone 6: Write a regression check

Scenario. New rows will arrive next week.

Route. Save the positive case, near miss and expected shape. Begin with a written prediction, collect the smallest sufficient evidence, and explain which observation would make you revise the conclusion.

Explained resolution. A useful check reports a meaningful change. A strong submission includes the result, the causal chain and a controlled contrasting case. It does not hide uncertainty: if platform, version or environment matters, state that boundary.

Capstone 7: Explain a limitation honestly

Scenario. The function does not fit the reader job.

Route. Name the limitation and safer route. Begin with a written prediction, collect the smallest sufficient evidence, and explain which observation would make you revise the conclusion.

Explained resolution. Good advice is bounded. A strong submission includes the result, the causal chain and a controlled contrasting case. It does not hide uncertainty: if platform, version or environment matters, state that boundary.

Capstone 8: Choose the right function

Scenario. Several array functions appear equivalent.

Route. Define desired values, shape, order and errors. Begin with a written prediction, collect the smallest sufficient evidence, and explain which observation would make you revise the conclusion.

Explained resolution. Choose the contract matching the job. A strong submission includes the result, the causal chain and a controlled contrasting case. It does not hide uncertainty: if platform, version or environment matters, state that boundary.

Frequently asked questions

Is Excel CHOOSECOLS mainly a formula to memorise?

No. Mastery means stating the array contract, predicting the returned dimensions, checking an edge case and choosing another function when the job differs.

How long should a practice session be?

Ten to twenty focused minutes is enough for one rule, one example, one near miss and one explained variation.

Should every combination be memorised?

No. Memorise what positive, negative and omitted arguments mean, then derive the result from the source dimensions.

What can a non-specialist parent ask?

Ask the learner to sketch the source and result, predict before calculating, and explain one changed case.

When is copying useful?

Copying establishes a baseline. Learning begins when the learner reconstructs, varies and explains it.

How do we separate setup and concept errors?

Use a tiny known-good workbook first. Check Excel version and spill space before changing the formula model.

Should practice use an important workbook?

Begin in a disposable workbook or a copy, then transfer the verified rule.

What belongs in revision notes?

Keep the rule, smallest formula, source dimensions, predicted result, observed result and Excel version.

What if two computers disagree?

Record versions, source grid, formula and spill area, then check Microsoft Support.

When should the learner move on?

Move on after the learner can predict two contrasts, diagnose a broken case and transfer the rule without prompts.

References and verification notes

Primary technical source: Official Microsoft Support documentation for CHOOSECOLS. Microsoft documents relative column selection, multiple, reordered, repeated and negative positions, #VALUE! boundaries, examples and supported editions.

The worked examples were checked with independent column-selection checks for positive, negative, reordered and repeated positions, with header lookup, spill and version boundaries checked against Microsoft Support. The checks confirm the specific examples in this guide; they do not promise identical behaviour on an older version, a different operating system, a different browser implementation, or a repository with different state. Keep the version and environment with the saved fixture.

Last reviewed: 27 October 2026. This article is educational guidance for families and learners. It does not advertise a physical Punggol outlet or make admissions, affiliation, score or outcome guarantees.