Lab — Computer Fundamentals and Office Automation

14 experiments, each set out as 1. Question, 2. Aim, 3. Steps, 4. Programme, 5. Execution and Results.

Word, PowerPoint and Excel cannot be installed in the environment these notes are verified in, so each experiment has two halves:

python3 tools/data-science/run_office_labs.py

For the same spreadsheet functions applied to statistical data, see labs/course-4-stats/excel-walkthroughs.md.

The experiments

(The syllabus writes experiment 1 as "Dessembling" — a typo in the official document for "Disassembling".)

Experiment 1 — Assembling and disassembling a computer

1. Question

Unit 2, hardware and networks. Disassemble a desktop computer and assemble it again, naming each component as it comes out and goes back.

2. Aim

Identify the parts of a computer and how they connect, and handle them safely.

3. Steps

1. Make it safe. Unplug the power, discharge static by touching the metal chassis or wearing an anti-static strap, never force a component, and note the orientation of everything before removing it.

2. Identify the parts. Identify and be able to name: the motherboard, CPU and its heatsink, RAM modules and their slots, SMPS (power supply), hard disk or SSD, SATA and power cables, expansion cards, and the front-panel connectors.

3. Take it apart, then put it back, in the reverse order, checking each connector.

4. Write notes as you go — the practical record matters as much as the doing.

4. Programme

There is no program: the work is done with the hardware, by the steps above. Safety, which examiners ask about, is step 1.

5. Execution and Results

Experiment 2 — Network topology of your institution

1. Question

Unit 2. Identify the network topology of your institution's computer lab.

2. Aim

Record the network as it is actually cabled, and name its topology with a reason.

3. Steps

1. Walk the lab and record what you actually see: how many machines, how they connect to the switch, where the switch sits, how the switch reaches the router, and how the router reaches the ISP.

2. Draw it, label the devices, and state the topology with your reasoning.

Almost every college lab is a physical star — every machine cabled to a central switch. If several labs each have a switch, all feeding one core switch, that is a tree (a hierarchy of stars) and saying so earns extra credit.

4. Programme

There is no program: the experiment is an observation, recorded as a labelled drawing.

5. Execution and Results

Experiment 3 — Resume

1. Question

Unit 3, Word. Prepare your resume.

2. Aim

Produce a one-page resume laid out with styles, exported so that its layout cannot shift.

3. Steps

1. Use styles for the section headings, not manual formatting.
2. Keep to one page for a first-year student.

3. Include: name and contact details, objective, education with percentages, skills, projects, certifications, achievements.

4. Export to PDF so the layout cannot shift on someone else's machine.

4. Programme

There is no program: the resume is written in Word, by the steps above.

5. Execution and Results

Experiment 4 — Leave letter

1. Question

Unit 3, Word. Write a letter to a higher official asking for ten days' leave.

2. Aim

Write a business letter in the standard format.

3. Steps

1. Use the standard business letter format: sender's address, date, recipient's designation and address, subject line, salutation, body (reason, dates, handover arrangements), closing, signature.

2. Be exact. Ten days' leave means stating the exact dates and what happens to your work in the meantime.

4. Programme

There is no program: the letter is written in Word, by the steps above.

5. Execution and Results

Experiment 5 — Presentation with text, audio and video

1. Question

Unit 3, PowerPoint. Prepare a presentation that uses text, audio and video.

2. Aim

Build a presentation with all three media, transitions and animations.

3. Steps

The requirement is specifically all three media.

1. Audio: Insert → Audio → Audio on My PC, or Record Audio.
2. Video: Insert → Video → This Device, or an online video.
3. Playback: set it to Automatically or On Click under the Playback tab.
4. Embed rather than link where possible, or the media will not play on the examiner's machine.
5. Add transitions between slides and animations within one slide, so both are demonstrated.

4. Programme

There is no program: the presentation is made in PowerPoint, by the steps above.

5. Execution and Results

Experiment 6 — Class timetable

1. Question

Unit 4, Excel. Prepare your class timetable.

2. Aim

Lay out a timetable grid in Excel that stays readable as it scrolls.

3. Steps

1. Draw the grid: days down the side and periods across the top.
2. Use Merge Cells for double periods, borders for the grid, and colour-coding by subject.
3. Use Freeze Panes so the day column stays visible when scrolling — a small touch that shows understanding.

4. Programme

There is no program: the timetable is a layout, built in Excel by the steps above.

5. Execution and Results

Experiment 7 — Gross and net salary

1. Question

Unit 4, Excel. Compute the gross and net salary of six employees, with DA 30% and HRA 15% of Basic Pay and a deduction of 10% of Basic Pay plus DA.

2. Aim

Build a payroll sheet whose rates sit in their own cells, and check every figure.

3. Steps

1. One row of the sheet, one formula per column. DA, HRA, Gross, Deduction and Net, each from Basic Pay and the rate cells.

2. Compute every row and check it against 1.45 and 1.32 times Basic. The rates fix both ratios, so one check covers the row.

3. Total the columns.

4. Highest and lowest net salary.
5. What the common mistake would cost. The deduction taken on Basic alone, priced in rupees.

DA        = 30% of Basic Pay
HRA       = 15% of Basic Pay
Deduction = 10% of (Basic Pay + DA)     ← includes DA
Gross     = Basic Pay + DA + HRA
Net       = Gross − Deduction

4. Programme

The sheet's formulas are in the docstring of payslip(); the Python equivalent runs them on the six employees:

"""Experiment 7 -- Gross and net salary of six employees.

Excel cannot be run here, so this script computes what the SPREADSHEET
FORMULAS compute and asserts every figure the notes quote. The formulas
themselves are in notes/sem-1/course-1-computer-fundamentals/lab.md; this
file is the proof that the numbers beside them are right.

The one thing worth taking away: Deduction is 10% of (Basic + DA), not 10%
of Basic. Get that wrong and every net salary is too high -- with no error
message, because the sheet still calculates.
"""
from fixtures import EMPLOYEES, DA_RATE, HRA_RATE, DEDUCTION_RATE


# Step 1: One row of the sheet, one formula per column
def payslip(basic):
    """One row of the sheet. Each line is one Excel formula.

        E2  DA         =D2*$B$1
        F2  HRA        =D2*$B$2
        G2  Gross      =D2+E2+F2
        H2  Deduction  =(D2+E2)*$B$3
        I2  Net        =G2-H2
    """
    da = basic * DA_RATE
    hra = basic * HRA_RATE
    gross = basic + da + hra
    deduction = (basic + da) * DEDUCTION_RATE
    net = gross - deduction
    return da, hra, gross, deduction, net


def main():
    print("  Rates: DA 30% of Basic, HRA 15% of Basic, "
          "Deduction 10% of (Basic + DA)\n")
    header = f"  {'Name':<15}{'Basic':>9}{'DA':>9}{'HRA':>8}" \
             f"{'Gross':>10}{'Deduct':>9}{'Net':>10}"
    print(header)
    print("  " + "-" * (len(header) - 2))

    # Step 2: Compute every row and check it against 1.45 and 1.32 times Basic
    totals = [0.0] * 6
    for name, _emp_id, _dept, basic in EMPLOYEES:
        da, hra, gross, deduction, net = payslip(basic)

        # The closed form. Gross = 1.45B and Net = 1.32B fall straight out of
        # the rates, and asserting them catches the commonest error in this
        # experiment: taking the deduction on Basic alone, which would leave
        # Net = 1.35B -- 3% of Basic too much, every month, for every
        # employee.
        assert gross == basic * 1.45, (name, gross)
        assert round(net, 6) == round(basic * 1.32, 6), (name, net)
        assert net != basic * 1.35

        row = (basic, da, hra, gross, deduction, net)
        totals = [t + v for t, v in zip(totals, row)]
        print(f"  {name:<15}{basic:>9,.0f}{da:>9,.0f}{hra:>8,.0f}"
              f"{gross:>10,.0f}{deduction:>9,.0f}{net:>10,.0f}")

    # Step 3: Total the columns
    print("  " + "-" * (len(header) - 2))
    print(f"  {'TOTAL':<15}" + "".join(
        f"{v:>{w},.0f}" for v, w in zip(totals, (9, 9, 8, 10, 9, 10))))

    # The column totals quoted in lab.md.
    assert totals == [200500, 60150, 30075, 290725, 26065, 264660], totals

    # Step 4: Highest and lowest net salary
    # Highest and lowest paid, which the experiment also asks for.
    nets = {name: payslip(basic)[4] for name, _i, _d, basic in EMPLOYEES}
    top = max(nets, key=nets.get)
    low = min(nets, key=nets.get)
    assert top == "Faisal Ahmed" and nets[top] == 68640
    assert low == "Chitra Devi" and nets[low] == 24420
    print(f"\n  Highest net  {top:<15} {nets[top]:>10,.0f}")
    print(f"  Lowest net   {low:<15} {nets[low]:>10,.0f}")

    # Step 5: What the common mistake would cost
    # What the common mistake would have cost, in rupees, for this sheet.
    wrong_total = sum(basic * 1.35 for _n, _i, _d, basic in EMPLOYEES)
    overpay = wrong_total - totals[5]
    assert round(overpay, 6) == round(200500 * 0.03, 6)
    print(f"\n  Deducting 10% of Basic instead of 10% of (Basic + DA)")
    print(f"  overpays the payroll by {overpay:,.0f} a month "
          f"(3% of the {totals[0]:,.0f} basic bill).")


