CHAPTER 06
NUMBERS AND DATA
AMIR’S PARCEL
Slides by
Ms. Abeera Sikandar
lecturer
bahria college anchorage
computer sciences
• In this topic, we will see the data about parcel delivery company.
Following is the spreadsheet data we will see:
• What is data table?
‘Data’ is the name for facts and figures. A spreadsheet allows to organize data
in a structured format called data table.
• How data becomes information?
Data alone doesn’t mean much. To make it information it is sorted, analysed and
formatted. Then it becomes meaningful as well. For example:
Data: 2 0 1 0 0 9 2 1 21 September 2010
The rows of a table are called
records.
• Properties of table
Table is made up of rows and columns.
Rows are called records.
Columns are called Fields
The columns of a table
are called fields.
• Properties of table
Records Fields
1. Each records have several fields. 1. Each field stores one fact.
2. A record stores all data about 2. The heading of the column is
one thing, event or person. field name. It explains what data
is in the field.
Primary key field
• Properties of table
Data tables have a field which holds different/unique value in each record. This
is called primary key.
It identifies each record.
In this table the primary key is
• How to make data table?
To turn spreadsheet into data table, follow the following steps:
1 Drag the pointer across all the cells in the spreadsheet. Now they are highlighted.
2 click to ‘Format as table’
3 Choose the colour and style for the table.
What is sorting?
Sorting means putting data the records of the data table in order. Field is
selected to sort the table.
Types of sorting:
Alphabetical (A Z or Z A)
Numerical (ascending or descending)
What is sorting?
How can we sort
numerical data table in
excel ?
1. Click on the arrow next to
heading of field you want
to sort table with.
2. Choose A Z for
ascending order sorting.
What is filtering? How does filtering work?
1. Filter means selecting records from a data table to see them. To see
specific records, we apply rules in spreadsheets which are called filter
criteria.
2. The records that fulfill that filter criteria will appear only. Rest will be
hidden. Once the filter criteria is off. Those records appear again.
What is filtering?
How will you apply a filter on Amir’s delivery parcel on ‘Deliveries’ field
which are not delivered yet?
1. Click the arrow next to the field to filter.
2. Click to remove the tick next to Select all.
3. Click to add a tick next to ‘No’ . Then click on OK.
What is filtering?
Why are data checks important?
1. Data checks are to ensure the accuracy in a data. Data with errors can cause big problems.
So data checks are applied that help to spot mistakes.
Type of Data check: Validity check.
Validation means to decide on rules called validation criteria. The software checks all the data
against the criteria. If the data does not match the criteria, then the data is wrong.
How to add validation to a data table?
How will you add validation in the weight field of Amir’s parcel data table?
In the ‘weight’ field, we’ll add ‘number validation’ :
1. The weight must be a whole number.
2. The weight must be bigger than zero.
How to add validation to a data table?
How will you add validation in the weight field of Amir’s parcel data table?
Steps to add validation:
1. Select the cells that store ‘weight’ data.
2. Click to start the ‘Data tab’.
3. Click on ‘Data validation’.
4. Select ‘Data validation’ from the options list.
5. Select ‘Whole number’.
6. Select ‘greater than’.
7. Type ‘0’.
8. Click on OK.
How to check errors?
How will you check errors in the weight field of Amir’s parcel data table, when criteria is:
1. The weight must be a whole number.
2. The weight must be bigger than zero.
Steps to check errors:
1. Select the cells that store ‘weight’ data.
2. Click to start the ‘Data tab’.
3. Click on ‘Data validation’.
4. Select ‘circle invalid data’.
It will circle all the data that are not following validation criteria.
List Validation List validation is the method for input data in
What is list validation? simplified way. The user does not need to type
data. He chooses the right value from a list
Inputting data is an important task. But data entry by
typing can be problematic in following ways:
Why list validation is used?/ It can take long time to input all data.
It is easy to make mistakes while typing.
What are problems with data typing?
Data may be inconsistent.
List validation as many advantages:
Choosing from a list can be quicker than typing.
What are the advantages of list validation? There is less chance of making mistakes.
The data is consistent.
List Validation
How to add list validation?
1. Choose List
2. Open the ‘Data tab and click on ‘Data Validation’.
Click on arrow button to select the fields that
will make list.
3. Select the list of places.
4. Click on the arrow button again.
List Validation
How to use data list for data entry?
1. Enter a new pickup from.
2. Click on the arrow to open the list.
3. Select a place from the list.
Calculated field
The field where we enter the formula for some calculation and application gives the answer for
every record in the table is called calculated field.
How to add a formula
In our data table, we have a field called ‘weight’. Now we will apply some calculations for
charges applied for each delivery. For this, first we will add a new field named as “Charges”.
Then we will apply a following formula for each parcel charges depending on its weight”
𝒘𝒆𝒊𝒈𝒉𝒕
Type the name of
𝟓𝟎
field
We will add formula into charges field.
Each formula begins with = sign.
Enter an ‘=‘ sign in
the first row of
data table
How to add a formula
3. Click on the first cell in
‘weight’ field.
4. The app puts the name of field
in formula.
5. Type /50 at the end of
formula.
How to format a field as a currency
1. Select a column.
2. Click to choose the currency format.
3. The numbers are shown as currency.
Logical Test
Logical test checks a statement and finds out if its true and false.
The logical test on the statement ‘ This parcel has been delivered’ can have the answer.
1. True (Yes, parcel has been delivered).
2. False (No, it has not been delivered).
Type the name of
How to start an IF formula field
New field will be added called “to pay”. This field will show how much customer has to pay.
Logical test begins with an ‘if’ formula
Start the forumla by
typing =IF into the cell.
How to enter logical test
The test is: “Is the answer in the ‘Delivered’ field, “YES”?
1. Click on the ‘Delivered’ field, The field name will be added to the formula.
2. Type “YES” to finish the test.
How to complete the formula
The test is: “Is the answer in the ‘Delivered’ field, “YES”? Then charges will be applied.
1. Type a comma.
2. Click on the “Charge” field.
3. Type another comma, Then type ‘0’. Finish with a close bracket.
`
