

spreadsheets

Entering data in a worksheet

cell data entries

Entries into a worksheet can be classified into four categories

 Labels

 Values

 Formulae

 Functions
 
Labels
 Any text or alphanumeric characters entered in a cell are viewed as
labels by the spreadsheet program
 Labels are used as row or column headings usually to describe the
contents of the row or column
 numbers can be formatted so that they can be used as labels. To
achieve this add an apostrophe just before the most significant digit
 Labels are aligned to the left of the cell and cannot be manipulated
mathematically.
 
Values
 These are numbers that can be manipulated mathematically

 They may include currency, date, numbers (0-9), special symbols or text that
can be manipulated mathematically by the spreadsheet.

labels
values
 
Formulae
 These are user designed mathematical expressions that create a
relationship between cells and return a value in a chosen cell
 a formula must start with an equal sign.

 Excel formulae use cell addresses and the arithmetical operators like plus
(+) for addition, minus (-) for subtraction, asterisk (*) for multiplication
and forward slash (I) for division.

formulae
 
Functions
 These are inbuilt predefined formulae that the user can quickly use
instead of having to create a new one each time a calculation has to be
carried out

function
 
Cell referencing
 A cell reference identifies a cell or a range of cells on the worksheet and shows
Microsoft Excel where to look for the values or data needed to use in a formula
 Microsoft Excel uses the A 1 cell referencing style. This means that a cell is
identified by its column label followed by the row number
 E.g. A1, H21

 There are three types of cell referencing used when creating formulae for
manipulating cell content
 Relative cell referencing
 Absolute cell referencing
 Mixed cell referencing
 
Relative referencing

 is a cell reference, which changes automatically when the formula is copied to

another cell or range
 Example would be if you type the formula =Al+Bl in cell Cl. If the same
formula is copied to cell C2 the formula automatically changes to =A2+B2.
 
Absolute referencing

 is a cell reference, which does NOT change when the formula is copied to
another cell or range
 To make a formula absolute, add a dollar sign before the letter and/or number,
E.g. =$A$1+$B$1
 
Mixed referencing

 Is a combination of relative and absolute reference eg $A1, B$45

 
Block operations

 A block of selected cells in a worksheet is referred to as a range



Basic functions and formulae

 
Statistical functions.

=Average(): It returns the average (mathematical mean) of a set of values

which can be numbers, arrays or references that contain numbers. If the value
20 is in cell DI0 and 30 in El0 then:
=Average(Dl0:E10) returns 25 as the average of the two values.

=Count(): Counts the number of cells that contain values within a range e.g.
= count (AI0: EI0) many return a value 5 if all the cells have values.
=Max() : It returns the largest value in a set of values. It ignores text and logical values
= Max (Al0:EI0) will return the maximum value in the range.

=Min(): It returns the smallest value in a set of values. It ignores text and logical values
= Min (AI0:EI0) will return the minimum values in the range.

=Mode(): It returns the most frequently occurring value in a set of values. e.g.
= Mode (AIO:ElO)

=Rank(): Returns the rank of a number in a list by comparing its size relative to the
others. For example if A 1 to AS contains numbers 7, 3.8,3.8, 1 and 2 then RANK (A2,
Al :A5,1) returns 3 while RANK (AI, AI:A5,I) returns 5. The general format is RANK
(number to be ranked, range, order).
 
Logical functions
=If(): It returns a specified value if a condition is evaluated and found to be true and another
value if it is false. If (marks > 50, "pass", "fail") will display a pass if values are more than 50 else
it will display fail.

=Countif(): Counts the number of cells within a specified range that meet the given condition or
criteria. e.g. suppose A10 : E10 contains eggs, beans, beans, eggs, eggs, countif(AI0:EI0, "Eggs")
will return 3.

=Sumif() : It adds values in the cells specified by a given condition or criteria. e.g. For example if
AIO to ElO contains values 10,50,60, 30, 70, to sum all values greater than 50 = Sumif(AI0:EI0,
">50"). This returns 130.
 
Mathematical functions

=Sum(): adds values in a range of cells as specified and returns the result in the
specified cell. e.g Sum (AI0:EI0) adds values in the range

= Product(): multiplies values in a range of cells and returns the result in the
specified cell. For example if A 10 has 30 and BI0 has
 
Arithmetic formulae - using operators

Operator Function

+ (plus) adds values as specified

- (minus) . subtracts values as specified
* (multiplication) multiplies values
/ (division) divides values.
( ) parenthesis encloses arguments to be calculated first.
 
Data organization in a database

One of the functions of a database system is to organize data

for easy access, retrieval and manipulation. Data is organized
from the simplest form called a field to a very complex
structure called a database.

Fields

A field is a character or a logical combination of characters

that represent data item. For example, in a class list, the
student name is a field.

Records

This is a collection of related fields that represent a single

entity. An example of a record is the student report card that
may contain the. student's name, admission number, class,
total marks, average and grade.

Tables

A table is a collection of related records. For example, the

students' file in a school database contains the details of all
the students in the school.

Database

This is the highest in data organization hierarchy that holds all

related files 'or tables. For example, a school database may
contain students and staff tables/files.


Description of field data types


Text/ short text

This type includes alphabetic letters, numbers, spaces and