if __name__ == "__main__":
    main()

5. Execution and Results

  Rates: DA 30% of Basic, HRA 15% of Basic, Deduction 10% of (Basic + DA)

  Name               Basic       DA     HRA     Gross   Deduct       Net
  ----------------------------------------------------------------------
  Anitha Rao        25,000    7,500   3,750    36,250    3,250    33,000
  Bharat Kumar      32,000    9,600   4,800    46,400    4,160    42,240
  Chitra Devi       18,500    5,550   2,775    26,825    2,405    24,420
  Daniel Joseph     45,000   13,500   6,750    65,250    5,850    59,400
  Esha Nair         28,000    8,400   4,200    40,600    3,640    36,960
  Faisal Ahmed      52,000   15,600   7,800    75,400    6,760    68,640
  ----------------------------------------------------------------------
  TOTAL            200,500   60,150  30,075   290,725   26,065   264,660

  Highest net  Faisal Ahmed        68,640
  Lowest net   Chitra Devi         24,420

  Deducting 10% of Basic instead of 10% of (Basic + DA)
  overpays the payroll by 6,015 a month (3% of the 200,500 basic bill).

The sheet, worked through. Six employees, columns A Name B EmpID C Department D Basic, with the rates in $B$1, $B$2, $B$3:

Notice what falls out of the rates: Gross is always 1.45 × Basic and Net is always 1.32 × Basic. Check one row against that and you have checked the whole sheet.

(The same calculation in C is lab experiment 12 of Problem Solving Using C — comparing the two is instructive.)

Experiment 8 — Class results

1. Question

Unit 4, Excel. Twenty students, five subjects. Compute per student: total, average, result (pass/fail), grade. Compute per subject: highest, lowest, average, pass count.

2. Aim

Build a results sheet whose formulas are right, and show what the two easy mistakes would cost.

3. Steps

1. The grade formula, on the average.

2. Total, average, result and grade for each student. The result uses the lowest mark: a student must pass every subject.

3. Print the class sheet.

4. The class summary. The grade distribution, and who failed.
5. Subject by subject: highest, lowest, average, passes.
6. Mistake 1: grading on the total.
7. Mistake 2: pass or fail on the average.

H2  Total     =SUM(C2:G2)
I2  Average   =AVERAGE(C2:G2)
J2  Result    =IF(MIN(C2:G2)>=40,"Pass","Fail")
K2  Grade     =IF(I2>=90,"A",IF(I2>=75,"B",IF(I2>=60,"C",IF(I2>=40,"D","F"))))
    Subject high   =MAX(C2:C21)
    Subject low    =MIN(C2:C21)
    Pass count     =COUNTIF(C2:C21,">=40")

4. Programme

"""Experiment 8 -- Class-wise and subject-wise results for twenty students.

Two mistakes make this experiment's sheet calculate perfectly and report
nonsense. Both are demonstrated here with the actual class, because a warning
in prose is easy to nod at and forget:

  1. Grading on the TOTAL instead of the average. The cut-offs (90/75/60/40)
     are percentages; a total out of 500 clears 90 almost automatically.
  2. Deciding pass/fail on the AVERAGE instead of every subject. A student can
     average well above 40 while failing a paper outright.

The corrected formulas, and the column layout every one of them depends on,
are in notes/sem-1/course-1-computer-fundamentals/lab.md.
"""
from collections import Counter

from fixtures import STUDENTS, SUBJECTS, PASS_MARK, GRADE_BANDS, FAIL_GRADE


# Step 1: The grade formula, on the average
def grade(score):
    """K2  =IF(I2>=90,"A",IF(I2>=75,"B",IF(I2>=60,"C",IF(I2>=40,"D","F"))))"""
    for cutoff, letter in GRADE_BANDS:
        if score >= cutoff:
            return letter
    return FAIL_GRADE


# Step 2: Total, average, result and grade for each student
def rows():
    """Per student: total, average, result, grade -- columns H, I, J, K."""
    out = []
    for roll, name, *marks in STUDENTS:
        total = sum(marks)                              # H  =SUM(C2:G2)
        average = total / len(marks)                    # I  =AVERAGE(C2:G2)
        result = "Pass" if min(marks) >= PASS_MARK else "Fail"
        out.append((roll, name, marks, total, average, result, grade(average)))
    return out


def main():
    # Step 3: Print the class sheet
    table = rows()

    print(f"  {'Roll':>4}  {'Name':<10}" +
          "".join(f"{s[:4]:>6}" for s in SUBJECTS) +
          f"{'Total':>7}{'Avg':>7}  {'Result':<7}{'Grade':>5}")
    print("  " + "-" * 74)
    for roll, name, marks, total, average, result, letter in table:
        print(f"  {roll:>4}  {name:<10}" + "".join(f"{m:>6}" for m in marks) +
              f"{total:>7}{average:>7.1f}  {result:<7}{letter:>5}")

    # Step 4: The class summary
    dist = Counter(r[6] for r in table)
    assert dist == Counter({"B": 6, "D": 6, "C": 4, "A": 3, "F": 1}), dist
    fails = [r[1] for r in table if r[5] == "Fail"]
    assert fails == ["Divya", "Ishita", "Kavya", "Rahul"], fails
    print(f"\n  Grade distribution   " +
          "  ".join(f"{g}:{dist[g]}" for g in "ABCDF"))
    print(f"  Passed {len(table) - len(fails)} of {len(table)}; "
          f"failed: {', '.join(fails)}")

    # Step 5: Subject by subject: highest, lowest, average, passes
    print(f"\n  {'Subject':<12}{'High':>6}{'Low':>6}{'Average':>9}{'Passed':>8}")
    print("  " + "-" * 41)
    for i, subject in enumerate(SUBJECTS):
        column = [r[2][i] for r in table]
        passed = sum(1 for m in column if m >= PASS_MARK)
        print(f"  {subject:<12}{max(column):>6}{min(column):>6}"
              f"{sum(column) / len(column):>9.2f}{passed:>8}")
        if subject == "Maths":
            assert (max(column), min(column), passed) == (98, 12, 17)
            assert sum(column) / len(column) == 67.0

    averages = {s: sum(r[2][i] for r in table) / len(table)
                for i, s in enumerate(SUBJECTS)}
    hardest = min(averages, key=averages.get)
    print(f"\n  Hardest paper by class average: {hardest} "
          f"({averages[hardest]:.2f})")

    # Step 6: Mistake 1: grading on the total
    wrong = Counter(grade(r[3]) for r in table)
    assert wrong == Counter({"A": 19, "B": 1}), wrong
    kavya = next(r for r in table if r[1] == "Kavya")
    assert kavya[3] == 80 and kavya[6] == "F" and grade(kavya[3]) == "B"
    print(f"\n  If K2 referenced H2 (the total out of 500) instead of I2:")
    print(f"    grades become {dict(wrong)} -- 19 of 20 students get an A,")
    print(f"    and Kavya, who failed all five papers with {kavya[3]}/500,")
    print(f"    is awarded a {grade(kavya[3])}.")

    # Step 7: Mistake 2: pass or fail on the average
    lenient = [r[1] for r in table if r[4] < PASS_MARK]
    assert lenient == ["Kavya"], lenient
    wrongly_passed = sorted(set(fails) - set(lenient))
    assert wrongly_passed == ["Divya", "Ishita", "Rahul"], wrongly_passed
    print(f"\n  If J2 tested the average (>=40) instead of MIN(C2:G2):")
    for name in wrongly_passed:
        r = next(x for x in table if x[1] == name)
        low = min(r[2])
        subject = SUBJECTS[r[2].index(low)]
        print(f"    {name:<9} averages {r[4]:.1f} but scored "
              f"{low} in {subject} -- passed in error")


if __name__ == "__main__":
    main()

