Excel Functions and formulas

SUM function

The SUM function adds all the numbers that you specify as arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.). Each argument can be a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.), a cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.), an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.), a constant (constant: A value that is not calculated. For example, the number 210 and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.), a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).), or the result from another function. For example, SUM(A1:A5) adds all the numbers that are contained in cells A1 through A5. For another example, SUM(A1, A3, A5) adds the numbers that are contained in cells A1, A3, and A5.

Syntax

=SUM(number1, [number2], [number3], [number4], ...)

The SUM function syntax has the following arguments:

• number1  Required. The first item that you want to add.
• number2, number3, number4, ...  Optional. The remaining items that you want to add, up to a total of 255 items.

AVERAGE function

Description

Returns the average (arithmetic mean) of the arguments. For example, if the range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.

Syntax

=AVERAGE(number1, [number2],...)

The AVERAGE function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• number1  Required. The first number, cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.), or range for which you want the average.
• number2, ...  Optional. Additional numbers, cell references or ranges for which you want the average, up to a maximum of 255.

MAX function

Returns the largest value in a set of values.

Syntax

=MAX(number1,number2,...)

Number1, number2, ...   are 1 to 255 numbers for which you want to find the maximum value.

MIN function Returns the smallest number in a set of values. Syntax =MIN(number1,number2,...) Number1, number2, ...   are 1 to 255 numbers for which you want to find the minimum value. RANK function Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.) Syntax =RANK(number,ref,order) Number   is the number whose rank you want to find. Ref   is an array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored. Order   is a number specifying how to rank number. If order is 0 (zero) or omitted, Microsoft Excel ranks number as if ref were a list sorted in descending order. If order is any nonzero value, Microsoft Excel ranks number as if ref were a list sorted in ascending order.  IF function Description The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(A1>10,"Over 10","10 or less") returns "Over 10" if A1 is greater than 10, and "10 or less" if A1 is less than or equal to 10. Syntax =IF(logical_test, value_if_true, [value_if_false]) SUMIF function Description You use the SUMIF function to sum the values in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 5. You can use the following formula: =SUMIF(B2:B25,">5") In this example, the criteria is applied the same values that are being summed. If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."  Note    To sum cells based on multiple criteria, see SUMIFS function. Syntax =SUMIF(range, criteria, [sum_range])

Netiquette

Netiquette from Andz Bass

Nature and Types of Software

There are two types of software:

-Systems software: the operating system.

-Applications software: the programs we use.

*Systems software is the control software that operates the hardware and allows the applications to run.

Operating Systems

-Operating Systems run in the background without the users being aware of it.

-It controls the inputs, outputs, interrupts, and storage of files as requested by the applications software.

Functions of the operating sytem:

-Managing memory, i.e. allocating memoryto more than one program running simultaneously. Some files may get moved to the hard drive temporarily. The place on the hard drive is stored in a directory for fast access when needed again.

-Interrupts, for Enter, Printer out of paper, or a hardware or software malfunction.

-Diagnostic checks on the system on Boot up, sending error messages where necessary.

-Selecting and controlling peripheral devices through small Driver programs.  

Utility Programs

-Systems software include other utility programs. 

-Virus checking and cleaning.

-Security using identification and passwords.

-File and hard disk management for efficiency and storage.

-Customising the computer to the users requirements, i.e. short cut menus from mouse click.

-Setting peripheral devices to the users requirements, i.e. setting the monitor resolution.  

Popular Operating Systems

Windows XP – home and business

Windows 2000 – business network use

Mac Operating System – GUI, graphics

OS/2 Warp – commercial work

UNIX – general purpose, mainframe

Linux – alternative to Windows

Dos – command driven 

Types of Operating Systems

Command driven: DOS.

       - the user has to type in the command

  -it has to be in the correct syntax, errors were common

  -more adaptable than Windows when used by an experienced user.

Windows, Icons, Mouse and Pull down menus.

  -originally developed by Apple for the Macintosh

  -later developed by Microsoft for Windows 3.1.

  -uses a mouse to click onto an icon to navigate to programs and files. 

Applications Software

ÒUsed for a specific purpose or application.

-Word processing;

-Numerical analysis and storage;

-Recording of data;

-Designing and graphics;

-Image processing;

