Excel: Module 5
What feature do conditional functions inherently rely on? formatting logical operators PEMDAS order of operations
logical operators
PEMDAS is an abbreviation to help with which mathematical concept? binomial distribution exponential growth order of operations solving for Y
order of operations
What symbol is used to mark a nested function in Excel? - question mark - exclamation mark - semicolon - parentheses
parentheses
Which keyboard characters are used to help determine order of operations? parentheses ampersands asterisks brackets
parentheses
n Excel, the formulae 5+5*3 and (5+5)*3 provide different answers because of: arguments. syntax. functions. errors.
syntax
What is an example of an exponent? 1/2 √16 0.8 sin32
√16
What selects the range of cells that will be used in an Excel calculation? a discussion an argument a conversation a declaration
an argument
Gayle applies the function =TEXTJOIN("," TRUE, A1, B1, C1) to the following comma-delimited data: "One", "Two", "Three" What will Excel return? an error code One, Two, Three One Two Three One,Two,Three
an error code
What syntax tells Excel to cross-reference a cell in another sheet? a colon an exclamation mark a question mark an ampersand
an exclamation mark
Carlos wants to know how much money 15 Euros is in US dollars. What does he need to do? concatenate values convert currency aggregate costs filter statements
convert currency
Conditions are used for which two excel tools? - formulae and charts - functions and formatting - forecasting and charts - themes and functions
functions and formatting
Marjory has a spreadsheet containing formulas that she saved as a simple CSV file for a friend to open with notepad (or some similarly basic word processor program). What should Marjory expect the friend to see in the cells that had formulas? - Nothing; the cells with formulas are ignored completely. - final formula results (e.g., "10" for a SUM of 1, 2, and 8) - the formulas verbatim (e.g. "=SUM(A1:A3)") - empty cells where the formulas were (e.g., ", , , ")
- final formula results (e.g., "10" for a SUM of 1, 2, and 8)
Given the comma-delimited data 1, 5, 10, 12 what would Excel return for the following formula? =(SUM(A1,C1))-(D1) -1 33 -11 11
-1
Tailor applies the formula =PROPER(A1) (and then B1, C1, etc.) to the following row of comma-delimited data: 1, -2, ONE, two What will Excel return? 1, -2, One, Two 1, 2, ONE, Two 1, -2, one, two One, Negative Two, One, Two
1, -2, One, Two
Tyler applies the formula =LOWER(A1) (then B1, C1, etc.) to the following row of comma-delimited data: 1, 2, ONE, two What will Excel return? 1, 1, 1, 1, 1, 2, one, two 1, 1, ONE, two 1, 2, ONE, two
1, 2, one, two
Max applies the SUM function to the following comma-delimited data: 1, 2, 3, 4, five What will the function return? 4 7 10 an error
10
Given the comma-delimited data 1, 5, 10, 12 what would Excel return for the following formula? =SUM(A1:C1)-5 6 11 -5 5
11
Nataly enters the following into a cell: =SUMIF(A1:E1, "<20") If the comma-delimited data in the cells were: 1, 5, 10, 20, 25 what value would Excel return? 36 45 25 16
16
Cedric types the following into a cell: =AVERAGEIF(A1:E1, "<> 5") If the comma-delimited data in the cells were: 1, 2, 2, 3, 5 what value would Excel return? 5 2.6 2 1.6
2
Lyle types the following into a cell: =COUNTIF(A1:F1, ">0") If the comma-delimited data in the cells were: -1, -6, 0, 0, 1, 6 what value would Excel return? 6 4 7 2
2
Gladys applies the AVERAGE function to the following comma-delimited data: 1, 1, , , 1, 2, 2, 3, 4 1 2.166666667 4 an error
2.166666667
Given the comma-delimited data 1, 5, 10, 120 what would Excel return for the following formula? =LEFT(C1,1)*RIGHT(D1,2) 20 0 10 12
20
Based on Excel's naming conventions for functions, what would you expect the function SQRT to return for the number 16? an error 4 32 256
4
Candice applies the COUNT function to the following comma-delimited data: 1, 1, 3, 5, five What will the function return? 4 3 2 5
4
Lisa applies the MAX function to the following comma-delimited data: -50 12 1 49
49
Silas applies the MIN function to the following comma-delimited data: 5, 7, What will the function return? an error 5 -2 2
5
Given the comma-delimited data 1, 5, 10, 12 what would Excel return for the following formula? =SUM (A1, C1)-5 11 -5 -4 6
6
What is an example of an operator that would return a FALSE entry in Excel for the given values? 5>=5 5 6 65
6
What is an example of a logical operator in Excel? 1+1= IF A2 TRUE = =0>A2 =A2, ELSE =B1
=0>A2
What is an example of a string of characters that would create a formula in Excel? 5+2 = 2 =B4 + 2*F1 B4:B8 SUM B1-B2
=B4 + 2*F1
Which formula contains an example of an Excel reference? 5+2 = 2 10 =AVERAGE X*Y =SUM B1:B7
=SUM B1:B7
Which of these is a string? "-256" All of these are strings. "100" "zero"
All of these are strings.
What argument would Penny need to type into Number1 in a Function Argument popup to include data in the first five rows of the second column of an Excel spreadsheet with no headers? B1:B5 C1*C5 A1-A5 1-5/2
B1:B5
Which function will work on a string? SUM RIGHT MAX MIN
RIGHT
Addition and subtraction work on strings, but more complicated arithmetic, like multiplication or division, won't work. True False
False
Excel's major weakness is an inability to create forecasts or predictions with its workbooks. True False
False
Excel's most commonly used logical operator is SUM. True False
False
For a function like RIGHT, if no number is specified, the default argument value is zero. True False
False
Formulas update automatically when data is changed, but functions are static and have to be re-applied to data each time. True False
False
Functions are more restrictive than formulas in terms of the complexity of the calculations they can perform. True False
False
LEFT and RIGHT functions don't work on cells containing numbers. True False
False
References in Excel are similar to the quotations, footnotes, or sources you would see in Word and other word processors. True False
False
The CONCAT command is new to Excel and preforms an improved version of the original CONCATENATE command. True False
False
There are just over 100 functions built into Excel. True False
False
What is an example of something that Excel CANNOT reference? a worksheet a file a cell a range
a file
Given the comma-delimited data 1, 5, 10, 120 what would Excel return for the following formula? =CONCAT(B1-D1) "5120" "5-120" "5 120" "-115"
"-115"
Taylor has the number "1" typed into A1. He types =UPPER(A1) in cell B1. He then goes back to cell A1 and types the text string "one." This will change cell B1 from _____ to ____. "#NUM!", "ONE" "1", "one" "1", "1" "1", "ONE"
"1", "ONE"
Given the comma-delimited data 1, 5, 10, 120 what would Excel return for the following formula? =CONCAT(B1:D1) "1 5 10 120" "510120" "5 10 120" "1510"
"510120"
For the following data, what will =LEFT(A1, 3) return? "correct" "cor" "correct" three cells to the left "ect" "correct" three times, left-aligned
"cor"
For the following data, what will =RIGHT(A1, 3) return? "correct" "cor" "correct" three cells to the right "ect" "correct" three times, right-aligned
"ect"
For the following data, what will =MID(A1, 2, 3) return? "corrects" "rr" "rec" "re" "orr"
"orr"
What indicates a range covering first five headers for a typical spreadsheet? A1:A5 A1+E5 A1:E1 A1-E1
A1:E1
Which Excel function, according to its description in the Insert Function popup, checks whether a condition is met and returns one value if TRUE and another value if FALSE? AND TRUE/FALSE IF ELSE
IF
What is the main advantage of TEXTJOIN over CONCAT? - It allows a delimiter to be included. - It allows numbers to be included. - It allows multiple cells to be included. - It automatically formats the text appropriately.
It allows a delimiter to be included.
Sandra Dee is working with a very large spreadsheet and wants to combine the salutation with names (e.g., turn the three cells "Mr. ", "John ", and "Smith " into one cell saying Mr. John Smith). Why is CONCATENATE preferable over Merge? Merge only works on empty cells; CONCATENATE works on any cell. Merge creates larger cells; CONCATENATE combines into a regular sized cell. Merge only works on numbers; CONCATENATE works on anything. Merge adds a space between each string; CONCATENATE doesn't.
Merge creates larger cells; CONCATENATE combines into a regular sized cell.
Lamar is planning to concatenate the following data: "Jane", "Doe", ",", "born 1971" He'd like the final result to read "Jane Doe, born 1971" exactly. Will the CONCATENATE function work as he intends? - Yes, he'll get the result he wants. - No, Excel can't concatenate numbers. - No, Excel can't concatenate punctuation. - No, CONCATENATE won't include spaces.
No, CONCATENATE won't include spaces.
Crystal has a database of book titles, but the capitalization is off on many of them (e.g., some have the first TWo LEtters of a word capitalized, some have no capitalization, some are in all caps, etc.). Why can't she just apply a single PROPER function to the data to create a "fixed" copy? - PROPER will capitalize EVERY word (including "a" or "and," for example). - PROPER will only capitalize the first word of each cell. - PROPER will capitalize EVERY letter. - PROPER won't work on the ALL CAPS titles.
PROPER will capitalize EVERY word (including "a" or "and," for example).
Cecilia has a series of first, last, and middle names she wants to combine into a single cell. However, some cells are missing data (e.g., there's no middle name for many rows). Which command will BEST suit her needs? CONCAT CONCATENATE TEXTJOIN MERGE
TEXTJOIN
Kylie has the following comma-delimited data: "1234", "Spruce", "Ave.", "91210" What command could she use to most easily turn this into street address in a single, normal-sized cell? CONCAT CONCATENATE TEXTJOIN MERGE
TEXTJOIN
What must be included in any TEXTJOIN TRUE or FALSE for ignore_empty a comma or space delimiter a range of three or more a string size limit
TRUE or FALSE for ignore_empty
What will happen if a function is performed on a range that contains filtered data? - Only the non-filtered data will be included. - The entire range, filtered or not, will be included. - Excel will return an error message. - Only the filtered data will be included.
The entire range, filtered or not, will be included.
Celeste has created a SUM function of the following comma-delimited data: A, B, C, 1, 2, 3. She then changes the fourth entry from "1" to "one." What will happen to the function's existing return? - Nothing; the function was already performed. - The function will return "5." - The function will return "3." - The function will return an error.
The function will return "5."
Greg has a large spreadsheet of data collected by many workers. Because so many people worked on it, capitalization is inconsistent, and some cells are empty. Greg asks an Excel expert if he can just apply a function to change the data directly in his spreadsheet to a uniform capitalization theme. What is the limitation of using UPPER, LOWER, or PROPER to meet his request? - These functions create new data, they don't change the existing data. - These functions can't easily be applied to large amounts of data. - These functions won't work well on data with inconsistent capitalization. - The functions won't work on ranges that have empty cells
These functions create new data, they don't change the existing data
#NUM! represents an error somewhere in a formula or function. True False
True
A formula won't do anything without an argument for another cell or range. True False
True
An IF function basically checks whether something is TRUE or FALSE for a particular cell. True False
True
Even text can be used with some functions. True False
True
Excel's advantage over specialized inventory software is that it's easier to learn. True False
True
Operators fundamentally look at the relationship between values. True False
True
The MID function requires three arguments to work. True False
True
The UPPER function won't change anything if the argument is for cells containing just numbers. True False
True
These functions create new data, they don't change the existing data True False
True