5. Execution and Results

  Roll  Name        Math  Phys  Chem  Engl  Comp  Total    Avg  Result Grade
  --------------------------------------------------------------------------
     1  Aarav         92    88    95    90    96    461   92.2  Pass       A
     2  Bhavna        78    82    75    80    85    400   80.0  Pass       B
     3  Chaitanya     65    70    58    72    68    333   66.6  Pass       C
     4  Divya         45    52    38    60    55    250   50.0  Fail       D
     5  Eshan         88    91    85    79    93    436   87.2  Pass       B
     6  Farhan        55    48    62    51    58    274   54.8  Pass       D
     7  Gayatri       96    94    98    92    90    470   94.0  Pass       A
     8  Harsha        72    68    75    70    66    351   70.2  Pass       C
     9  Ishita        38    42    45    40    39    204   40.8  Fail       D
    10  Jatin         82    78    88    75    80    403   80.6  Pass       B
    11  Kavya         12    20     8    15    25     80   16.0  Fail       F
    12  Lakshmi       60    65    55    68    62    310   62.0  Pass       C
    13  Manoj         90    85    92    88    94    449   89.8  Pass       B
    14  Nithya        48    55    50    45    52    250   50.0  Pass       D
    15  Omkar         75    80    72    78    74    379   75.8  Pass       B
    16  Pallavi       68    62    70    65    60    325   65.0  Pass       C
    17  Rahul         35    55    60    58    62    270   54.0  Fail       D
    18  Sneha         98    96    99    95    97    485   97.0  Pass       A
    19  Tarun         58    60    52    55    57    282   56.4  Pass       D
    20  Usha          85    88    82    90    86    431   86.2  Pass       B

  Grade distribution   A:3  B:6  C:4  D:6  F:1
  Passed 16 of 20; failed: Divya, Ishita, Kavya, Rahul

  Subject       High   Low  Average  Passed
  -----------------------------------------
  Maths           98    12    67.00      17
  Physics         96    20    68.95      19
  Chemistry       99     8    67.95      18
  English         95    15    68.30      19
  Computers       97    25    69.95      18

  Hardest paper by class average: Maths (67.00)

  If K2 referenced H2 (the total out of 500) instead of I2:
    grades become {'A': 19, 'B': 1} -- 19 of 20 students get an A,
    and Kavya, who failed all five papers with 80/500,
    is awarded a B.

  If J2 tested the average (>=40) instead of MIN(C2:G2):
    Divya     averages 50.0 but scored 38 in Chemistry -- passed in error
    Ishita    averages 40.8 but scored 38 in Maths -- passed in error
    Rahul     averages 54.0 but scored 35 in Maths -- passed in error

What the two mistakes actually cost. Run on the twenty-student class in labs/course-1-office/fixtures.py:

Both sheets calculate cleanly. Neither shows an error. That is the whole problem with them.

The subject-wise half, from the same class:

Maths has the lowest class average, so it is the hardest paper — even though Chemistry contains the single lowest mark. Averages and extremes answer different questions, and the examiner may ask for either.

Experiment 9 — Grade evaluation with IF, AND, OR, IFERROR

1. Question

Unit 4, Excel. Evaluate grades with IF, AND, OR and IFERROR — the syllabus specifies all four functions.

2. Aim

Write the grade formulas, and test them on the cells that break them: a blank and text.

3. Steps

1. How Excel reads a cell: a number, a blank or text.
2. The plain nested IF.
3. The guarded formula. A blank is reported as absent and text as no data.
4. AND and OR on two subjects.
5. The cells to test with.
6. Grade every test cell both ways.
7. AND and OR with a blank cell.

Grade with a missing-mark guard:
=IFERROR(IF(B2>=90,"A",IF(B2>=75,"B",IF(B2>=60,"C",IF(B2>=40,"D","F")))), "No data")

Pass only if both subjects are cleared:
=IF(AND(B2>=40, C2>=40), "Pass", "Fail")

Distinction in at least one subject:
=IF(OR(B2>=90, C2>=90), "Distinction", "-")

4. Programme

"""Experiment 9 -- Grade evaluation with IF, AND, OR and IFERROR.

The syllabus names all four functions, so all four appear. What this script
adds is the part the examiner actually tests: what each formula does when the
cell is EMPTY, or holds text, or holds a number outside 0-100.

Excel's own quirks are reproduced faithfully rather than tidied up, because
the quirks are the lesson:

  * an empty cell compares as 0, so a blank silently grades "F", not "no data"
  * ISNUMBER is what separates "absent" from "scored nothing"
  * IFERROR only catches ERRORS -- a blank is not an error, so IFERROR does
    not catch it, and the formula the notes give needs ISBLANK as well
"""
from fixtures import GRADE_BANDS, FAIL_GRADE, PASS_MARK

BLANK = ""          # an empty cell
ERROR = "#VALUE!"   # what Excel puts in a cell whose formula failed


# Step 1: How Excel reads a cell: a number, a blank or text
def excel_number(cell):
    """How a comparison like B2>=90 sees a cell.

    Excel coerces an empty cell to 0 in a numeric comparison. Text does not
    coerce -- and in a comparison, text sorts ABOVE every number, so
    "absent">=90 is TRUE. That is not a joke; it is why the guarded formula
    below exists.
    """
    if cell is BLANK or cell == BLANK:
        return 0
    if isinstance(cell, str):
        return float("inf")          # text > any number, in Excel's ordering
    return cell


# Step 2: The plain nested IF
def naive_grade(cell):
    """=IF(B2>=90,"A",IF(B2>=75,"B",IF(B2>=60,"C",IF(B2>=40,"D","F"))))"""
    value = excel_number(cell)
    for cutoff, letter in GRADE_BANDS:
        if value >= cutoff:
            return letter
    return FAIL_GRADE


# Step 3: The guarded formula
def guarded_grade(cell):
    """The formula lab.md gives, with the blank and text cases handled:

        =IF(ISBLANK(B2),"Absent",
           IFERROR(IF(NOT(ISNUMBER(B2)),NA(),
             IF(B2>=90,"A",IF(B2>=75,"B",IF(B2>=60,"C",IF(B2>=40,"D","F"))))),
           "No data"))
    """
    if cell is BLANK or cell == BLANK:
        return "Absent"
    if not isinstance(cell, (int, float)):
        return "No data"            # IFERROR catches the NA() we raised
    return naive_grade(cell)


# Step 4: AND and OR on two subjects
def both_cleared(a, b):
    """=IF(AND(B2>=40, C2>=40), "Pass", "Fail")"""
    return "Pass" if (excel_number(a) >= PASS_MARK and
                      excel_number(b) >= PASS_MARK) else "Fail"


def any_distinction(a, b):
    """=IF(OR(B2>=90, C2>=90), "Distinction", "-")"""
    return "Distinction" if (excel_number(a) >= 90 or
                             excel_number(b) >= 90) else "-"


# Step 5: The cells to test with
CASES = [
    #  cell        what it is
    (95,          "a clear A"),
    (75,          "exactly on the B cut-off"),
    (74.9,        "a whisker below it"),
    (39,          "below the pass mark"),
    (0,           "scored nothing"),
    (BLANK,       "an empty cell -- the student was absent"),
    ("AB",        "text typed into the marks column"),
    (ERROR,       "a cell already holding an error"),
]


# Step 6: Grade every test cell both ways
def main():
    print(f"  {'Cell':<10}{'Naive IF':<12}{'Guarded':<12}What it is")
    print("  " + "-" * 66)
    for cell, description in CASES:
        shown = "(empty)" if cell == BLANK else repr(cell)
        print(f"  {shown:<10}{naive_grade(cell):<12}"
              f"{guarded_grade(cell):<12}{description}")

    # The cut-offs are inclusive: >= means 75 is a B, not a C.
    assert naive_grade(75) == "B" and naive_grade(74.9) == "C"
    assert naive_grade(90) == "A" and naive_grade(89.9) == "B"

    # An empty cell grades F under the naive formula. This is the single
    # result worth remembering from this experiment: the sheet reports a
    # failure for a student who was never examined.
    assert naive_grade(BLANK) == "F"
    assert guarded_grade(BLANK) == "Absent"

    # Text does not error either -- it grades A, because in Excel's ordering
    # text is greater than every number.
    assert naive_grade("AB") == "A"
    assert guarded_grade("AB") == "No data"

    # Step 7: AND and OR with a blank cell
    print("\n  AND / OR, on two subjects")
    print(f"  {'B2':>8}{'C2':>8}  {'AND -> Pass?':<14}OR -> Distinction?")
    print("  " + "-" * 50)
    for a, b in [(85, 90), (45, 38), (95, 60), (40, 40), (BLANK, 95)]:
        sa = "(empty)" if a == BLANK else a
        sb = "(empty)" if b == BLANK else b
        print(f"  {str(sa):>8}{str(sb):>8}  {both_cleared(a, b):<14}"
              f"{any_distinction(a, b)}")

    assert both_cleared(45, 38) == "Fail"       # AND needs both
    assert both_cleared(40, 40) == "Pass"       # the boundary passes
    assert any_distinction(95, 60) == "Distinction"
    assert both_cleared(BLANK, 95) == "Fail"    # blank is 0, so AND fails
    assert any_distinction(BLANK, 95) == "Distinction"

    print("\n  IFERROR catches errors, not blanks. Test your sheet with an")
    print("  empty cell as well as with text -- the examiner will.")


