Excel: Names and Functions

Excel: Names and Functions

Excel saves files as workbooks, which contain: 1) worksheets, 2) macros, 3) charts

Spreadsheet is a general term for categorizing a specific type of application, but worksheet is the sheet containing data in a workbook or in the spreadsheet program.

Names are different from labels names may be provided to cells containing or ones that will contain data and are used in formulas and functions rather than cell references, whereas labels are just simple ids to the general data in a row or column.

Names fall into two categories:

1)Booklevel (workbook level) applicable to all worksheets in that workbook

2)Sheetlevel (for a specific sheet) applicable to one sheet only

Preference is to use sheetlevel names as they can be re-used for other sheets, whereas booklevel names cannot be re-used.

To develop names:

Highlight the cell or cell range to name, then either: 1) use the Formula Tab and select Define Name, select workbook or sheet, and type in a specific name; 2) use the Name Box, click on the cell reference in that box and then enter the actual name to replace that reference.

The Name Manger in the Formula Tab can be selected to see the names youve developed and their category: booklevel or sheetlevel

Functions form: name, parens, inside parens is the argument or arguments, as:

pmt(interest, months, amt. financed)

Nested function: one function inside the parens of another, as

ABS(pmt(interest, months, amt. financed))

Functions maybe categorized as to what they do, see the formulas toolbar

Functions to know:

A6 may use ABS, SUM, PMT

A7 may use these and more

Date & Time:

Today()

Year()

Lookup and Reference:

Vlookup(lookupvalue, where, col-index)

Hlookup(lookupvalue, where, row-index)

Lookup(lookupvalue, where, vector, result_vector)

Two examples (idealized):

Vlookup:

Employee_Table

Name / Address / Salary
Ackers / 33 N. 8th / 53000
Ocean / 42 S. Blvd. / 29000
Rickers / West 8th St, Apt. 6 / 34000

Vlookup(Rickers, Employee_Table, 3) result returned would be 34000

Hlookup:

Vacuum _Table

Properties / Eureka / Hoover
Weight (lbs) / 3.8 / 3.9
Motor (hp) / 2.6 / 3.3
Cost / 224 / 319

Hlookup(Hoover,Vacuum_Table, 3) result returned would be 3.3

Logical:

If(test, iftrue, iffalse)

Example (idealized):

If:

Name / Address / Salary
Ackers / 33 N. 8th / 53000
Ocean / 42 S. Blvd. / 29000
Rickers / West 8th St., Apt. 6 / 34000

If(Salary<30000,Salary+2000,) result returned would be 31000 for Ocean

Math:

Sum(range), Sum(individual cells)

Sumif(rangeck,test,rangesum)

Example (idealized):

Sumif:

Name / Address / Salary / Beneifts
Ackers / 33 N. 8th / 53000 / 8000
Ocean / 42 S. Blvd. / 29000 / 4500
Rickers / West 8th St., Apt. 6 / 34000 / 6000

Sumif(Salary,>30000,Benefits) result returned would be 14000

Average(range), Average(individual cells)

Round(number or cell reference, number of digits)

Statistical:

Count(range)

Countif(rangeck,test)

Example (idealized):

Counif:

Name / Address / Salary
Ackers / 33 N. 8th / 53000
Ocean / 42 S. Blvd. / 29000
Rickers / West 8th St., Apt. 6 / 34000

Countif(Salary,>30000) result returned would be 2

Max(range or specific cells)

Min(range or specific cells)

Correl(array1,array2)

Example (idealized):

Correl(age,gpa)

Returns a Corr Coefficient between: 1 and -1

Meaning: 1 is absolute positive relationship, 0 No relationship, -1 absolute negative relationship

So: .95 is very close to 1 and so it is a positive relationship, just not assured

Likewise: -.95 is very close to -1 so it is a negative relationship, just not assured