ISTM Exam II Excel Functions, TAMU Phinney

How do you make a cell absolute?

$A$1 the column reference is relative and will change when copied and pasted to cells in other columns in the worksheet. The row reference is absolute and will remain constant when copied and pasted to other cells.

Conditional Functions all you to do what?

CONDITIONAL FUNCTIONS allows you to perform calculations where the cell references used to complete the calculations depend on the values of other cells in a worksheet

What does 'counta' count?

COUNTA(value1,[value2]...) counts the number of cells that are not blank

Every good spreadsheet model will have three common elements?

Changeable inputs, outputs, and intermediate calculations.

_______ ________ can calculate an outcome for the model based on each state of the input variables

DATA TABLES we can specify a number of different possible states for one or two input variables and construct a table in a worksheet that calculates an outcome for the model based on each state of the input variables

The 'day' function shows you what?

DAY (seria_number) returns to the day portion of a date

Which two chart tools allow you to design and modify a graph?

Design and Format

What does Effect do?

EFFECT (nominal_rate, nperv) calculates the annual percentage for an interest rate given the number of times per year that interest is charged

Which function allows you to see which cells are referenced?

Excel has some good troubleshooting tools to aid in the process of identifying and correcting errors. Specifically, the "TRACE PRECEDENTS" feature allows you to see which cells are referenced in a particular formula or function.

What does FV calculate?

FV (rate, pmt ,pv [fv],[type]) calculates the future value of an investment given the interest rate, number of payments to be made, and the amount of payment.

What are Goal seeks limitations?

GOAL SEEK has some important limitations. First, you can only specify one input and one outcome variable to change as part of the analysis. Also, you must specify an exact amount for the desired outcome and the cell must contain a formula

What does the "average" function tell you?

It [AVERAGE (number1,number2...)] calculates the simple average of a set of numbers

What does the 'count' function do?

It [COUNT(value1,[value]...)] counts the number of cells that contain numbers

Why is goal seek used?

It allows the user to specify the desired value for an outcome cell and select an input cell that Excel should modify to achieve the desired outcome

What does the "trace dependents" show you?

It allows you to see which other cells reference a particular cell.

Building models allows you to?

It allows you to use spreadsheets to make good decisions based on sophisticated and relevant analysis of the problems you are trying to solve.

What does 'quick sorting' do?

It is an easy way to rearrange data table, but it is limited to sorting just one column at a time

How are monthly payments calculated?

It is calculated using the PMT function that accounts for the loan amount, interest rate, and term of the loan as defined in the inputs section of the model

Who developed Boolean Logic?

It is rooted in Boolean algebra that was developed by George Boole in the 1800s

What does max payment show?

It is the total payment the applicant can make and still be approved for the loan. It is calculated as the acceptable income servicing debt minus the other monthly debts applicant must pay

What does a chart legend help with?

It is used to help the user differentiate between the various data series depicted in a chart

What is the "substitute" function used for?

It is used to replace characters in a block of text with other words.

What does "left" function do?

It returns a specified number of character from the beginning of a block text. There are two arguments for this function; (text,[num_chars]).

What does the "mid" function do? And how do you do it?

It returns a specified number of characters from the middle of a block of text. The text argument of the MID function allows you to specify the block of text from which you wish to return some characters. You specify where to beginning returning characters from within the block of text using the start_num argument. Finally, the num_chars argument is used to specify how many characters to return

What does "len Text" refer to?

It returns the length of a block of text as the number of characters, including spaces, within that block of text. The len text function requires only one argument and expects that argument to be a block of text.

Search does what?

It returns the position of a specified character or a set of characters, within a block of text. There are three arguments of the SEARCH FUNCTION.

Scenario manager is useful for?

SCENARIO MANAGER is useful for examining the impact of several inputs to a spreadsheet model changing at the same time

What function lets you compare categories?

STACKED COLUMN CHART is used in situations when you need to compare categories (or the same category twice over time) and the categories can be decomposed into smaller common segments across the columns

What are Excel's three specific tools for conducting what-if analysis on your spreadsheet models

Scenario Manager, Data Tables, and goal seek

Time Functions show?

Show the hour, minute, second

The "Average If" function shows what?

The AVERAGEIF function is used to calculate the total for a set of values that match a specific criteria

What does the Countif function do?