if __name__ == "__main__":
    main()

5. Execution and Results

  Cell      Naive IF    Guarded     What it is
  ------------------------------------------------------------------
  95        A           A           a clear A
  75        B           B           exactly on the B cut-off
  74.9      C           C           a whisker below it
  39        F           F           below the pass mark
  0         F           F           scored nothing
  (empty)   F           Absent      an empty cell -- the student was absent
  'AB'      A           No data     text typed into the marks column
  '#VALUE!' A           No data     a cell already holding an error

  AND / OR, on two subjects
        B2      C2  AND -> Pass?  OR -> Distinction?
  --------------------------------------------------
        85      90  Pass          Distinction
        45      38  Fail          -
        95      60  Pass          Distinction
        40      40  Pass          -
   (empty)      95  Fail          Distinction

  IFERROR catches errors, not blanks. Test your sheet with an
  empty cell as well as with text -- the examiner will.

An examiner will try exactly that — and here is what they will find:

=IF(ISBLANK(B2),"Absent",
   IFERROR(IF(NOT(ISNUMBER(B2)),NA(),
     IF(B2>=90,"A",IF(B2>=75,"B",IF(B2>=60,"C",IF(B2>=40,"D","F"))))),
   "No data"))

AND and OR inherit the same coercion: with B2 empty, AND(B2>=40,C2>=40) is FALSE and OR(B2>=90,C2>=90) still reports a distinction off C2 alone.

Experiment 10 — Employee search, four ways

1. Question

Unit 4, Excel. Build Name, ID, Department, Salary, then implement the same lookup four ways: VLOOKUP, HLOOKUP, XLOOKUP and INDEX+MATCH.

2. Aim

Find an employee's salary four ways, and show where the four differ.

3. Steps

1. VLOOKUP and HLOOKUP: a position inside one range.
2. XLOOKUP and INDEX+MATCH: two separate ranges.
3. All four on one key, and on a key that is missing.
4. VLOOKUP cannot look left.
5. Insert a column, and VLOOKUP returns the wrong field.

VLOOKUP      =VLOOKUP($F$2, $A$2:$D$50, 4, FALSE)
HLOOKUP      =HLOOKUP($F$2, $A$1:$Z$5, 3, FALSE)        (for row-oriented data)
XLOOKUP      =XLOOKUP($F$2, $B$2:$B$50, $D$2:$D$50, "Not found")
INDEX+MATCH  =INDEX($D$2:$D$50, MATCH($F$2, $B$2:$B$50, 0))

4. Programme

"""Experiment 10 -- The same employee lookup, four ways.

VLOOKUP, HLOOKUP, XLOOKUP and INDEX+MATCH all answer the same question, so
the experiment is pointless unless you can say how they DIFFER. This script
models each one's actual mechanics and then demonstrates the two differences
that matter, with the sheet in front of you:

  1. VLOOKUP can only look RIGHT from its key column. Keying on EmpID
     (column B) to fetch Name (column A) is not a hard VLOOKUP -- it is an
     impossible one.
  2. VLOOKUP's column number is a POSITION, not a reference. Insert a column
     and the formula keeps working and starts returning the wrong field.
     XLOOKUP and INDEX+MATCH do not, because they name a range instead.

The sheet is the payroll of experiment 7:  A Name  B EmpID  C Department
D Basic Pay.
"""
from fixtures import EMPLOYEES, EMP_COLUMNS


# Step 1: VLOOKUP and HLOOKUP: a position inside one range
class LeftLookupError(Exception):
    """What VLOOKUP cannot do. Excel reports it as #N/A."""


def vlookup(key, table, columns, col_index, exact=True):
    """=VLOOKUP(key, range, col_index, FALSE)

    Searches the FIRST column of `table` only, and returns the col_index'th
    column of the same row, counting the search column as 1.
    """
    if not exact:
        raise NotImplementedError("approximate match is not used here")
    if not 1 <= col_index <= len(columns):
        raise IndexError("#REF!  -- col_index is outside the range")
    for row in table:
        if row[0] == key:
            return row[col_index - 1]
    return "#N/A"


def hlookup(key, table, columns, row_index):
    """=HLOOKUP(key, range, row_index, FALSE), on the TRANSPOSED sheet.

    HLOOKUP is VLOOKUP rotated: it searches the first ROW and returns from
    the row_index'th row. It exists for sheets laid out with fields down the
    side and records across the top -- which is why this one transposes the
    payroll first.
    """
    transposed = [[columns[c]] + [row[c] for row in table]
                  for c in range(len(columns))]
    header = transposed[0]                       # the Name row
    for column in range(1, len(header)):
        if header[column] == key:
            return transposed[row_index - 1][column]
    return "#N/A"


# Step 2: XLOOKUP and INDEX+MATCH: two separate ranges
def xlookup(key, lookup_values, return_values, if_not_found="Not found"):
    """=XLOOKUP(key, lookup_array, return_array, "Not found")

    Two independent ranges, so direction is irrelevant and there is no
    position to break.
    """
    for value, result in zip(lookup_values, return_values):
        if value == key:
            return result
    return if_not_found


def index_match(key, lookup_values, return_values):
    """=INDEX(return_range, MATCH(key, lookup_range, 0))"""
    for position, value in enumerate(lookup_values, start=1):   # MATCH
        if value == key:
            return return_values[position - 1]                  # INDEX
    return "#N/A"


def column(table, columns, name):
    return [row[columns.index(name)] for row in table]


def main():
    table = [list(row) for row in EMPLOYEES]
    columns = list(EMP_COLUMNS)

    print("  The sheet:  " + "  ".join(
        f"{chr(65 + i)}={c}" for i, c in enumerate(columns)))
    print()
    for row in table:
        print(f"    {row[0]:<15}{row[1]:<7}{row[2]:<13}{row[3]:>7,}")

    # Step 3: All four on one key, and on a key that is missing
    key = "Daniel Joseph"
    names = column(table, columns, "Name")
    basics = column(table, columns, "Basic")

    answers = {
        "VLOOKUP":     vlookup(key, table, columns, 4),
        "HLOOKUP":     hlookup(key, table, columns, 4),
        "XLOOKUP":     xlookup(key, names, basics),
        "INDEX+MATCH": index_match(key, names, basics),
    }
    print(f"\n  Basic pay of {key}:")
    for how, value in answers.items():
        print(f"    {how:<14}{value:>8,}")
    assert set(answers.values()) == {45000}, answers

    # A key that is not there. Each function fails differently, and the
    # difference is the reason XLOOKUP exists.
    missing = "Zoya Khan"
    print(f"\n  A key that is not in the sheet ({missing}):")
    print(f"    VLOOKUP       {vlookup(missing, table, columns, 4)}")
    print(f"    XLOOKUP       {xlookup(missing, names, basics)}"
          "     <- because you supplied the fourth argument")
    print(f"    INDEX+MATCH   {index_match(missing, names, basics)}")
    assert vlookup(missing, table, columns, 4) == "#N/A"
    assert xlookup(missing, names, basics) == "Not found"

    # Step 4: VLOOKUP cannot look left
    emp_ids = column(table, columns, "EmpID")
    print("\n  Now look up by EmpID and fetch the Name (column B -> column A):")
    try:
        # Name is one column LEFT of EmpID, so there is no positive col_index
        # that reaches it. The honest model of this is an exception, not a
        # wrong answer -- you cannot write the formula at all.
        vlookup("E104", [r[1:] for r in table], columns[1:], 0)
        raise AssertionError("a col_index of 0 should have been rejected")
    except IndexError as exc:
        print(f"    VLOOKUP       {exc}")
    print(f"    XLOOKUP       {xlookup('E104', emp_ids, names)}")
    print(f"    INDEX+MATCH   {index_match('E104', emp_ids, names)}")
    assert xlookup("E104", emp_ids, names) == "Daniel Joseph"
    assert index_match("E104", emp_ids, names) == "Daniel Joseph"

    # Step 5: Insert a column, and VLOOKUP returns the wrong field
    # Somebody adds a "Grade" column between Department and Basic Pay. Nothing
    # errors. The VLOOKUP formula still says 4.
    widened_cols = columns[:3] + ["Grade"] + columns[3:]
    widened = [row[:3] + ["G" + row[1][-1]] + row[3:] for row in table]
    still_basic = column(widened, widened_cols, "Basic")

    broken = vlookup(key, widened, widened_cols, 4)
    print("\n  Someone inserts a 'Grade' column before Basic Pay:")
    print(f"    VLOOKUP(...,4)  now returns {broken!r}  "
          "<- the Grade, silently")
    print(f"    XLOOKUP         {xlookup(key, column(widened, widened_cols, 'Name'), still_basic):,}")
    print(f"    INDEX+MATCH     {index_match(key, column(widened, widened_cols, 'Name'), still_basic):,}")

    assert broken == "G4"          # not a number, not an error -- just wrong
    assert broken != 45000
    assert xlookup(key, column(widened, widened_cols, "Name"),
                   still_basic) == 45000
    assert index_match(key, column(widened, widened_cols, "Name"),
                       still_basic) == 45000

    print("\n  That is the viva answer: VLOOKUP's 4 is a position and the")
    print("  other two name a range, so only VLOOKUP breaks -- and it breaks")
    print("  without any error to warn you.")


