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.