punctuation. Use this data type for fields that do not need to
be used for calculations such as names, places, identification
numbers etc. This type of field accommodates a maximum of
255 characters.

Number

These are fields made up of numeric numbers 0 to 9 that are

to be manipulated mathematically.

Memo/long text

This is a field made up of alphanumeric (both alphabetic and

numeric) data. Instead of using Text use this data type if you
need to enter several paragraphs of text because it
accommodates a maximum of 32 000 characters.

Date/Time

Used to identify a field as either a date or time. This is

because date/time values can be manipulated mathematically
in a database. For example, you can calculate the age of a
person from the date of birth to the current data.

Currency

Used to identify numeric values that have decimals or

fractions. Use this data type especially when dealing with
monetary values such as fees balance, amount sold etc.

AutoNumber

This is a numeric value used if you wish Ms Access to

automatically increment the values in a field. For example
when entering a list of forty students and you have a field
labelled StudNumber, the numbers will increase by one every
time you enter a new record.

Yes/No

This is a logical field where an entry is either a yes or a no,

true or a false. For example a field may require you to answer
whether you are a male or a female

OLE Object

OLE stands for Object linking and Embedding. This type of

field is mostly used with graphical user interface applications
for inserting graphical objects such as pictures, drawings
charts etc. - - --­
 

Field properties

As you create more and more complex tables, you will

find a need to use the field properties to specify finer
details related to fields and the table entries expected. The
field properties depend on the type of-the field selected.
 
Field size

This allows the user set the number of characters in a field instead of
the default 50 for text fields for numeric field’s integer and long
integer, Byte, Single and Double.
Integer and longer integer: Accept numbers with no decimals.
Byte: Can only accept a number from 0-255.
Single and double: Accept numbers with decimals. Single
accommodates up to 38 decimal places while double, accommodates
up to 308.

Format

Determines how information appears on the screen and when

printed. For example, you can format a number to scientific,
currency, percentage or general format.

Decimal places

For number and currency fields you can specify the number
of decimal places.

Input mask

Input mask automatically formats the field entry into a

specified format. F or example, if you enter a number such as
02000 I 00409874 and the input mask is set as 000-(00000)-
000000, it is automatically displayed as 020-(00100)-409874.
This property is mostly used to format phone and address
entries.

Caption

This is a more descriptive name for a field to be used in a

table or a form display. For example the caption for StuName
could be Student Name.

Default value

This is a value that will appear automatically in the datasheet

or form if ' nothing is entered by the user to change it. For
example = Date -( ) automatically displays the current date in
a date field.

Validation Rule

Logical expression restricts the values to be entered in a field.

'For example, if you want to restrict marks entered in a field
to values between, zero and a hundred, type >=0 And <= 100.

Validation Text

The message that appears once the validation rule is violated.

For example, you may create a validation text for the above
validation rule I to display "Enter a number between 0 and
100" whenever the user enters I a value outside this range.

Required

Determines if an entry must be made in the field before you

proceed to the next field or record. For example, if a primary
key is required, you must enter before you proceed.

Allow Zero Length

This allows the user to proceed without making any entry in

the field set as Zero length.

Indexed

An Index, facilitates the organization of records for easy

search. A primary key is an example of an index set to No
duplicates to control double entry of a record (redundancy).

QUERIES

 Is a database feature that enables the user to display specific

records as well as perform calculations on fields from one or
multiple tables
 
Types of queries
 SELECT QUERY
 Used for searching and analyzing data in one or more tables

 ACTION QUERY
 Used to make changes to many record at once
The four types of a action queries found in Microsoft Access are:
1. Update - Updates data in a table.
2. Append query - adds data in a table from one or more tables.
3. Make table Query - Creates a new table from a dynaset.
4. Delete Query - Deletes specified records from one or more tables.

Parts of the query grid

 
Specifying the search criteria

 In order to search for a particular set of records, you have to enter a conditional statement or
statements in the criteria row. For example if you have a table called employees with one of
the fields as salary, you can display all the employees earning more than Shs. 5,000, by typing
>5000 in the criteria row, salary column.

 To define criteria, use either relational or logical operators. Relational operators include less
than «), greater than (», greater than or equal to (>=), less than or equal to «=), not equal to «»
and equal to (=). Logical operators include AND, OR and NOT.
 

 Use AND to display values in a specific range. For example, to display records from the employees
table with salaries above 4000 but less than 6000, type, >4000 AND < 6000 on the criteria row in the
salary column. All the employees who meet this condition will be displayed.

 Use OR if you wish to get either one of two values. For example if you wish to get those employees
either in Nairobi OR Embu.

 If you want to display data in a particular range use the word Between. For example, instead of typing ,
>4000 AND < 6000, type Between 4000 And 6000.
 

 If you want to list all records except those that you do not want to see use NOT. For example if
you type NOT 6000 in the salary column of the employees table, all employees records will be
displayed except. those with their salary as 6000.

 To display records you are not sure of the field name but at least you can remember a few
characters, use LIKE and the wildcards. Wildcards are special symbols mostly an asterisk and a
question mark used in place of other characters. For example, to display all names starting with
"Sm" followed by any other character, type Like Sm? Like */*/1993 lists records created in
1993 regardless of the day or month.