if __name__ == "__main__":
    main()

5. Execution and Results

  The sheet:  A=Name  B=EmpID  C=Department  D=Basic

    Anitha Rao     E101   Analytics     25,000
    Bharat Kumar   E102   Engineering   32,000
    Chitra Devi    E103   Support       18,500
    Daniel Joseph  E104   Engineering   45,000
    Esha Nair      E105   Analytics     28,000
    Faisal Ahmed   E106   Management    52,000

  Basic pay of Daniel Joseph:
    VLOOKUP         45,000
    HLOOKUP         45,000
    XLOOKUP         45,000
    INDEX+MATCH     45,000

  A key that is not in the sheet (Zoya Khan):
    VLOOKUP       #N/A
    XLOOKUP       Not found     <- because you supplied the fourth argument
    INDEX+MATCH   #N/A

  Now look up by EmpID and fetch the Name (column B -> column A):
    VLOOKUP       #REF!  -- col_index is outside the range
    XLOOKUP       Daniel Joseph
    INDEX+MATCH   Daniel Joseph

  Someone inserts a 'Grade' column before Basic Pay:
    VLOOKUP(...,4)  now returns 'G4'  <- the Grade, silently
    XLOOKUP         45,000
    INDEX+MATCH     45,000

  That is the viva answer: VLOOKUP's 4 is a position and the
  other two name a range, so only VLOOKUP breaks -- and it breaks
  without any error to warn you.

Be ready to explain that in the viva — it is the obvious question. Two demonstrations make the answer concrete, on the payroll sheet from experiment 7 (A Name B EmpID C Department D Basic):

Look up by EmpID and fetch the Name. Name is one column left of the key, so there is no positive col_index that reaches it — you cannot write the VLOOKUP at all. XLOOKUP and INDEX+MATCH return Daniel Joseph without comment, because they take two independent ranges instead of one range and an offset.

Now insert a Grade column before Basic Pay.

VLOOKUP's 4 is a position; the other two name a range. So only VLOOKUP breaks — and it breaks silently, returning a plausible-looking value with no error to warn you.

One more difference worth a mark: for a key that is not in the table, VLOOKUP and INDEX+MATCH both give #N/A, while XLOOKUP returns whatever you put in its fourth argument. That argument is the reason XLOOKUP exists.

Experiment 11 — Sales report with pivot tables

1. Question

Unit 5, Excel. Dataset: Product, Region, Date, Quantity, Revenue. Make a sales report with pivot tables and pivot charts.

2. Aim

Summarise transactions by region, product and month with pivot tables, and filter them with a slicer.

3. Steps

1. A pivot table is a group-by.
2. Pivot 1: Region by Product, Sum of Revenue.
3. Pivot 2: Revenue by month.
4. Sum, Count and Average of one field.
5. What a slicer does.

4. Programme

"""Experiment 11 -- Sales report with pivot tables and charts.

A pivot table is a group-by with a layout. Building one by hand once makes
the dialog obvious afterwards: Rows are the group keys down the side, Columns
are the group keys across the top, Values is the aggregation, and the Grand
Total row is the same aggregation with no grouping at all.

The nine rows here are the ones Course 11 loads into Power BI and Tableau, so
the region totals this pivot produces can be compared straight across the
programme -- ₹10,360 for South, computed here with a dictionary and there
with DAX.
"""
from collections import defaultdict

from fixtures import sales_rows

ROWS = sales_rows()   # product, region, date, qty, revenue


# Step 1: A pivot table is a group-by
def pivot(rows, row_key, col_key, value, aggregate=sum):
    """Rows = row_key, Columns = col_key, Values = aggregate of `value`."""
    buckets = defaultdict(list)
    for row in rows:
        buckets[(row_key(row), col_key(row))].append(value(row))
    return {cell: aggregate(values) for cell, values in buckets.items()}


def render(cells, title, money=True):
    row_labels = sorted({r for r, _c in cells})
    col_labels = sorted({c for _r, c in cells})
    width = max(len(str(x)) for x in list(row_labels) + ["Grand Total"]) + 2

    print(f"\n  {title}")
    print(f"  {'':<{width}}" + "".join(f"{c:>15}" for c in col_labels) +
          f"{'Total':>15}")
    grand = 0
    col_totals = defaultdict(int)
    for r in row_labels:
        total = 0
        line = f"  {r:<{width}}"
        for c in col_labels:
            v = cells.get((r, c), 0)
            total += v
            col_totals[c] += v
            line += f"{v:>15,}" if v else f"{'-':>15}"
        grand += total
        print(line + f"{total:>15,}")
    print(f"  {'Grand Total':<{width}}" +
          "".join(f"{col_totals[c]:>15,}" for c in col_labels) +
          f"{grand:>15,}")
    return grand


def main():
    print(f"  {len(ROWS)} transactions: Product, Region, Date, "
          "Quantity, Revenue\n")
    for product, region, date, qty, revenue in ROWS:
        print(f"    {product:<15}{region:<7}{date}  {qty:>3}  {revenue:>7,}")

    # Step 2: Pivot 1: Region by Product, Sum of Revenue
    by_region_product = pivot(ROWS, lambda r: r[1], lambda r: r[0],
                              lambda r: r[4])
    grand = render(by_region_product,
                   "Rows = Region   Columns = Product   Values = Sum of Revenue")

    region_totals = defaultdict(int)
    for (region, _product), value in by_region_product.items():
        region_totals[region] += value
    assert region_totals == {"South": 10360, "North": 2520}, dict(region_totals)
    assert grand == 12880

    # Step 3: Pivot 2: Revenue by month
    # Right-click a date in the pivot -> Group -> Months. That is all the
    # grouping is: take the first seven characters of the ISO date.
    by_month = pivot(ROWS, lambda r: r[2][:7], lambda r: "Revenue",
                     lambda r: r[4])
    render(by_month, "Rows = Date (grouped by month)   Values = Sum of Revenue")

    months = {m: v for (m, _c), v in by_month.items()}
    assert months == {"2026-01": 5180, "2026-02": 2480,
                      "2026-04": 3640, "2026-05": 1580}, months
    assert sum(months.values()) == 12880

    # There is no March. Excel's date grouping only shows months that OCCUR,
    # so the pivot lists four rows and a line chart drawn from it joins
    # February straight to April -- a two-month gap rendered as one step.
    # Experiment 14 shows what that does to a growth column.
    assert "2026-03" not in months
    print("\n  Note the pivot has four month rows, not five: no sale fell in")
    print("  March, and pivot grouping omits empty periods rather than")
    print("  showing them as zero. Experiment 14 deals with the consequences.")

    # Step 4: Sum, Count and Average of one field
    qty_sum = sum(r[3] for r in ROWS)
    assert qty_sum == 87
    print(f"\n  Value Field Settings on the same field:")
    print(f"    Sum of Quantity      {qty_sum}")
    print(f"    Count of Quantity    {len(ROWS)}")
    print(f"    Average of Quantity  {qty_sum / len(ROWS):.2f}")
    assert round(qty_sum / len(ROWS), 2) == 9.67

    # Step 5: What a slicer does
    # A slicer is a filter applied before the group-by, nothing more.
    south_only = [r for r in ROWS if r[1] == "South"]
    sliced = pivot(south_only, lambda r: r[1], lambda r: r[0], lambda r: r[4])
    assert sum(sliced.values()) == 10360
    print(f"\n  Slicer set to South: the same pivot now totals "
          f"{sum(sliced.values()):,} -- a filter applied before grouping.")


if __name__ == "__main__":
    main()

5. Execution and Results

  9 transactions: Product, Region, Date, Quantity, Revenue

    Rice 5kg       South  2026-01-15   10    2,800
    Shampoo 200ml  South  2026-01-15    5      700
    Tea 500g       South  2026-01-15    8    1,680
    Rice 5kg       South  2026-02-10    6    1,680
    Notebook       North  2026-02-10   20      800
    Tea 500g       South  2026-04-05   12    2,520
    Rice 5kg       North  2026-04-05    4    1,120
    Shampoo 200ml  South  2026-05-20    7      980
    Notebook       North  2026-05-20   15      600

  Rows = Region   Columns = Product   Values = Sum of Revenue
                      Notebook       Rice 5kg  Shampoo 200ml       Tea 500g          Total
  North                  1,400          1,120              -              -          2,520
  South                      -          4,480          1,680          4,200         10,360
  Grand Total            1,400          5,600          1,680          4,200         12,880

  Rows = Date (grouped by month)   Values = Sum of Revenue
                       Revenue          Total
  2026-01                5,180          5,180
  2026-02                2,480          2,480
  2026-04                3,640          3,640
  2026-05                1,580          1,580
  Grand Total           12,880         12,880

  Note the pivot has four month rows, not five: no sale fell in
  March, and pivot grouping omits empty periods rather than
  showing them as zero. Experiment 14 deals with the consequences.

  Value Field Settings on the same field:
    Sum of Quantity      87
    Count of Quantity    9
    Average of Quantity  9.67

  Slicer set to South: the same pivot now totals 10,360 -- a filter applied before grouping.

