BISM1201 Chap.7 Excel Analysis and Decision Logic
Excel Analysis and Decision Logic
Reliable Excel work separates source data, calculations and presentation. Each row should represent a consistent record and each column a defined field. Summary functions answer different business questions: SUM and COUNT describe scale, AVERAGE and MEDIAN describe centre differently, and MIN, MAX, LARGE, SMALL and RANK describe position.
Relative references move when copied; absolute references keep selected rows or columns fixed. Logical functions translate policy into repeatable tests. IF chooses between outcomes, AND requires all conditions, OR requires at least one, NOT reverses a result, and IFERROR handles defined error cases. Boundary testing and manual samples make formulas auditable.
What this chapter covers
- 01
Workbook structure and source-data discipline
- 02
Summary and statistical function choice
- 03
Relative and absolute cell references
- 04
IF, AND, OR, NOT and nested decisions
- 05
Boundary tests, error handling and interpretation
Classifying service-ticket priority
- +1Write a four-case decision table before building the formula.
- +1Use AND for the Critical rule because both conditions must be true.
- +1Use OR for the remaining Priority rule and preserve Standard as the final result.
- +1Test both true, each single condition and neither condition.
- +1Test exactly 8 hours because exceeds does not include the boundary.
Key terms
- Relative reference
- A cell reference whose row or column changes when the containing formula is copied to another position.
- Absolute reference
- A cell reference with a locked row, column or both so the intended input remains fixed during copying.
- Arithmetic mean
- The sum of numeric observations divided by their count, which can be influenced strongly by extreme values.
- Median value
- The middle observation after sorting, useful when extremes would make the arithmetic mean unrepresentative.
- Logical test
- An expression evaluated as true or false and used to direct a formula, classification or control.
- Boundary case
- A value immediately below, at or above a rule threshold, used to verify inclusion and comparison direction.
Excel Analysis and Decision Logic FAQ
When should I use median instead of average?
Use the median when you need the middle observation and extreme values could distort the arithmetic mean. Explain why the chosen measure fits the business question rather than selecting a function by habit.
How do dollar signs change an Excel reference?
A dollar sign locks the following column letter or row number. Test a copied formula at several positions to confirm which parts should move and which parameter or lookup range should remain fixed.
Why write a decision table before nested IF?
The table exposes every combination, boundary and outcome in plain language. It helps order thresholds correctly, choose AND or OR deliberately and detect a missing case before complexity is hidden inside syntax.
When is IFERROR appropriate?
Use it when a defined error is expected and a controlled message helps the user. Avoid converting every error to zero because zero may be a genuine business result and the source problem becomes invisible.
Exam move
Create a small invented dataset and answer one scale, centre and position question with different functions. Copy a formula using mixed and absolute references, then inspect the last row. For logic, build a decision table and test values below, at and above each threshold.
Finish every exercise with one sentence explaining what the result means for a decision.
Keep a function journal organised by business question rather than alphabetically. For each entry, write the question, required input shape, formula pattern, one boundary case, one common failure and an interpretation sentence. Contrast AVERAGE with MEDIAN on a dataset containing one extreme value.
Contrast COUNT with COUNTA on blanks and text. The comparison makes function choice meaningful.
Practise references on a grid. Copy one formula down, across and into a two-dimensional table, predicting which row and column should move before each copy. Inspect the formula at all corners. Put important assumptions in labelled cells and lock only what should remain stable.
This routine catches reference errors that still produce plausible output.
For logic, translate policy into a truth table and mark inclusive words such as at least or no more than. Test immediately below, at and above every threshold. Use IFERROR only after identifying the expected error and appropriate message.
End with a control pass: reconcile totals, inspect blanks and duplicates, compare a manual sample, scan formula consistency and read the report from a manager’s perspective. A workbook is finished when the logic is traceable and the decision meaning is clear.
Conduct a formula-review exchange using a clean copy of the workbook.
The reviewer should receive the business rule and expected outputs, not an explanation of the formulas. Ask them to locate inputs, predict two results, inspect copied references and reproduce one calculation manually. Log each discrepancy as a rule, data, reference, boundary or presentation problem. Repair the cause and rerun the same test rather than checking only the changed cell.