-Presentations;

-Desk top publishing;

-Web design.

Three Groups of Application software

ÒGeneric:

-general purpose for business;

  -most common applications are Word Processing, Spreadsheet & Database.

Ò Bespoke:

-tailor made, specifically written for a specific application.

ÒSpecific Task:

-particular tasks for business;

         -most common are payroll, CAD, and stock control. 

Generic

ÒMost generic software for business comes in an integrated package such as: Microsoft Office, Lotus Smart-Suite, Coral Draw.

ÒThe cost of the integrated package is much less than the sum of the separate applications.

ÒMicrosoft Office is the most popular package used in business, it contains the applications: Word, Excel, Access, Power-Point, Front-Page. 

Bespoke

ÒSoftware that is specially written for an application.

ÒAn expensive but a perfect solution to a requirement when there is no suitable package available.

ÒOften a one off piece of software written for a large organisation as a perfect match to their needs.

Computer Viruses

Virus:
A computer virus is a computer program that can copy itself and infect a computer. The term "virus" is also commonly but erroneously used to refer to other types of malware.
Viruses can increase their chances of spreading to other computers by infecting files on a network file system or a file system that is accessed by another computer.

Malware:
Short for malicious software, is software designed to infiltrate a computer system without the owner's informed consent.
Malware includes computer viruses, worms, trojan horses, spyware, crimeware, most rootkits.

Worm:
is a self-replicating malware computer program. It uses a computer network to send copies of itself to other nodes (computers on the network) and it may do so without any user intervention. This is due to security shortcomings on the target computer. Unlike a virus, it does not need to attach itself to an existing program. Worms almost always cause at least some harm to the network, if only by consuming bandwidth, whereas viruses almost always corrupt or modify files on a targeted computer.

Trojan Horse:
A Trojan horse, or Trojan, is malware that appears to perform a desirable function for the user prior to run or install but instead facilitates unauthorized access of the user's computer system. "It is a harmful piece of software that looks legitimate. Users are typically tricked into loading and executing it on their systems", as Cisco describes.
A Trojan horse may modify the user's computer to display advertisements in undesirable places, such as the desktop or in uncontrollable pop-ups, or it may be less notorious, such as installing a toolbar on to the user's Web browser without prior mentioning.
Once a Trojan horse has been installed on a target computer system, a hacker may have access to the computer remotely and perform various operations, limited by user privileges on the target computer system and the design of the Trojan horse

Spyware:
Spyware is a type of malware that is installed on computers and collects little bits of information at a time about users without their knowledge. The presence of spyware is typically hidden from the user, and can be difficult to detect. Typically, spyware is secretly installed on the user's personal computer. Sometimes, however, spywares such as keyloggers are installed by the owner of a shared, corporate, or public computer on purpose in order to secretly monitor other users.

Crimeware:
Crimeware is designed to perpetrate identity theft in order to access a computer user's online accounts at financial services companies and online retailers for the purpose of taking funds from those accounts or completing unauthorized transactions that enrich the thief controlling the crimeware.

Rootkit:
Rootkits can target the BIOS, hypervisor, boot loader, kernel or less commonly, libraries or applications.
The most common forms of rootkit either damage the systems they occupy or redirect the systems' resources for purposes ranging from pranks to gratifying their authors' egos to crime. 

Virus vs. Worm:
A virus stays on your computer and your computer only. 
Worms crawl through networks.
Unlike a worm, a virus cannot infect other computers without assistance. It is spread via trading programs with others (file sharing programs, email). 

Spyware  vs. Adware:
Symptoms: Sluggish Pc, Increased pop-ups, homepage changes, strange search results.
Both are data miners, meaning they are looking for information. Both cause the above symptoms.
Can lead to identity theft. 
9 out of 10 pc’s are infected.
Good place to look for info? http://www.webroot.com/spywareinformation

Spyware Removal:
You can try to do it manually- but is often very difficult.  
Often it can disrupt major computer processes.
You can check out the following free programs:
AdAware
SpySweeper (both versions, free&pay)
NOTE: Please be careful when downloading these tools, some programs claim to remove spyware, but instead, come with their own spyware embedded.

Top Spyware Threats:
PurityScan
N-Case
Gator
CoolWebSearch
Tansponder
ISTbar/AUpdate
KeenValue
Internet Optimizer (bargain buddy)
Perfect Keylogger
TIBS Dialer