The nine transactions used here are the same rows Business Intelligence Tools loads into Power BI and Tableau, so the totals can be compared straight across the catalogue. Pivot one gives:

₹10,360 for South is the figure Business Intelligence Tools reaches with DAX, Big Data Technologies with Hive and Spark, Cloud Computing for Data Science with a warehouse query and Data Engineering and MLOps with an ETL job. Six different engines, one number — which is only meaningful because the runner asserts the underlying rows still match.

A pivot table is a group-by: Rows are the keys down the side, Columns the keys across the top, Values the aggregation, and the Grand Total is the same aggregation with no grouping at all. Switching Value Field Settings on one field between Sum, Count and Average — 87, 9 and 9.67 for Quantity here — is the fastest way to see that.

Experiment 12 — Data entry form with validation

1. Question

Unit 5, Excel. A student registration form using Data → Data Validation, with drop-downs and input rules.

2. Aim

Make each field refuse what it should, and know what each rule still lets through.

3. Steps

1. The five validation rules.
2. Values that should pass and values that should be refused.
3. Try every rule.
4. The email rule, and what it lets through.
5. Input Message, Error Alert, and Stop against Warning.

4. Programme

"""Experiment 12 -- Data entry form with drop-downs and input rules.

Data Validation is a set of predicates Excel evaluates before it lets a value
into a cell. Writing them out as functions and then testing them against bad
input is exactly what the experiment asks you to demonstrate, and it turns up
the thing the syllabus's own email rule gets wrong.

Every rule below is tested with values that SHOULD pass and values that
SHOULD be rejected. A rule you have not tried to break is a rule you have not
tested.
"""
from datetime import date

COURSES = ["B.Sc. Data Science", "B.Sc. Statistics", "B.Com", "B.A."]
ROLL_MIN, ROLL_MAX = 1, 200
DOB_MIN, DOB_MAX = date(1990, 1, 1), date(2012, 12, 31)


# Step 1: The five validation rules
def course_rule(value):
    """Allow -> List, Source: the four courses. A dropdown IS the rule."""
    return value in COURSES


def roll_rule(value):
    """Allow -> Whole number, between 1 and 200."""
    return isinstance(value, int) and ROLL_MIN <= value <= ROLL_MAX


def dob_rule(value):
    """Allow -> Date, between 1990-01-01 and 2012-12-31."""
    return isinstance(value, date) and DOB_MIN <= value <= DOB_MAX


def phone_rule(value):
    """Allow -> Text length, equal to 10."""
    return isinstance(value, str) and len(value) == 10


def email_rule_syllabus(value):
    """Allow -> Custom, formula  =ISNUMBER(SEARCH("@",E2))

    This is the rule the experiment names. It asks one question: does an @
    appear anywhere? SEARCH is case-insensitive and accepts wildcards, and
    ISNUMBER turns "found at position n" into TRUE.
    """
    return isinstance(value, str) and "@" in value


def email_rule_better(value):
    """=AND(ISNUMBER(SEARCH("@",E2)), ISNUMBER(SEARCH(".",E2)),
            LEN(E2)>=6, ISERROR(SEARCH(" ",E2)))

    Still not a real address check -- nothing short of a regular expression
    is -- but it rejects the cases the plain rule lets through.
    """
    return (isinstance(value, str) and "@" in value and "." in value
            and len(value) >= 6 and " " not in value)


# Step 2: Values that should pass and values that should be refused
CHECKS = [
    ("Course",  course_rule,  "List",         [("B.Sc. Data Science", True),
                                               ("B.Sc. Physics", False),
                                               ("", False)]),
    ("Roll no", roll_rule,    "Whole number", [(1, True), (200, True),
                                               (201, False), (0, False),
                                               (45.5, False)]),
    ("DOB",     dob_rule,     "Date",         [(date(2006, 7, 14), True),
                                               (date(1989, 12, 31), False),
                                               (date(2013, 1, 1), False)]),
    ("Phone",   phone_rule,   "Text length",  [("9876543210", True),
                                               ("98765432", False),
                                               ("98765432101", False)]),
]


# Step 3: Try every rule
def main():
    for field, rule, kind, cases in CHECKS:
        print(f"\n  {field}  --  Allow: {kind}")
        for value, expected in cases:
            got = rule(value)
            assert got == expected, (field, value, got)
            mark = "accepted" if got else "REJECTED"
            shown = ("(empty)" if value == "" else
                     value.isoformat() if isinstance(value, date) else
                     repr(value))
            print(f"    {shown:<24}{mark}")

    # Step 4: The email rule, and what it lets through
    print("\n  Email  --  Allow: Custom,  =ISNUMBER(SEARCH(\"@\",E2))")
    email_cases = [
        "asha@nri.ac.in",
        "ASHA@NRI.AC.IN",
        "@",
        "not an email @ all",
        "asha.nri.ac.in",
    ]
    print(f"    {'value':<24}{'syllabus rule':<16}stricter rule")
    for value in email_cases:
        a = "accepted" if email_rule_syllabus(value) else "REJECTED"
        b = "accepted" if email_rule_better(value) else "REJECTED"
        print(f"    {value:<24}{a:<16}{b}")

    # A single @ character satisfies the rule the syllabus specifies. So does
    # a sentence containing one. Neither is an email address, and Excel will
    # accept both without a murmur.
    assert email_rule_syllabus("@") is True
    assert email_rule_syllabus("not an email @ all") is True
    assert email_rule_better("@") is False
    assert email_rule_better("not an email @ all") is False

    # And what it correctly keeps out.
    assert email_rule_syllabus("asha.nri.ac.in") is False

    print("\n  Both rules accept a real address and reject one with no @.")
    print("  The difference is '@' on its own, and a sentence with an @ in")
    print("  it: the syllabus rule takes both. Say so in the viva -- knowing")
    print("  the limits of your own validation is the point of the exercise.")

    # Step 5: Input Message, Error Alert, and Stop against Warning
    print("\n  Every rule needs all three tabs filled in:")
    for tab, purpose in [
            ("Settings",      "the rule itself"),
            ("Input Message", "the hint shown when the cell is selected"),
            ("Error Alert",   "Stop / Warning / Information, and your text")]:
        print(f"    {tab:<16}{purpose}")
    print("\n  Stop refuses the value. Warning and Information both ALLOW it")
    print("  after a confirmation -- so a form that must not accept bad data")
    print("  has to use Stop.")


if __name__ == "__main__":
    main()

5. Execution and Results

  Course  --  Allow: List
    'B.Sc. Data Science'    accepted
    'B.Sc. Physics'         REJECTED
    (empty)                 REJECTED

  Roll no  --  Allow: Whole number
    1                       accepted
    200                     accepted
    201                     REJECTED
    0                       REJECTED
    45.5                    REJECTED

  DOB  --  Allow: Date
    2006-07-14              accepted
    1989-12-31              REJECTED
    2013-01-01              REJECTED

  Phone  --  Allow: Text length
    '9876543210'            accepted
    '98765432'              REJECTED
    '98765432101'           REJECTED

  Email  --  Allow: Custom,  =ISNUMBER(SEARCH("@",E2))
    value                   syllabus rule   stricter rule
    asha@nri.ac.in          accepted        accepted
    ASHA@NRI.AC.IN          accepted        accepted
    @                       accepted        REJECTED
    not an email @ all      accepted        REJECTED
    asha.nri.ac.in          REJECTED        REJECTED

  Both rules accept a real address and reject one with no @.
  The difference is '@' on its own, and a sentence with an @ in
  it: the syllabus rule takes both. Say so in the viva -- knowing
  the limits of your own validation is the point of the exercise.

  Every rule needs all three tabs filled in:
    Settings        the rule itself
    Input Message   the hint shown when the cell is selected
    Error Alert     Stop / Warning / Information, and your text

  Stop refuses the value. Warning and Information both ALLOW it
  after a confirmation -- so a form that must not accept bad data
  has to use Stop.

Stop refuses the value; Warning and Information both let it through after a confirmation. A form that must not accept bad data has to use Stop, and that is a viva question.

Test every rule with values that should pass and values that should be refused. A rule you have not tried to break is a rule you have not tested.

