Microsoft Excel - Formulas and Functions ( TestOut 4.1 - 4.2)

Invalid Name Error

#NAME? Appears when a formula or function cannot find the referenced data it needs to complete the calculation. This could be caused by a few different things, such as a misspelling in the formula name or an invalid reference.

Converting from Relative to Absolute References

A dollar sign can be added in front of the row, column or both to fix that location.

Active Formula

A formula that is currently being typed into a cell.

Argument

A number or cell that Excel uses in calculating the formula.

Formula Notation

Any arguments that are required for the formula to work are listed. Any optional arguments will be listed in brackets.

Calculation Options

By default, Excel will automatically recalculate its formulas whenever you make a change. You can opt to change this tp recalculate only when you click "Calculate Now".

Show Formulas

Can be useful to see the underlying formulas in the spreadsheet instead of the results.

TEXTJOIN

Concatenates multiple items using a delimiter, such as a comma, space, or period. The first argument defines the delimiter. The second argument determines whether the function will ignore blank cells. The remaining arguments are the items to be concatenated.

COUNT

Counts the numbers in a range of cells. Does NOT count text or blank cells.

Error Message

Displays a green triangle in the top left corner of the cell when it detects an potential error. You can then click on the yellow exclamation mark to determine which error it is possibly detecting. You can then opt to change the formula or determine is it correct and IGNORE the error message. Always investigate the cell in question and determine if a problem exists.

AVERAGE

Finds the arithmetic mean of a range of cells.

Date & Time

Functions are useful when performing calculations that you'd normally do on a calendar. For example, the DAYS function returns the number of days between two specified dates.

Financial

Functions that are mostly used to calculate financial information, such as interest rates, payments, and loan amortization.

Lookup & Reference

Functions that are used to find individual values within a spreadsheet. When using a sheet with hundreds of rows, the VLOOKUP function allows you to quickly find the information you're looking for.

Logical

Functions that are useful for when you want to find or analyze data based on some condition. The most common logical function is the IF function, which will perform an operation only if the desired condition is found.

Text

Functions that manipulate, format, and make calculations from text. The CLEAN function, for instance, removes all non-printable characters from a string of text.

Math & Trig

Functions that perform the same calculations as an advanced calculator. You can use the LOG function to find a logarithm; SIN, COS, and TAN to find trigonometric values; and even ROMAN to convert a number into Roman numerals.

Spill Functions

Functions that return values to a range of cells. The function will only be able to be edited/deleted from the original cell. All other cells containing data from the Spill Function will be greyed out.

Relative Reference

Instead of pointing to a specific cell, they point to a location relative to the location of the formula. When using this, the relative reference updates to its new location as the formulas is copied to other cells.

Error Checking

It scans your current worksheet for common errors and displays recommendations for resolving the potential errors.

SUMIF AVERAGEIF COUNTIF

Logical Functions that condense large data sets into one number summaries. formula structures: =SUMIF(range,criteria,[sum_range]) =AVERAGEIF(range,criteria,[average_range]) =COUNTIF(range,criteria)

Insert Function Dialog Box

Opens the Insert Function dialog box, where you can search for and insert any function. This is especially useful if you are unfamiliar with functions.

Absolute Reference

Points to a specific cell. As a formula is copied to other cells, that cell remains fixed in the formula.

LOWER

Reformats text to all lowercase letters.

UPPER

Reformats text to all uppercase letters.

PROPER

Reformats text to make the first letter of each word uppercase and all other letters lowercase.

LEFT

Return a specified number of characters from the left of a text string. The first argument determines the number of characters, and the second argument supplies the text.

RIGHT

Return a specified number of characters from the right of a text string. The first argument determines the number of characters, and the second argument supplies the text.

RANDBETWEEN

Returns a random integer between two specified numbers (including the numbers themselves).

SEQUENCE

Returns an array of cells with a number sequence. This array can be any number of rows and columns, can start at any number, and can increment by any number.

MID

Returns characters from the middle of a text string, given a starting position and length. The first argument specifies the text. The second argument is a number that determines which character is the first in the returned string, and the third argument is a number that determines how many characters the function will return.

MAX

Returns the highest number in a range of cells. Useful when summarizing very large amounts of data.

MIN

Returns the lowest number in a range of cells. Useful when summarizing very large amounts of data.

LEN

Returns the number of characters in a text string.

Trace Dependents

Shows all cells that have a calculation based on the selected cell.

Formula Bar

Shows the underlying formulas of the selected cell.

UNIQUE

Takes an array of cells as an argument and returns a new array containing one of each of the unique values in the original array.

SORT

Takes an array of cells as an argument and returns a new array of cells where the values are sorted, usually alphabetically or numerically.

CONCAT

Takes multiple inputs and concatenates them into a single string. It can accept as arguments text, references to cells, and references to ranges.

Cancel/Enter

The Cancel and Enter buttons perform the same action as pressing Esc or Enter on your keyboard, respectively. When entering a formula, it is important that you either click the Enter button or press Enter on your keyboard when you're done. If you don't, Excel will think that you're still working on your formula when, in fact, you're trying to do something else. If you ever mess up a formula and want to start over, press Esc.

AutoSum button

The fastest way to add simple functions to your spreadsheet. The functions it provides are SUM, AVERAGE, COUNT NUMBERS, MAX, and MIN.

Inconsistent Formula Error

The formula in the cell doesn't match the pattern of formulas in nearby cells.

Insert Function

This dialog box allows you to search for and insert functions.

#SPILL!

This shows a Spill Error. All cells that will be filled with data must be empty. This error shows that the function can't spill properly while something is in the way.

Mixed Reference

When either the row/column is an absolute reference and the column/row is a relative reference.

AutoSum

When using this feature, Excel will guess which numbers you want to add and then insert the SUM function into the selected cell. Even though AutoSum is pretty good at guessing, you should always double-check the generated function before accepting it.

Functions List

When you begin typing a formula in a cell (by typing =), it changes into a list of common or recently used functions. This can be helpful when making formulas that require multiple functions.

Trace

Will show all cells that are used in the calculation of the selected formula.

Defined Names

You can assign a name to selected data. This can make it easier to insert formulas. Names can be given to individual cells, ranges, and tables.