For more information on these, visit:
http://www.webroot.com/spywareinformation/spywaretopthreats/

Basic Computer Maintenance

LCD Monitor:
- Do not touch or attach items to the screen.
- Turn off to clean.
- Spray mild window cleaner on a soft cloth.
- Do not spray directly on screen.
- Do not use paper towels.

Wires:
- Avoid pulling or putting stress on your wires.
- Avoid putting objects on top of wires.
- Always disconnect cords by grasping the plug, not the cord.

CPU:
-Turn power off before cleaning.
- Any type of household cleaner for outside.
- Once a month take off cover and gently blow off dust.
- Always disconnect power cord by grasping plug- not cord.

Surge Protectors:
- Use a surge protector to protect electronic devices from power surges.
- Will not offer 100% protection.
- To be safe, never use computer during a storm.

CD-ROM Drives:
- Always use the button to open and close the drive.
- Do not use compressed air to clean the floppy diskette, CD, DVD or Zip drives.

CDs and DVDs:
- Use a soft cotton cloth
- Wipe against the tracks starting from the middle and wiping outwards
- Never wipe with the tracks
- Water w/ soap or rubbing alcohol

Keyboard:
- Do not eat or drink while typing on your computer.
- Turn off before cleaning.
- Spray Windex onto cloth, not on keyboard
- Use Compressed air

Mouse:
- Clean the top of your mouse like your keyboard
- Scrape guck off bottom with your fingernail

Installing Software:
- Do not use “bootleg” software
- Be sure to reboot after each program is installed
- Test your computer for problems before installing another program

Protect Against Viruses:
- Don’t open email attachments from people you don’t know.
- If your computer suddenly starts acting strange, you should run a virus scan.
- Be careful about disks and CDs from other computers- not everyone has virus protection.
- Use a firewall. A firewall is a piece of software or hardware that helps screen out hackers, viruses, and worms that try to reach your computer over the internet.
- Be careful about what you download from the Internet!
- Be sure that everyone who uses your computer follows these rules!

General Troubleshooting:
- If your computer does not start the first thing to check is the power source… is it plugged in?
- If your computer locks up (freezes) the first thing you should do is restart the computer.
- If your document fails to print you should check the
    a. paper supply, 
    b. power source, 
    c. paper feeder. 
- If you install a new software program on your computer and it does not work the most likely reason is that you do not have enough memory.LCD Monitor:
- Do not touch or attach items to the screen.
- Turn off to clean.
- Spray mild window cleaner on a soft cloth.
- Do not spray directly on screen.
- Do not use paper towels.

Wires:
- Avoid pulling or putting stress on your wires.
- Avoid putting objects on top of wires.
- Always disconnect cords by grasping the plug, not the cord.

CPU:
-Turn power off before cleaning.
- Any type of household cleaner for outside.
- Once a month take off cover and gently blow off dust.
- Always disconnect power cord by grasping plug- not cord.

Surge Protectors:
- Use a surge protector to protect electronic devices from power surges.
- Will not offer 100% protection.
- To be safe, never use computer during a storm.

CD-ROM Drives:
- Always use the button to open and close the drive.
- Do not use compressed air to clean the floppy diskette, CD, DVD or Zip drives.

CDs and DVDs:
- Use a soft cotton cloth
- Wipe against the tracks starting from the middle and wiping outwards
- Never wipe with the tracks
- Water w/ soap or rubbing alcohol

Keyboard:
- Do not eat or drink while typing on your computer.
- Turn off before cleaning.
- Spray Windex onto cloth, not on keyboard
- Use Compressed air

Mouse:
- Clean the top of your mouse like your keyboard
- Scrape guck off bottom with your fingernail

Installing Software:
- Do not use “bootleg” software
- Be sure to reboot after each program is installed
- Test your computer for problems before installing another program

Protect Against Viruses:
- Don’t open email attachments from people you don’t know.
- If your computer suddenly starts acting strange, you should run a virus scan.
- Be careful about disks and CDs from other computers- not everyone has virus protection.
- Use a firewall. A firewall is a piece of software or hardware that helps screen out hackers, viruses, and worms that try to reach your computer over the internet.
- Be careful about what you download from the Internet!
- Be sure that everyone who uses your computer follows these rules!