Experiment 13 — Budget with Goal Seek and Scenario Manager

1. Question

Unit 5, Excel. Build a personal budget: income, expense categories, total expenses, savings. Use Goal Seek, the Scenario Manager and a one-variable data table on it.

2. Aim

Find the income a savings target needs, and compare scenarios side by side.

3. Steps

1. The budget: income, expenses, savings and the savings rate.
2. Goal Seek, as a root finder.
3. Goal Seek: a savings amount.
4. Goal Seek: a savings rate.
5. When Goal Seek cannot find a solution.
6. Scenario Manager.
7. One-variable data table.

4. Programme

"""Experiment 13 -- Budget planning with Goal Seek and Scenario Manager.

Goal Seek is a numerical root finder wearing a dialog box. It changes one
cell, watches another, and stops when the watched cell reaches your target.
Implementing it -- properly, with bisection, not by rearranging the algebra --
is the only way to see why it sometimes fails to converge, which is the
question the examiner asks.

The syllabus asks for three things and students routinely stop at the first:
Goal Seek, Scenario Manager, and a one-variable Data Table. All three are
here.
"""
from fixtures import INCOME, EXPENSES

# Step 1: The budget: income, expenses, savings and the savings rate
TOTAL_EXPENSES = sum(EXPENSES.values())


def savings(income=INCOME, expenses=None):
    """B10  =B2-SUM(B4:B9)"""
    return income - (TOTAL_EXPENSES if expenses is None else expenses)


def savings_rate(income):
    """B11  =B10/B2   -- savings as a fraction of income."""
    return savings(income) / income


# Step 2: Goal Seek, as a root finder
def goal_seek(formula, target, lo, hi, tolerance=1e-9, max_iterations=100):
    """What Tools -> Goal Seek does: change one cell until another hits a
    target. Bisection needs the answer bracketed and the formula monotonic
    over the bracket -- exactly the two conditions under which Excel's own
    Goal Seek reports 'may not have found a solution'.
    """
    f_lo, f_hi = formula(lo) - target, formula(hi) - target
    if f_lo * f_hi > 0:
        raise ValueError("target is not bracketed -- Goal Seek would fail")
    for iterations in range(1, max_iterations + 1):
        mid = (lo + hi) / 2
        f_mid = formula(mid) - target
        if abs(f_mid) < tolerance or (hi - lo) / 2 < tolerance:
            return mid, iterations
        if f_lo * f_mid < 0:
            hi, f_hi = mid, f_mid
        else:
            lo, f_lo = mid, f_mid
    raise RuntimeError("did not converge")


def main():
    print(f"  Monthly income          {INCOME:>10,}")
    for name, amount in EXPENSES.items():
        print(f"    {name:<20}{amount:>10,}")
    print(f"  Total expenses          {TOTAL_EXPENSES:>10,}")
    print(f"  Savings                 {savings():>10,}")
    print(f"  Savings rate            {savings_rate(INCOME):>10.2%}")

    assert TOTAL_EXPENSES == 33000
    assert savings() == 12000
    assert round(savings_rate(INCOME), 6) == round(12000 / 45000, 6)

    # Step 3: Goal Seek: a savings amount
    # Set cell B10 To value 20000 By changing cell B2. Linear, so the answer
    # is obvious -- which makes it the right one to check the machinery on.
    income, iterations = goal_seek(savings, 20000, 0, 500000)
    print(f"\n  Goal Seek: savings of 20,000 by changing income")
    print(f"    income required  {income:>12,.2f}   ({iterations} iterations)")
    assert abs(income - 53000) < 1e-6, income

    # Step 4: Goal Seek: a savings rate
    # This is the one worth doing. The rate is NOT linear in income, so you
    # cannot read the answer off the sheet, and 'save 30% of what I earn'
    # needs a bigger rise than most people guess.
    income30, iterations = goal_seek(savings_rate, 0.30, 1, 500000)
    print(f"\n  Goal Seek: a savings RATE of 30% by changing income")
    print(f"    income required  {income30:>12,.2f}   ({iterations} iterations)")
    print(f"    check            {savings_rate(income30):>12.4%}")
    assert abs(income30 - 33000 / 0.70) < 1e-4, income30
    assert abs(income30 - 47142.857142857) < 1e-4
    print(f"    a rise of {income30 - INCOME:,.2f}, to move the rate from "
          f"{savings_rate(INCOME):.2%} to 30%")

    # Step 5: When Goal Seek cannot find a solution
    # A savings rate of 100% needs infinite income: the target is approached
    # but never reached. Excel would grind through its iteration limit and
    # report that it may not have found a solution.
    try:
        goal_seek(savings_rate, 1.00, 1, 10 ** 9)
        raise AssertionError("a 100% savings rate should not be reachable")
    except ValueError as exc:
        print(f"\n  Goal Seek: a savings rate of 100%  ->  {exc}")

    # Step 6: Scenario Manager
    scenarios = {
        "Best case":  (52000, 31000),
        "Realistic":  (45000, 33000),
        "Worst case": (41000, 35500),
    }
    print("\n  Scenario Summary")
    print(f"    {'':<14}{'Income':>10}{'Expenses':>11}{'Savings':>10}{'Rate':>9}")
    for name, (inc, exp) in scenarios.items():
        s = inc - exp
        print(f"    {name:<14}{inc:>10,}{exp:>11,}{s:>10,}{s / inc:>9.1%}")

    assert [inc - exp for inc, exp in scenarios.values()] == [21000, 12000, 5500]
    # The realistic column must reproduce the live sheet, or the scenario has
    # drifted from the model it is supposed to describe.
    assert scenarios["Realistic"] == (INCOME, TOTAL_EXPENSES)

    # Step 7: One-variable data table
    # Rent down the left column, savings recalculated for each. Data ->
    # What-If Analysis -> Data Table, Column input cell = the rent cell.
    print("\n  One-variable Data Table: savings against rent")
    print(f"    {'Rent':>8}{'Savings':>10}{'Rate':>9}")
    other_expenses = TOTAL_EXPENSES - EXPENSES["Rent"]
    table = []
    for rent in range(12000, 18001, 1000):
        s = savings(expenses=other_expenses + rent)
        table.append((rent, s))
        print(f"    {rent:>8,}{s:>10,}{s / INCOME:>9.1%}")

    assert other_expenses == 18000
    assert table == [(12000, 15000), (13000, 14000), (14000, 13000),
                     (15000, 12000), (16000, 11000), (17000, 10000),
                     (18000, 9000)], table
    # Every extra rupee of rent is a rupee off savings -- the slope is -1, and
    # the table exists to make that visible rather than argued.
    slopes = {(b[1] - a[1]) / (b[0] - a[0]) for a, b in zip(table, table[1:])}
    assert slopes == {-1.0}, slopes
    print(f"\n    Slope: {slopes.pop():.0f} -- one rupee of rent, one rupee "
          "of savings.")


if __name__ == "__main__":
    main()

5. Execution and Results

  Monthly income              45,000
    Rent                    15,000
    Food                     8,000
    Transport                3,500
    Utilities                2,800
    Entertainment            2,200
    Miscellaneous            1,500
  Total expenses              33,000
  Savings                     12,000
  Savings rate                26.67%

  Goal Seek: savings of 20,000 by changing income
    income required     53,000.00   (47 iterations)

  Goal Seek: a savings RATE of 30% by changing income
    income required     47,142.86   (32 iterations)
    check                30.0000%
    a rise of 2,142.86, to move the rate from 26.67% to 30%

  Goal Seek: a savings rate of 100%  ->  target is not bracketed -- Goal Seek would fail

  Scenario Summary
                      Income   Expenses   Savings     Rate
    Best case         52,000     31,000    21,000    40.4%
    Realistic         45,000     33,000    12,000    26.7%
    Worst case        41,000     35,500     5,500    13.4%

  One-variable Data Table: savings against rent
        Rent   Savings     Rate
      12,000    15,000    33.3%
      13,000    14,000    31.1%
      14,000    13,000    28.9%
      15,000    12,000    26.7%
      16,000    11,000    24.4%
      17,000    10,000    22.2%
      18,000     9,000    20.0%

    Slope: -1 -- one rupee of rent, one rupee of savings.

Worked through on income ₹45,000 and expenses ₹33,000 (rent 15,000, food 8,000, transport 3,500, utilities 2,800, entertainment 2,200, miscellaneous 1,500), giving savings of ₹12,000 — a rate of 26.67%.

The second is the one worth doing. The rate is not linear in income, so you cannot read the answer off the sheet: moving from 26.67% to 30% needs a rise of ₹2,142.86, which is not the figure most people guess.

Scenario Summary:

The Realistic column must reproduce the live sheet exactly. If it does not, the scenario has drifted from the model it claims to describe — the commonest fault in this experiment, and easy to check.

