PCIB Exam 2

dynamic array

- SORT & FILTER - spilling

breakeven analysis

(a type of what-if analysis) calculates the break-even point in sales volume or dollars; can be used to evaluate the impact of changes in sales volume on profits; requires knowledge of fixed costs, variable costs, and mixed costs

logical functions

- AND: defines multiple conditions that must be met for a TRUE result (=AND(logical1,[logical 2])) - OR: if one of the arguments is true, the result is TRUE; all arguments must be false for FALSE to be the result (=OR(logical1,[logical2])) - NOT: if the argument is true, displays a FALSE; if the argument is FALSE, displays a TRUE (=NOT(logical1)) ** AND, OR , and NOT are commonly used with IF functions

financial functions

- FV: calculates the future value of an investment (=FV(rate,nper,pmt,[pv],[type])) - PV: calculates the present value of a money transaction (=PV(rate,nper,pmt,[fv],[type])) **for type: 1 if the payment is made at the beginning of each period, 0 if the payment is made at the end of the period, default is 0 - NPV: is the value today of an investment; however it differs from PV because it allows for variable payment amounts; amounts can be positive or negative; assumes payments are made over consistent periods of time and each payment comes at the end of the period; useful for comparing different investments (=NPV(rate,value1,value2,...) - NPER: calculates total number of periods for repayment of loan (=NPER(rate,pmt,pv,[fv],[type])) - RATE: calculates interest rate on loan (=RATE(nper,pmt,pv,[fv],[type],[guess])) **guess is when you want to guess at an interest rate, the default is 10%

loan functions

- IPMT: how much of a specific period (per) payment is going toward the interest that has accrued on the loan (=IPMT(rate,per,nper,pv,[fv],[type])) - PPMT: how much of a specific period (per) payment is going toward the principal of a loan (=PPMT(rate,per,nper,pv,[fv],[type])) - CUMIPRINC: the amount of principal paid over a specific number of periods (=CUMIPRINC(rate,nper,pv,start_period,end_period,type) - CUMIPMT: amount of interest paid over a specific number of periods; does not allow a negative value (=CUMIPMT(rate,nper,pv,start_period,end_period,type)) ++start_period is the start of the payment period and end_period is the end of the payment period

conditional math functions

- MAXIFS/MINIFS (=MAXIFS/MINIFS(max_range,criteria_range1,criteria1....)) - SUMIF/SUMIFS (=SUMIFS(sum_range,criteria_range1,criteria1.....)) - COUNTIF/COUNTIFS (=COUNTIF(range,criteria)) - AVERAGEIF/AVERAGEIFS (=AVERAGEIF(range,criteria,[average_range]) - etc

statistical functions

- MEDIAN: middle value of a set of values (=MEDIAN(number1,[number2]...) - MODE: the value that appears most often in a set of values - MODE.SNGL: returns only one mode value, returns the first mode value that it finds, if there are no duplicate values, returns the error #N/A (=MODE.SNGL(number1,[number2]....) - MODE.MULTI: will return multiple values if the data has more than one mode; an array function (=MODE.MULT(number1,[number2]....)

3D excel maps

excel add-in that plots geographic and time data on a world map or a custom map; uses Bing to create three-dimensional visualizations; 3D maps are saved as tours that can have one or multiple scenes that play sequentially to show different views of the data; no save button in 3D maps, tours and scenes are preserved in the state in which you close the window; 3D maps tours and scenes are saved with it

text functions

- TEXT: can display numeric data as text using special formatting strings to display the text =TEXT(value,format_text) ex: (=TEXTA1,"YYMMDD") - TEXTJOIN: combines strings of text, values, or characters; use when same delimiter is necessary for all TextN arguments (=TEXTJOIN(delimiter,ignore_empty,text1,[text2],...) - CONCAT: joins or combines data strings, does not have a delimiter argument, can specify a different delimiter between each two TextN arguments, must include space characters to separate words or values (=CONCAT(text1,[text2],...) - REPLACE: replaces part of a text string, based on the number of characters specified, with a different text string (=REPLACE(old_text,start_num,num_chars,new_text)

array formulas

- advantages: consistency (the formula in all the cells containing the array formula is the same; that consistency can help ensure greater accuracy); safety (formulas that are grayed out cannot be deleted, to delete a multi-cell array formula, the formula must be deleted from the original cell where it was entered, this will delete the formula from the entire range of cells); smaller file sizes (a single array formula can be used instead of several intermediate formulas) - disadvantages: can be difficult for a user to understand; if they are very complex, they can use up a lot of computer memory and slow down calculations - array constants: a set of static values; horizontal array constant (a row; separate values with a comma, type opening and closing braces -- ex: ={5,10,15,20,25}); vertical array constant (a column; separate values with a semicolon; type opening and closing braces -- ex: ={5;10;15;20;25})

linking workbooks

- dependent workbook: includes or refers to data from another workbook - source workbook: includes data that is referenced in a dependent workbook - external reference: used to reference data in multiple workbooks; links/data connection to source workbook (ex: '{WorkbookName]WorksheetName'!CellRange); when you point to create an external reference, it is built with absolute references - tips for linking workbooks: make cells with links easy to identify; avoid circular links (ex: if workbook a references data in workbook b, then workbook b should not reference data in workbook a); consider where linked files will be stores

analysis toolpak

- descriptive statistics: quick and easy way to prepare a summary report - moving average: a series of average for a set of values (sometimes called a rolling or running average); often used with times series data to smooth out short-term fluctuations and highlight longer-term trends or cycles - histogram: column chart that illustrates frequency of each data point in a data set; excel creates bins if bin range is left blank; type a list of values to create your own bins

fixed, variable, mixed costs

- fixed costs: remain constant, in total, regardless of the level of activity - variable costs: change based on the level of activity (the number of products sold or services rendered) - mixed costs: variable and fixed components

calculating standard deviation and MAD

- mean absolute deviation (MAD): measure of how spread out a set of values is from the average (the mean) STEPS: - calculate forecast error: actual value - mean or forecasted value - calculate absolute value of forecast error (=ABS(number)) - calculate average of the absolute values - standard deviation: measures how broadly values deviate from the mean or average value in a range of numbers STDEV.S: applies to sample populations, ignores text STDEV.P: applies to the entire population, ignores text

design concepts from slides

- remember that we want our workbooks to be accurate and easy to maintain and update - that means we should, to the extent possible, use cell references, formulas and functions instead of static data

what-if analysis and variables

- what if analysis (also called sensitivity analysis): use of several different values in one or more formulas to explore all the various results - variable: a value that can be changed to see how the change affects other values

setting worksheet and workbook protection

- workbook level: protects workbook structure (number and arrangement of worksheets); prohibits adding, deleting, unhiding, or moving worksheets; setting password is optional; does not protect the contents of cells - worksheet level: protects worksheets from alteration; protects the contents of cells; if protect sheet is enabled, locked cells cannot be edited; by default, all cells in worksheet are locked; cells that a user may edit must be unlocked BEFORE setting worksheet protection; can specify what editing capability is available to users; setting password is optional

using scenario manager

a saved set of values in a worksheet, user can vary numbers and see potential results, often used to review best and worst possibilities, worksheet can have up to 32 scenarios - scenario summary report: a generated worksheet that describes each scenario in a workbook; formatted as an excel outline with tow row outline levels and two column outline levels; can hide and display details about the changing cells and the result cells; can generate a scenario summary report as a pivot table report

object hyperlink

a shortcut assigned to an object such as a smartart graphic, a chart or a picture; there is no underlined text, but the hyperlink screentip can be used to provide information

cell hyperlink

a text shortcut or jump term in a cell (appears as underlined text); font color, font name, and font size can be changed

using comments

a thread in a conversation; enables the user to ask and respond to questions or issues about a workbook; default comment is automatically inserted in a shared workbook for each edit; users can insert their own comments, when a comment is posted an indicator appears in upper right corner of cell comments vs notes - comments allow the user to have a conversation; they have a reply box - notes are used to make annotations

MATCH

looks for a value within a range and returns the position of that value within the range =MATCH(lookup_value,lookup_arrary,[match_type]) - 1: (default value) returns a match for the largest value that is less than or equal to the lookup value (lookup array must be sorted in ascending order) - 0: returns an exact match - -1: returns the smallest value that is greater than or equal to the lookup value (lookup array must be sorted in descending order)

smartart graphic

a visual representation, usually with text; consists of several smaller shapes which can be added and removes; can type text into a text pane or directly inside component shapes; as text is typed into text pane, the text and its shape are sized to fit the content; not linked to worksheet data - list: for items that do not need to be in a particular order - process: for items that do need to be in a particular order - cycle: for a process that repeats over and over again - hierarchy: shows branching, in either a decision tree or an organization chart - relationship: shows relationships between items - matrix: shows ow an item fits into the whole - pyramid: shows how items relate to each other with the largest item being on the bottom and the smallest item being on the top - picture: shows a series of pictures along with text in the diagram

co-authoring (sharing) a workbook

multiple people edit a workbook, either simultaneously or at different times; owner receives email or alert when co-author has made edits; workbook must be saved in OneDrive

using notes

notes can be used to document a workbook and make the workbook easier to understand; to view a note, point to the cell that has a red triangle in the corner; each cell can have only one note, can 'show all notes' and 'convert to comments'

hyperlinks

a clickable text string or object that, when clicked, moves the pointer to another location; can open another workbook or webpage, switch to another worksheet, navigate to a picture or smartart graphic

FIND and REPLACE

a feature that scans a document, searches for occurrences of specific text, symbols or formatting, and allows a user to replace it with new text, symbols or formatting

watch window

a floating dialog box with cell addresses and content to monitor while you work in another part of the workbook or worksheet; enables you to observe the effects of changes without having to scroll through a large sheet or switch between multiple sheets

creating an amortization schedule

an amortization schedule is a table that shows the interest and principal payments and the remaining balance of the loan for each period - interest and principal can be calculated using the IPMT and the PPMT functions - the payment is the same each period; it equals interest plus principal paid each period - remaining balance = beginning balance - principal payment - note that after the last payment is made, the remaining balance is 0

IFERROR

detects an error and displays a more user-friendly error message; only works for formulas that can return a standard excel error value message =IFERROR(value,value_if_error) - value: the formula that will be checked for errors - value_if_error: the value, text string, or formula to use if the formula in the value argument results in an error common error messages - #N/A: a value or an argument is missing - #VALUE!: an incorrect data type is used - #REF!: a cell reference is empty, usually because cells were deleted - #DIV/0!: the formula divides by zero or an empty cell - #NUM!: the formula uses an invalid numeric entry - #NAME?: the formula uses unrecognized text such as misspelled function, sheet, or range name - #NULL!: the formula refers to an intersection of two cell ranges that do not intersect, or uses an incorrect range separator - #SPILL!: a formula returns multiple results and excel cannot return the results to the grid

setting list validation

displays a list of data values from which the user can choose data for the list: - type into dialog box separated by commas OR place in cell range in the workbook - BEST PRACTICE: list data in alphabetical or other logical order

one dimensional map chart

displays high-level geographic details (continents, countries, states, or provinces); cannot plot cities or streets

INDEX

displays the contents of the cell at the intersection of a specified column and row; rarely used alone and is generally nested with the MATCH function ** In order to use a VLOOKUP (HLOOKUP) function, the value to be looked up must be in the first column (row of the lookup table). ** INDEX and MATCH can be used instead of VLOOKUP (HLOOKUP) when the value to be looked up is not in the first column (row) of the table =INDEX(array,row_num,[column_num])

SWITCH

evaluates an expression against a list of values and returns the result corresponding to the first matching value; if there is no match, an optional default value is returned; like a VLOOKUP with the table built into the formula =SWITCH(expression,value1,result1,default_or_value2,result2,....) - expression: a value to be switched - value 1: the value compared with the expression - result 1: the value returned if value1 matches the expression - default 1: optional default value returned if value 1 does not match the expression ** up to 126 matching values and results can be evaluated & will not accept logical operators (<,>,<=)

setting custom data validation

can be used for situations where multiple criteria are needed; a formula can be used

using control formulas

can be used to display a choice, to run a command, or to perform an action in a worksheet - option button control: a radio button that allows the user to select one choice from a set of possibilities; default label "option button n" in which "n" is a number; edit the label to specify the option names; usually organized in a group box control or a rectangle shape - combo box: creates a drop-down list from which you select one option; options in the list are from a cell range in the workbook; link the control to a cell which displays a value that represents the position of the choice in the range; use INDEX command to display combo box result in a worksheet cell - scroll bar: use to quickly enter or change a range of values, scrolls through a range of values when you click the scroll arrows or drag the scroll box; useful for setting or adjusting a large range of values, or for cases when precision is not important; link the scroll bar to the cell containing the value you want to change

TRANSPOSE

changes the rows of a selected range into columns and vice versa; an array function =TRANSPOSE(array) ** when an array function is entered in a range of cells, the formula cannot be deleted from one cell in the range. to delete the formula, it must be deleted from all cells in the range.

finalizing for distribution

commands that can be used before a workbook is distributed to others - smart lookup: command research feature that uses Bing, an internet search engine, to find information about a label or value - check accessibility: command that opens the accessibility checker pane; identifies content that a person with a visual or literacy impairments may find difficult to understand - alt text: read aloud by a screen reader to aid those who need assistance - document inspector: checks a file for hidden data and personal information contained in the workbook properties (metadata); use to remove certain elements such as personal information in the document properties, comments and hidden rows, columns, and worksheets before finalizing a document and sending it to others - check compatibility: identifies commands, features, and objects that are not supported in earlier versions of excel; lists the items that may be lost of downgraded if the workbook is saved in an earlier microsoft excel format; groups the items by loss of functionality and loss of fidelity; lists the number of times each issue occurs in the workbook - loss of functionality: can cause permanent loss of data; loss of a specific tool/function - loss of fidelity: loss of formatting; a function works differently

EXACT

compares two text strings, values, or characters to determine if they are identical; arguments are case sensitive, does not check formatting, result is 'true' or 'false' =EXACT(text1,text2)

DATEVALUE

converts a date in a text format into a serial value so that it can be used in mathematical calculations =DATEVALUE(Date_text) - date_text: text that represents a date

TIMEVALUE

converts a time in a text format into a serial value =TIMEVALUE(Time_text) - time_text: text that represents a time

the form button

creates a data input form for list-type data; window or dialog box with labels and entry boxes in a vertical layout; can display up to 32 fields or columns and shows one row (record) at a time; worksheet is updated with data entered in form, form can be used to delete, add, or filter records

forecast sheet feature

creates a new worksheet with a data table and chart predicting future values based on the data selected; illustrates past data as well as predicts future values; can be created quickly to estimate future values for product sales, work hour requirements, expense levels - forecast sheet: uses data series to build a line or column chart; one data series must be a date or time field, one data series is the values used for forecasting; generated sheet formatted as excel table, displays existing dates/times and values in adjacent columns; forecast dates display at the bottom of the table with estimated values in a third column; line chart with existing and forecasted values is shown to the right of the excel table

setting data validation, input message, and error alerts

data validation tools: help to minimize data entry errors; rules to control what can and cannot be entered in specific cells; excel checks data as it is entered to verify that it matches established requirements input message: appears when a user makes a validated cell active; comment box that contains a guideline for the person entering data error alert: pop-up message that appears after invalid data is entered; stop (user cannot enter invalid data, must cancel or retry), warning (invalid entry is allowed but can be edited or canceled), information (invalid entry is allowed)

IFS

has one or more logical tests; the result that displays in the cell represents the first logical test that evaluates to TRUE; usually built from high value to low value; to specify a default result, enter TRUE for the final logical_test argument =IFS(logical_test1,value_if_true1,[logical_test2,value_if_true2],...)

one and two variable data tables

helps analyze how different values of one or more variables will change the results of a formula or formulas; inserts the function {=TABLE(row_input,column_input)} as an array formula; same formula is executed in each cell in the selected range - one variable data table: uses one set of input values, substitutes those values in one or more formulas and displays results for all the formulas - two-variable data table: uses two sets of input values, one in a column, the other in a row, and displays results for only one formula

mark workbook as final

indicates that it will not be edited further; converts it to read-only status; does not really prevent another user from editing it; when the workbook is opened, the message bar displays a warning, click the 'edit anyway' button to remove the read-only status from the file

circle invalid data

invalid data can occur when data is copied into a range with validation settings; validation is set AFTER data is already entered when invalid data is circled, it is placed in a red ellipse (an elongated circle) around each cell with invalid data; highlights cells but does not edit or correct the data

database functions

perform calculations on data that meets specific criteria; all database functions require the same arguments syntax = FunctionName(database,field,criteria) - database: data arranged in rows and columns with column labels - field: the column used for the calculation, can be identified by the column number in the database array or by the column label enclosed in quotation marks - criteria: the cell range defining the conditions the data must meet in order to be included in the calculation; criteria range must have at least two rows; first row includes labels that are identical to the column labels in the database range; second row in where you enter the criteria which can be numeric values, expression, or text; can specify AND criteria in same row, can specify OR criteria in separate rows, can use wildcard characters (*, ?) examples: DAVERAGE, DCOUNT, DCOUNTA, DMAX, DMIN, DSUM

SUBTOTAL

performs calculations on data in a filtered subset (rows that are hidden due to applying filters are not used in the calculations); performs 11 functions (sum, average, count,...etc); not to be confused with the subtotal tool on the data tab

excel templates

prototype or sample workbook; provides a starting point for building other similar workbooks; can contain data, formulas, formatting, charts, etc; best for work that requires the same layout, design, and data pattern

excel database (what is it)

range of cells that includes header row and rows of data; stores data made up or records (rows) and fields (columns)

rounding functions (how do they affect the values that are stored in the cells?)

refers to adjusting a number up or down to make it more appropriate to the context in which it is being used; used specifically to control the number of digits to the right of the decimal point, often used in nesting formulas - ROUND: round to a specific number of decimal places or a whole number (=ROUND(number,num_digits)) - ROUNDUP: automatically rounds up to the next number, regardless of the value to the right of the rounding point (=ROUNDUP(number,num_digits)) - ROUNDDOWN: automatically rounds down regardless of teh value to the right of the rounding point (=ROUNDDOWN(number,num_digits))

encrypt a workbook with a password

requires a password to open a file; the info tab in backstage displays the current permissions status of the workbook

XLOOKUP

searches a range or an array and returns an item corresponding to the first match if finds; if a match does not exist it can return the closest approximate match =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]) - lookup_value: the value to be looked up - lookup_array: the array or range to search - return_array: the array or range to return - [if_not_found]: text to return if a valid match is not found; if a valid match is not found, and [if_not_found] is missing, #N/A is returned) - [match_mode]: specify the match type; 0 = exact match, if not found return #N/A; -1 = exact match, if not found return the next smaller item; 1 = exact match, if not found, return the next larger item; 2 = a wildcard match using *, ?, and ~ - [search_mode]: specify the search mode; 1 = perform a search starting at the first item; -1 = perform a reverse search starting at the last item; 2 = perform a binary search; lookup_array MUST be sorted in ascending order; -2 = perform a binary search; lookup_array MUST be sorted in descending order

solver to find a solution for a formula

solver: data analysis tool that optimizes a problem; by manipulating the values of selected variables and bound by constraints; solver can be used to find the highest value, the lowest value, and the exact value

OFFSET

to create dynamic ranges and range names returns a reference to a range that is specified number of rows and columns from a cell range of cells; the reference that is returned can be a single cell or a range of cells =OFFSET(reference,rows,cols,[height],[width]) - reference: the reference from which you want to base the offset; must refer to a cell or range of adjacent cells - rows: the number of rows, up or down, that you want the upper-left cell to refer to; positive rows means below and negative rows means above the starting reference - cols: the number of columns, to the left or right, that you want the upper-left cell of the result to refer to; positive cols means to the right and negative cols means to the left of the starting reference - height: optional; the height, in number of rows, that you want the returned reference to be; height must be a positive number - width: optional; the width, in number of columns, that you want the returned reference to be; width must be a positive number

ranking values

used to calculate where a particular value appears in the dataset - RANK.EQ: when duplicate values exist in a range, this function shows the lower rank - RANK.AVG: calculates duplicate values with an average rank

fill across worksheets

used to copy cell content or formats to worksheets in group; will only fill to same location in different worksheets; options: all, contents, formats

grouping worksheets

useful for editing and formatting multiple sheets at once; worksheets should be identical in layout; exception: worksheets need not be identical if changing a print setting or inserting a header or footer; if you try to reorder a group of worksheets, they will move as one; if you print, every worksheet in group will print unless specified

using goal seek

uses iteration to find the values needed to achieve a goal or objective; backsolving: knowing the results and determining the value needed to reach those results; solves a formula for ONE cell