Objectives

  • Enter data; use SUM, AVERAGE, and a simple IF.
  • Format currency and freeze the header row.

Theory: Application software.

Tasks (55 minutes)

In a workbook named Surname_IT231_Lab4.xlsx:

  1. Sheet Sales: products A–E, columns Quantity, Unit Price, Revenue (=Quantity*UnitPrice).
  2. Totals row with SUM for Quantity and Revenue; AVERAGE of Unit Price.
  3. Status column: IF Revenue ≥ 10000 then "High" else "Normal".
  4. Format Revenue as currency (NPR or local).
  5. Freeze the header row.
  6. Use absolute references for a tax rate cell (e.g. $B$1) to compute Tax = Revenue * tax rate.

Use at least five product rows with invented but realistic numbers.

Before You Start

  • Open a blank workbook in Excel (LibreOffice Calc or Google Sheets also work; formulas are the same).
  • Rename the first sheet: double-click the tab and type Sales.
  • Every formula starts with =. After typing one, press Enter and check the result before copying it down.

Step-by-Step Guide

  1. Tax rate cell. In A1 type Tax rate, and in B1 type 13% (Nepal’s VAT rate; your instructor may give another).
  2. Headers in row 3. A3:F3: Product, Quantity, Unit Price, Revenue, Status, Tax. Make them bold.

  3. Sample data (rows 4–8). Use these numbers so you can check your results:

    Product Quantity Unit Price Revenue (expected) Status (expected) Tax at 13% (expected)
    A 120 85 10,200 High 1,326
    B 45 250 11,250 High 1,462.50
    C 300 20 6,000 Normal 780
    D 18 450 8,100 Normal 1,053
    E 60 175 10,500 High 1,365
  4. Formulas in row 4, then copy them down to row 8 by dragging the fill handle (the small square at the bottom-right of the cell):
    • Revenue D4: =B4*C4
    • Status E4: =IF(D4>=10000,"High","Normal")
    • Tax F4: =D4*$B$1. The $ signs keep the reference fixed on B1 when copied.
  5. Totals in row 9. A9: Total. B9: =SUM(B4:B8) gives 543. C9: =AVERAGE(C4:C8) gives 196 (the average unit price). D9: =SUM(D4:D8) gives 46,050. F9: =SUM(F4:F8) gives 5,986.50.
  6. Currency format. Select C4:D9 and F4:F9, then Home → Number Format → Currency (choose NPR/”Rs” or your local symbol), or More Number Formats → Custom "Rs "#,##0.00.
  7. Freeze the header. Click cell A4, then View → Freeze Panes → Freeze Panes. Rows 1–3 now stay visible while you scroll.
  8. Test the absolute reference. Change B1 to 10%. Every tax value and the tax total (4,605) should update at once.

Check Your Work

Checkpoint How to verify
Formulas, not typed numbers Clicking any Revenue, Status or Tax cell shows a formula in the formula bar
Totals Quantity 543; Revenue 46,050; average Unit Price 196 (with the sample data)
Absolute reference Changing B1 updates all tax values at once
Formatting Currency symbol shown; header rows stay visible when scrolling

Common Errors and Fixes

Problem Fix
#NAME? A function name is misspelt (e.g. =SUMM) or the text is missing quotes, as in "High".
Every Status shows “Normal” The numbers were typed as text (left-aligned). Retype them, or use Data → Text to Columns → Finish.
Tax is 0 in rows below the first The rate reference was not absolute. Use $B$1, not B1.
##### in a cell The column is too narrow for the number. Double-click the column border to auto-fit it.

Extension Task

For those who finish early: Add a column Rank using =RANK(D4,$D$4:$D$8) (adjust the rows to your layout), and apply Conditional Formatting → Data Bars to Revenue. Which product would you promote next month, and why?

Deliverable

The .xlsx file.

Viva

  1. What is a cell reference? Give a relative and an absolute example.
  2. What does SUM ignore that you still must check by eye?
  3. How is a spreadsheet different from a word-processor table?
  4. Why freeze panes?
  5. What is the difference between a formula and a function?
  6. Explain what the fill handle does when copying =B4*C4 down one row.
  7. What will =IF(D4>=10000,"High","Normal") return if D4 is exactly 10000?
  8. What is the difference between Freeze Top Row and Freeze Panes?