The COUNTIF function allows you to determine the number of cells within a range of cells that contain a specific value.

What does the 'if' function do?

The IF FUNCTIONS builds on Boleean logic to help you construct flexible formulas

The "Sum If" function shows what?

The SUMIF function is used to calculate the total for a set of values that match a specific criteria

What does the "weeknum" function do?

The Weeknum function (serial_number,[return_type]) returns to the week of the year for a date

The "today" function shows the?

The current date

Acceptable income service debt is?

The maximum dollar amount that the applicant can spend each month on debt to be approved for the loan. It is calculated by multiplying the monthly income by the acceptable percentage of monthly income allowed to go towards debt defined in the model inputs.

How do you find monthly income?

The monthly income is the annual income divided by 12

What does "and" allow you to do? What is it used in?

The operator "and" combines Boolean expressions to determine if ALL of the combined expressions are TRUE

The year function shows you what?

The year portion of a date ( a number between 1900 and 9999)

What do Themes do?

They apply to quickly add colors and other style effects to your worksheets

When do you use line charts?

They are particularly useful for identifying trends in a dataset. Line charts are typically used to compare categories items over time.

What are stacked column charts used for?

They are used to compare categories of items or the same category over time, breaks the column bars into segments that represent subcategories that are consistent across the columns

Pivot Tables are used to?

They are used to organize and summarize large amounts of data, also allows us to change how we summarize data to look at it in different ways

Conditional Functions are used too?

They are used to summarize information in a large data table

When should models be used?

They can be very useful for decision-making in many different settings.

What are column charts and bar charts used for?

They used to compare different categories of items or the same category over time

When are line charts used?

To compare the values of a particular category over time.

What function makes all the characters capitol letters?

UPPER FUNCTION is used to convert the characters in a block of text to all upper case letters

What are the two main reference functions?

Vlookup and Hlookup

What do relative cell references do?

We call cell references that automatically update when a formula is pasted to a new cell

When are column charts not useful?

When comparing large number of categories

How do you only print a specific part of your wooksheet

You may also find that there are parts of your worksheets that you do not wish to include in a report printout. You can define the parts of the worksheet that you want to print by setting the PRINT AREA of the worksheet

The 'max' value refers too?

[MAX (number1, number2...)] returns to the largest value in a set of numbers

'Weekday' shows you what?

[WEEKDAY (serial_number, [return_type]) returns the day of the week for a date

Workbooks do what?

contains the actual data and calculation; is organized into a collection of cells arranged in the form of a table

The "concatenate" function is used to?

is used to combine blocks of text.

The "not" function is used to?

is used to evaluate negation, or the opposite of a Boolean expression. The NOT FUNCTION is used because it is sometimes easier to define what something is not that it is to define what it is.

Cells store?

store individual pieces of data or calculations

A pie chart is used to compare?

PIE CHARTS are used to compare the parts of category to the whole and typically each of the parts is expressed as percentages of the whole

The PMT function calculates what and how?

PMT (rate, nper, pv, [fv], [type]) calculates the payment amount for a loan given the interest rate, number of payments to be made to pay off the loan and the original loan amount.

PV calculates what?

PV (rate, nper, pmt, [fv], [type]) calculates the current value (accounting for compounding interest) of an investment given the interest rate, number of payments to be made, and the amount of the payment.

What functions allows you to capitalize the first character in each block of text?

Proper Function

Rate calculates what?

RATE (nper, pmt, pv, [fv], [type], [guess]) calculates the interest rate earned for an investment given the number of payments made as part of the investment, the payment amount, and the current value of the investment.

What do 'reference functions' let you do?

REFERENCE FUNCTIONS allows you to lookup frequently used values on a reference table in Excel

The "Right" function shows what?

RIGHT is used to return a number or characters from the end of a block text

What function is used to convert all characters to lower case letters?

Lower Function

Min shows what?

MIN(number1, [number2], ...) returns the smallest value in a set of numbers

What function shows you the month portion of the date?

MONTH (serial_number) returns the month portion of a date (number between 1 through 12)

What function lets you combine cells to form a larger cell?

Merge Cells

What does the "now' function refer too?

NOW () returns the current time

NPER calculators what?

NPER (rate, pmt, pv [fv],[type]), calculates the number of payments that will be made to pay off a loan given the interest rate, payment amount, and original amount