General Troubleshooting:
- If your computer does not start the first thing to check is the power source… is it plugged in?
- If your computer locks up (freezes) the first thing you should do is restart the computer.
- If your document fails to print you should check the
    a. paper supply, 
    b. power source, 
    c. paper feeder. 
- If you install a new software program on your computer and it does not work the most likely reason is that you do not have enough memory.

Computer Hardware - General Overview

Programming  -is about Software…why do we have to learn about Hardware and other stuff?

Computer Systems

Hardware
Software


Hardware=The physical components (electrical circuits) that make up the computer

Software=The computer programs (sequences of instructions) that tell the computer what
to do in response to a command or some event.

How do we communicate with computers?
Through the use of binary Language.

Components of a Computer
- Input Devices
- Precessing Devices
- Storage Devices (Primary and Secondary)
- Output Devices.

Computer Processing System:
1. Data is INPUT
2. Data is Processed
3. There is an OUTPUT

IPO - Input, Process, Output.
or commonly known as GIGO - Garbage In, Garbage Out.

Types of Computer
- Microcomputer
- Minicomputers
- Mainframe Computers
- Supercomputers

Minicomputers
-Multi-user systems
-100’s of workstations or terminals attached to central minicomputer
-E.g. EPOS (Electronic Point of Sale) Systems

Mainframe computers

Large Organisations –banks, building societies, airlines, governments

May have 1000’s of terminals –geographically remote locations
Could occupy a whole site
100’s of disk drives & hardware units
Location often kept secret! (terrorist attacks)

Supercomputers

Largest Category of computer
Cost MillionsMostly used by scientific and industrial research departments
NASA –government agenciesWeather Centres
Stock Exchanges
Large Commercial Organisations

The processor
 
 What is the equivalent (in humans) to the processor?
 The Human Brain.

How does the Brain process?
-receives an instruction (stimulus)
-decodes & produces an appropriate instruction
-executes the instruction!
-slap on the cheek –anger/revenge etc logged
-anger decoded –instruction to slap back
-slap back!
(OR TURN THE OTHER CHEEK?)

Processor:
-Brain of the computer
-Processes instructions

THREE STEPS
1) Fetches Instructions
2) Decodes Instruction
3) Executes Instruction

What is a Processor?
-Most computers use integrated chips….or integrated circuits for their processors or main
memory
-A chip is about 1cm square…and can hold MILLIONS of electronic components such as
transistors and resistors
-CPU of a microcomputer is a microprocessor
-Processor and MAIN MEMORY of a PC are held on a single board called a motherboard.
-Either chips or integrated circuits
-Integrated circuits are also found in almost every modern electrical device such as cars, television sets, CD players, cellular phones, etc.


 CHIP
A computer chip is an electronic circuit (consisting mainly ofsemiconductor devices, as well as passive components) that has been manufactured in the surface of a thin substrate of semiconductor material.


An electric circuit is made from different electrical components such astransistors, resistors, capacitors and diodes, that are connected to each other in different ways. These components have differentbehaviors.


The transistor acts like a switch
Resistor –resists electricity –so you can control current
Capacitor –controls electricity
Diode –also allows control of current and flow

Why Integrated Circuits (transistors)?
-Before –there were VACUUM TUBE
-They were huge –costly –bulky –easily burned out
-The first Computer –ENIAC –huge 30 ton monster! *use of 18000 or so vacuum tubes*

MAIN MEMORY
-The program currently being executed and the data used by the program is held in MAIN
MEMORY
-MM is divided into millions of individually addressable storage units called BYTES
-One byte can hold one character
-Or one byte can hold a code representing something –i.e a part of a picture, or a sound, or
 a program instruction.
-The total number of bytes in MM = The computers MEMORY SIZE.

Computer Memory Sizes
-1 KB (KB)    =1024 Bytes
-1 MB           =1024KB
-1 GB  =1024MB
-1Tb  =1024GB (about 1 trillion bytes)
 
 Main Memory
-Processing power and Main Memory in a computer has increased exponentially in the past year! It has grown at a rate that no one could have predicted.
-1980 –Microcomputers with 32K of memory were bought for thousands of homes and schools!