The one-variable data table completes the set: rent down the left column, savings recalculated beside each — 12,000 → 15,000 savings, rising to 18,000 → 9,000. A slope of exactly −1: one rupee of rent, one rupee of savings. The table exists to make that visible rather than argued.

Experiment 14 — Dashboard

1. Question

Unit 5, Excel. The capstone: a dashboard with a combo chart, sparklines, slicers and KPI cells.

2. Aim

Combine the pivots, charts and formulas of the earlier experiments into one dashboard that stays correct when a month is empty.

3. Steps

1. Monthly revenue, with the empty month kept.
2. Growth on the previous month.
3. The KPI cells.
4. The combo chart's two series.
5. Sparklines.
6. What to check before submitting.

4. Programme

"""Experiment 14 -- Dashboard with combo charts, sparklines and slicers.

The capstone, and the one where a sheet that looks finished is most often
wrong. Everything a dashboard shows is derived, so a dashboard multiplies
whatever mistake is underneath it by the number of tiles on the page.

Two derived values are computed here and both need care:

  * the growth column, which divides by the previous period -- and the
    previous period can be zero, which is where IFERROR from experiment 9
    earns its place;
  * the sparkline series, whose shape depends on whether a missing month is
    drawn as a gap or as a zero.

Same nine transactions as experiment 11.
"""
from collections import defaultdict

from fixtures import sales_rows

ROWS = sales_rows()
MONTHS = ["2026-01", "2026-02", "2026-03", "2026-04", "2026-05"]
DIV0 = "#DIV/0!"


# Step 1: Monthly revenue, with the empty month kept
def monthly_revenue(rows, months=MONTHS):
    """Revenue per month, with every month in the range present.

    The pivot in experiment 11 lists only the four months that HAVE sales.
    Laying the series out against a complete month range instead is what
    makes a trend line honest -- and it is what puts a zero into the
    denominator of the growth column.
    """
    totals = defaultdict(int)
    for _product, _region, date, _qty, revenue in rows:
        totals[date[:7]] += revenue
    return [totals[m] for m in months]


# Step 2: Growth on the previous month
def growth(series):
    """=IFERROR((B3-B2)/B2, "n/a")  filled down the column."""
    out = [None]
    for previous, current in zip(series, series[1:]):
        out.append(DIV0 if previous == 0 else (current - previous) / previous)
    return out


def main():
    series = monthly_revenue(ROWS)
    assert series == [5180, 2480, 0, 3640, 1580], series
    assert sum(series) == 12880

    # Step 3: The KPI cells
    total = sum(r[4] for r in ROWS)
    by_product = defaultdict(int)
    for product, _region, _date, _qty, revenue in ROWS:
        by_product[product] += revenue
    best = max(by_product, key=by_product.get)
    average = total / len(ROWS)

    print("  KPI cells")
    print(f"    Total revenue              {total:>12,}")
    print(f"    Best product               {best:>12}  "
          f"({by_product[best]:,})")
    print(f"    Average per transaction    {average:>12,.2f}")
    print(f"    Transactions               {len(ROWS):>12,}")

    assert total == 12880
    assert best == "Rice 5kg" and by_product[best] == 5600
    assert round(average, 2) == 1431.11
    assert sum(by_product.values()) == total

    # Step 4: The combo chart's two series
    # Columns = revenue (rupees, thousands). Line = growth (a percentage).
    # They share nothing but a category axis, which is precisely why the line
    # needs a SECONDARY axis: on one axis a 30% growth figure plots as 0.3 of
    # a rupee and vanishes into the baseline.
    rates = growth(series)
    print("\n  Combo chart series (columns = revenue, line = growth)")
    print(f"    {'Month':<10}{'Revenue':>10}{'Growth':>12}")
    for month, revenue, rate in zip(MONTHS, series, rates):
        if rate is None:
            shown = "-"
        elif rate == DIV0:
            shown = DIV0
        else:
            shown = f"{rate:.2%}"
        print(f"    {month:<10}{revenue:>10,}{shown:>12}")

    assert rates[0] is None
    assert round(rates[1], 4) == round((2480 - 5180) / 5180, 4)
    assert rates[2] == -1.0                     # a month with no sales at all
    assert rates[3] == DIV0                     # ...and then dividing by it
    assert round(rates[4], 4) == round((1580 - 3640) / 3640, 4)

    print(f"\n    March has no sales, so April's growth divides by zero.")
    print(f"    That is the {DIV0} the =IFERROR(...) wrapper from experiment")
    print("    9 exists for. Without it the chart's line series breaks at")
    print("    April and the tile shows an error where a number should be.")

    # The alternative most students reach for -- drop the empty month -- does
    # not fix anything, it hides it. February to April then reads as one
    # step of +46.77% growth when it is two months of change, and nothing on
    # the chart says so.
    present = [v for v in series if v]
    misleading = growth(present)[2]
    assert round(misleading, 4) == round((3640 - 2480) / 2480, 4)
    print(f"\n    Dropping March instead gives April a growth of "
          f"{misleading:.2%},")
    print("    which is two months of change labelled as one. Neither the")
    print("    chart nor the number admits it. Keep the empty month.")

    # Step 5: Sparklines
    # One tiny line per product row, drawn from that row's monthly series.
    print("\n  Sparkline series, one row per product")
    print(f"    {'Product':<15}" + "".join(f"{m[-2:]:>8}" for m in MONTHS) +
          f"{'Total':>9}")
    for product in sorted(by_product, key=by_product.get, reverse=True):
        product_rows = [r for r in ROWS if r[0] == product]
        product_series = monthly_revenue(product_rows)
        assert sum(product_series) == by_product[product]
        print(f"    {product:<15}" +
              "".join(f"{v:>8,}" for v in product_series) +
              f"{sum(product_series):>9,}")

    # Step 6: What to check before submitting
    print("\n  Before submitting, verify each of these on the sheet itself:")
    for item in [
            "every pivot is connected to the slicer (Report Connections)",
            "the growth line is on the SECONDARY axis, not the primary",
            "the growth column is wrapped in IFERROR",
            "empty periods are shown, not silently dropped",
            "gridlines hidden, working sheets hidden, dashboard protected"]:
        print(f"    [ ] {item}")


if __name__ == "__main__":
    main()

5. Execution and Results

  KPI cells
    Total revenue                    12,880
    Best product                   Rice 5kg  (5,600)
    Average per transaction        1,431.11
    Transactions                          9

  Combo chart series (columns = revenue, line = growth)
    Month        Revenue      Growth
    2026-01        5,180           -
    2026-02        2,480     -52.12%
    2026-03            0    -100.00%
    2026-04        3,640     #DIV/0!
    2026-05        1,580     -56.59%

    March has no sales, so April's growth divides by zero.
    That is the #DIV/0! the =IFERROR(...) wrapper from experiment
    9 exists for. Without it the chart's line series breaks at
    April and the tile shows an error where a number should be.

    Dropping March instead gives April a growth of 46.77%,
    which is two months of change labelled as one. Neither the
    chart nor the number admits it. Keep the empty month.

  Sparkline series, one row per product
    Product              01      02      03      04      05    Total
    Rice 5kg          2,800   1,680       0   1,120       0    5,600
    Tea 500g          1,680       0       0   2,520       0    4,200
    Shampoo 200ml       700       0       0       0     980    1,680
    Notebook              0     800       0       0     600    1,400

  Before submitting, verify each of these on the sheet itself:
    [ ] every pivot is connected to the slicer (Report Connections)
    [ ] the growth line is on the SECONDARY axis, not the primary
    [ ] the growth column is wrapped in IFERROR
    [ ] empty periods are shown, not silently dropped
    [ ] gridlines hidden, working sheets hidden, dashboard protected

KPI cells, on the same nine transactions as experiment 11: total revenue 12,880, best product Rice 5kg (5,600), average per transaction 1,431.11, transactions 9.

Test it before submitting: click a slicer and confirm that every chart updates. If one does not, its pivot is not connected — the commonest fault, and the first thing an examiner will check.

Lab exam tips

1. Save constantly, under a filename containing your roll number.

2. Show your formulas. Press Ctrl + ` to display them all; examiners often ask for this.

3. Format properly. Currency for money, borders on tables, sensible column widths. Marks are given for presentation.

4. Label your charts. Title, axis titles, legend.

5. Use absolute references where a formula will be copied — and be able to explain why.

6. Test the edge cases yourself: a blank cell, a zero, a value not in the lookup table.

7. Expect a viva. "Why FALSE in that VLOOKUP?", "what happens if I change this cell?", "why is that reference $C2 and not $C$2?"

What the practical record should contain

For each experiment, the five parts set out above: 1. Question, the task as set; 2. Aim, in one line; 3. Steps, the method, with the click-path; 4. Programme, the formulas (and, where there is one, the program); 5. Execution and Results, what the sheet showed, and the result in words.

Each program, on its own page

The same experiments, one page each, so a program can be reached by what it does rather than by its number.