BUSI 520 Most Necessary

Term
1 / 122
Wrong argument type or operand
Click the card to flip 👆
Terms in this set (122)
Wrong argument type or operand
#VALUE!
Toggle between displaying formulas and values
CTRL+'
When multiple horizontal cells are selected, this copies leftmost formula/value to other cells
CTRL+r
Paste special
CTRL+alt+v
Number format to two decimal places
CTRL+shift+!
Move one screen to right within worksheet
Alt+pg dn
Delete selected cells entirely
CTRL+-
Current Date
CTRL+;
Border around selected cell range
CTRL+shift+&
Unrecognized text in a formula
#NAME?
Show format cells dialog windowCTRL+1When multiple vertical cells are selected, this copies topmost formula/value to other cellsCTRL+dMoves active cell to end of row without highlightingCTRL+right arrowSelect current rowShift+spaceCOUNT vs. COUNTACOUNT counts all cells with numbers or numerical arguments, COUNTA counts all non-empty cellsPercent formatCTRL+shift+%Change cell reference typeF4When using Excel to open a file that is in .csv format, formulas and formatting entered in the sheet can be safely saved in either .csv, .xls or .xlsx formatFalseCurrent timeCTRL+shift+:Select current columnCTRL+spaceTwo ways to close windowCTRL+w, CTRL+F4Remove border from cell rangeCTRL+shift+_Apply general formatCTRL+shift+~The formula =ROUND(10.59%,2) results in 0.11TrueThe correct formula to count all items in the range E4:E20 that end with a "d" in the third to last position in the text value is=COUNTIF(E4:E20,"*d??")The correct formula for counting values in the range D5:D25 that are greater than 90 is=COUNTIF(D5:D25,">90")To round the number 4.43 to next highest even integer, use the formula =EVEN(4.43)TrueThe formula =ROUND(SUM(9.33,9.55),0) results in19Best function for comparing side-by-sideSplit windowWhen numbers containing zeros are entered in a cell, if 0 is used as one of the digits in the cell's custom formatting code, that digit displaysany digit, regardless of whether it affects the value of the numberThe formula _____ counts all product codes in the range H4:H25 with only a single character followed by 715=COUNTIF(H4:H25,"?715")Any amount of any characters; one character* and ?EVEN and ODDRounds number UP to nearest even or odd integerRANK syntax=RANK(value you're ranking, range of comparison, 0 or nothing for descending or positive value for ascending)=RAND()Returns real number between 0 and 1RANDBETWEEN syntax (inclusive, returns integer)=RANDBETWEEN(lower bound, upper bound)If a cell contains the formula =Goodbye<Hello the resulting value is#NAME?Relational operators can be used to evaluate both numbers and textTrueIF syntax=IF(logical test, result if true, result if false)Relational operators for greater than, greater than or equal to, equal to, not equal to, less than or equal to, and less than>; >=; =; <>; <=; <The formula =OR(B5:B25) returns a value of _____ if any of the values in B5 through B25 contain the value TRUETrueIf B3 contains the date 12/31/2006 and C3 contains the date 12/31/2005, then =B3<C3 isFalseIf the cell M1 contains Monkey1 and cell D1 contains Dog2, the formula =M1<=D1 results in a value ofFalseTo apply "Cell Value Is" conditional formatting, firstselect the cells you want to conditionally formatRelational operators can be used to evaluate text, numbers, and datesTrueUse = for formulas within IF function?NoIf the values in cells F3, F4, F5, and F6 are all greater than 100, the formula =OR(F3:F6>100) will always return a value of TRUEFalseAND syntax for finding if A1 is in the 20s=AND(A1>=20,A1<=29)Can you use an array in an OR argument like OR(A3:A6>10)?NoIFERROR syntax=IFERROR(value or formula to check, value if error)What kind of construct is =AND(F3,NOT(OR(G3:H3)))"Only"=AND(NOT(F3),NOT(G3),NOT(H3)) is an example of a(n) _____ construct"None Of"Can relational operators evaluate dates?YesWhen using a IF function, formulas can be used within a Value_if_true or Value_if_false argument if the formula is enclosed within quotation marksFalseRelational expressions are not case sensitiveTrueWith regards to relational operators and text values, a greater value is one that appears earlier in the alphabetFalseNested IF syntax=IF(test, value if true, IF(second test, value if true, value if false)) You can keep putting in more IFs with more tests in place of the "value if false" depending on how many options there areWhen entered in a worksheet named Forecast, does the formula =Costs!B5*C5 multiples the value in cell B5 of the "Costs" worksheet by the value in cell C5 of the "Costs" worksheet?No, must be =Costs!B5*Costs!C5 otherwise C5 comes off of ForecastWhat what is the difference between =ISERR(value) and =ISERROR(value)?ISERR returns true for all errors but #N/A while ISERROR returns true for all INCLUDING #N/AVLOOKUP syntax=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) AKA =VLOOKUP(value being searched, array of cells in which the table is located, column number in selection, TRUE for closest without going over and FALSE for exact match)The lookup_value argument in a VLOOKUP or HLOOKUP function is often a number, text, a logical value, or a cell reference...but the lookup_value argument can also be a formulaTrueA data list that categorizes values for retrieval is a(n) _____ tableLookupWhen using a VLOOKUP function, the first column of the lookup table that contains the data you are comparing to your "lookup_value" is/are called theKey dataINDEX MATCH syntax=INDEX(answer_array, MATCH(lookup_value, lookup_array, match_type)) AKA =INDEX(list of answers column, MATCH(what you're searching, list of possible matches column, 0 for exact and 1 for closest)Use the _____ function to retrieve a value from a table based on the row and column numbers specifiedINDEXA LOOKUP function is used to retrieve a value fromA column or rowValues in a "closest match" vlookup table must be sorted in descending order so that the VLOOKUP function can make accurate comparisonsFalsePMT syntax=PMT(rate, nper, pv, [fv], [type]) AKA =PMT(annual interest rate/payments per year, payments per year*term in years, loan amount, ending balance (usually $0), 1=pay at beginning of period and 0=at end)In order to calculate the Future Value of a stream of monthly cash flows which start immediately, and also include an additional lump sum at the beginning of the transaction, the value in the PMT argument should equal the Lump Sum amount plus the monthly Payment amountFalseThe _____ is the time it will take to earn sufficient profits so that the loan can be repaidPayback periodRATE syntax=RATE(nper, pmt, pv, fv, type)NPER syntax=NPER(rate, pmt, pv, fv, type)PV syntax=PV(rate, nper, pmt, fv, type)FV syntax=FV(rate, nper, pmt, pv, type)Common structure of all the payment scheduling syntaxesThe argument is always (rate, nper, pmt, pv, fv, type) in that order with the function taken out and made the functionThe equivalent yearly simple interest rate that takes into account compounding is theAnnual percentage yieldIn financial functions, an argument Type of _____ indicates payments are made at the end of each period0The lower the net present value (NPV), the more profitable the projectFalseA _____ is additional money required from the borrower at the end of a loanBalloon paymentWhen calculating a future value, enter beginning lump sum amounts and beginning balances in the PV argument boxTrueIf there is not a FV argument box (such as when you're calculating Future Value), add the Ending Lump Sum to the end of the formula (added outside of the FV function)TrueBest tool for changing all instances of "hello" to "hi"SUBSTITUTEA set of validation rules for an XML document isA schemaA set of fields that describes one product or person is arecordYou can't use the subtotal TOOL on data that's been converted to an Excel tableTrueJoin two or more text strings into oneCONCATENATETo convert a value to text in the specified number formatTEXTRemove spaces (except between words)TRIMA _____ identifies the end of each record in a comma-delimited fileParagraph markThe FIND function is not case sensitiveFalseThe _____ function returns the serial number of the date that is the indicated number of months before or after the start_dateEDATETo convert lowercase letters in a text string to uppercase, use the UPPER functionTrueThe SEARCH function is case sensitiveFalseTo remove all nonprintable characters from a text string, use the _____ functionCLEANYou cannot use the SUBTOTAL function, =SUBTOTAL(...), on data that has been converted to an Excel "Table."False_____ can be used as a method to share data and a description of that data in an open nonproprietary format for use by different programsXMLTo extract characters from the beginning side of a text string, use the _____ functionLEFTTo compare two text strings to determine if they are identical (case sensitive), use the _____ functionEXACTXML stands for extensible ___________Markup languageThe _____ function determines the number of characters in a text stringLENIn an attempt to find the number of days that elapsed between two dates, you've created a formula with the following syntax: =(Recent_date - Earlier_date). However, for some your reason your formula result is showing up as a date rather than the number of days in between. What's the easiest way to quickly fix the problem?Format the result using general formatMost accurate basis for calculating time between two dates using YEARFRAC1 (actual days in each actual year, not an average like 365.25)DATEDIF calculates in years, months or days. What are the unit names for syntax?m, y, dIf you wanted the exact date a certain number of months from a date, what would you use?EDATESUBTOTAL function function numbers1=average, 2=count, 3=counta, 4=max, 5=minClick the _____ button in the Scenario Manager to copy scenarios on one sheet to the active worksheetMergeA PivotTable is _____ allowing you to change the detailed view of the dataInteractiveBinary numbers of 1 and 0 can be used in Solver to assign whether a contract is awarded (or not)TrueThe decision variables are theChanging cellsA(n) _____ solution results when Solver cannot determine the combination of decision variables to satisfy the constraintsInfeasibleSimply rounding the solver solution is an accurate alternative to using the integer constraintFalseWhen using Solver, the objective (target) cell cannot be a cell that contains a formulaFalse$C$9:$F$9<= $C$14:$F$14 could be used as a valid solver constraintTrueSUMPRODUCT ranges cannot have blank cells within the rangesFalseSolver's "Changing Variable Cells" can be cells with either values or formulasFalseTo minimize costs or maximize profits, useSolverRather than entering separate constraints such as B4>=0, B5>=0, B6>=0...it is possible to enter a single constaint such as B4:B6>=0TrueThe data validation default (list box) option isAny valueUse the _____ button on the Formula Auditing toolbar to keep track of cell properties such as name, value, and formula, even if the cell is out of viewWatch WindowA(n) _____ cell contains a value that is used in a formulaPrecedentData validation only restricts new data, not data already in the sheetTrueThe Error Alert Style of _____ indicates the entry does not conform to the data validation rules, and is accepted or canceledInformation