RAM and ROM
-There are two kinds of Memory
-RAM –Random Access Memory (MM)
(this is used for storing programs that are currently running and data that is being
processed)

ROM –Read Only Memory
(its contents are PERMANENTLY etched into the memory chip at the manufacturing stage. It
is used –for example –to load the bootstrap loader (the program that loads as soon as you
start the machine)

RAM
-Random Access memory
-Main Memory
-Stores info about applications that are open and data
-VOLATILE –
-When you switch off the machine, it disappears!!!

ROM
-Read only memory
-Non-Volatile (does not change)
-Programs that are necessary for the computer to run
-Boot up program
-etc

 Cache Memory

-This is a very FAST type of memory that is used to improve the spped of a computer, DOUBLING it ….in some cases.
-Acts as an intermediate store between CPU and MM
-It works by storing most frequently or recently used instructions so that it is fast to retrive them again.
-Cache is usually between 1KB and 512KB

Disk Storage
-Auxiliary storage is also called
-SECONDARY MEMORY
-BACKING STORE

EXTERNAL MEMORY
-The most common secondary memory (auxiliary storage) is DISK!

Hard disk  & Floppy Disk
All standalone PC’s come equipped with an in-built hard disk –the capacity of which is also measured in BYTES.

A typical hard disk nowadays is several gigabytes - - is used for storing software including the OPERATING SYSTEM..and other systems software.
Other types of Storage
-Flash Memory Cards
-Sticks
-Floppy discs
-Disks
 
INPUT AND OUTPUT devices
Input devices
-are the means whereby computers can accept data or instructions
-Keyboards, magnetic strip cards, smart cards, magnetic ink character recognition devices,

Output
–printer, VDU monitors, speakers, etc

There are two types of software:
-Systems software: the operating system.
-Applications software: the programs we use.
*Systems software is the control software that operates the hardware and allows the applications to run.

OPERATING SYSTEMS
-Operating Systems run in the background without the users being aware of it.
-It controls the inputs, outputs, interrupts, and storage of files as requested by the applications software.
APPLICATIONS SOFTWARE
ÒUsed for a specific purpose or application.
-Word processing;
-Numerical analysis and storage;
-Recording of data;
-Designing and graphics;
-Image processing;
-Presentations;
-Desk top publishing;
-Web design.

Generations of Computers

The Five Generations of Computers

First generation computers
(1940-1956) - Vacuum Tubes

- The first computers used vacuum tubes for circuitry and magnetic drums for memory.

—- They were often enormous and taking up entire room.

—- First generation computers relied on machine language.

—- They were very expensive to operate and in addition to using a great deal of electricity, generated a lot of heat, which was often the cause of malfunctions.

—- The UNIVAC and ENIAC computers are examples of first-generation computing devices.

 Second generation computers
(1956-1963) - Transistors

- Transistors replaced vacuum tubes and ushered in the second generation of computers.

- Second-generation computers moved from cryptic binary machine language to symbolic.

- High-level programming languages were also being developed at this time, such as early versions of COBOL and FORTRAN.

- These were also the first computers that stored their instructions in their memory.

 Third generation computers
(1964-1971) - Integrated Circuits

- The development of the integrated circuit was the hallmark of the third generation of computers.

—- Transistors were miniaturized and placed on siliconchips, called semiconductors.

—- Instead of punched cards and printouts, users interacted with third generation computers through keyboards and monitors and interfaced with an operating system.

—- Allowed the device to run many different applications at one time.

 Fourth generation computers
(1971-present) - Microprocessors

 

- The microprocessor brought the fourth generation of computers, as thousands of integrated circuits were built onto a single silicon chip.

—- The Intel 4004 chip, developed in 1971, located all the components of the computer.

—- From the central processing unit and memory to input/output controls—on a single chip.

—- Fourth generation computers also saw the development of GUIs, the mouse and handheld devices.

Fifth generation computers
(present and beyond) - Artificial Intelligence

- Fifth generation computing devices, based on artificial intelligence.

—- Are still in development, though there are some applications, such as voice recognition.

—- The use of parallel processing and superconductors is helping to make artificial intelligence a reality.

—- The goal of fifth-generation computing is to develop devices that respond to natural language input and are capable of learning and self-organization.