Synetic College Logo
Unit 1 of 1 · BCA Sem 2

Unit 1: Statistical computation and visualization

Fundamental of Statistics Laboratory notes · PTU syllabus (UGCC2505)

3 min read5 topics7 exam questions
On this page
  1. Unit summary
  2. Displaying data in tables and charts
  3. Frequency distribution and graphs
  4. Central tendency for grouped data
  5. Measures of dispersion
  6. Letter grades and IQ distribution
  7. Key terms
  8. Quick revision
  9. Important questions

Unit summary

In this lab you perform the calculations of Fundamental of Statistics on a computer, usually in MS Excel. You display data in tables and charts, build frequency distributions and graphs, and compute averages, dispersion, grades and an IQ distribution.

After this unit you can

  • Organise data and find maximum, minimum and averages using spreadsheet functions
  • Build a frequency distribution and draw a histogram, frequency polygon and ogives
  • Compute the mean, median and mode for grouped and ungrouped data
  • Compute range, standard deviation and coefficient of variation, and assign letter grades

PTU syllabus topics

  • Displaying maximum/minimum and tabular/graphical data
  • computing average marks
  • measures of central tendency for grouped and ungrouped data
  • frequency distribution construction (histogram, frequency polygon, frequency curve, ogive curves)
  • median and mode from grouped data
  • measures of dispersion
  • letter grade calculation
  • IQ distribution analysis
ProcessAnalysing a data set in the lab
  1. 1Enter and clean data

    Check for missing or wrong values

  2. 2Tabulate

    Build a frequency distribution

  3. 3Visualise

    Histogram, polygon, ogive

  4. 4Summarise

    Mean, median, mode, SD

  5. 5Interpret

    Write what the numbers mean

1

Topic 1

Displaying data in tables and charts

Enter data in columns with clear headings. Use Excel's built-in functions:

TaskExcel function
Largest / smallest value=MAX(B2:B31) / =MIN(B2:B31)
Total and count=SUM(B2:B31) / =COUNT(B2:B31)
Average marks=AVERAGE(B2:B31)
Median / mode=MEDIAN(B2:B31) / =MODE.SNGL(B2:B31)
Standard deviation=STDEV.P(B2:B31) (population) or =STDEV.S (sample)

Insert → Charts gives column, bar, pie and line charts for tabular data.

2

Topic 2

Frequency distribution and graphs

ProcessBuilding a frequency distribution in Excel
  1. 1Decide classes

    e.g. 0–10, 10–20 …

  2. 2Write upper limits as bins
  3. 3Count frequencies

    =FREQUENCY(data, bins) or COUNTIFS

  4. 4Add cumulative frequency

    Running total

  5. 5Draw graphs

    Histogram, polygon, ogive

  • Histogram: Insert → Statistic Chart → Histogram, or a column chart with no gaps between bars.
  • Frequency polygon: line chart through the mid-values and frequencies.
  • Ogive: line chart of cumulative frequency against upper limits (less than) or lower limits (more than). The point where they cross gives the median.

Example

COUNTIFS for the class 20–30: =COUNTIFS(B2:B51,">=20",B2:B51,"<30").

3

Topic 3

Central tendency for grouped data

For grouped data, build helper columns: mid-value (m), f × m, cumulative frequency.

  • Mean = Σfm / Σf
  • Median = L + [(N/2 − cf) / f] × h, using the class where cumulative frequency first reaches N/2
  • Mode = L + [(f₁ − f₀) / (2f₁ − f₀ − f₂)] × h, using the class with the highest frequency

Exam tip

Show the helper columns in your practical file — examiners check the working, not just the final answer.

4

Topic 4

Measures of dispersion

  • Range = =MAX(...) − MIN(...)
  • Standard deviation with STDEV.P, or manually with a column of (x − x̄)²
  • Coefficient of variation = SD / mean × 100; compare two data sets and state which is more consistent.
5

Topic 5

Letter grades and IQ distribution

Letter grades use nested IF or a lookup:

=IF(B2>=90,"A+",IF(B2>=75,"A",IF(B2>=60,"B",IF(B2>=40,"C","F"))))

For an IQ distribution, group IQ scores into classes (for example 70–80, 80–90 … 130–140), count frequencies, draw a histogram and observe that most scores cluster around 100 — the bell shape of the normal distribution. Compute the mean and SD of the scores.

Key terms

Bin
The upper limit used to count values into a class
Cumulative frequency
The running total of frequencies
STDEV.P
Excel function for population standard deviation
Helper column
An extra column used for intermediate calculations

Quick revision

  • MAX, MIN, AVERAGE, MEDIAN, MODE.SNGL, STDEV.P are the core functions.
  • Use FREQUENCY or COUNTIFS for class frequencies.
  • Grouped mean, median and mode need helper columns.
  • Nested IF assigns letter grades.

Important exam questions

Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).

Short-answer questions

  1. Q1.Which Excel functions give the mean, median and mode?
  2. Q2.What is a bin in a frequency distribution?
  3. Q3.How do you find the median from ogives?
  4. Q4.Differentiate between STDEV.P and STDEV.S.

Long-answer questions

  1. Q1.Construct a frequency distribution of marks of 50 students in Excel and draw a histogram, frequency polygon and ogives.
  2. Q2.Compute the mean, median, mode and standard deviation of grouped data in Excel, showing all helper columns.
  3. Q3.Assign letter grades to students using nested IF and analyse the grade distribution.

Stuck on this unit?

Message SBS on WhatsApp for help with Fundamental of Statistics Laboratory, or to ask about studying BCA at Synetic.

WhatsApp us