Excel unit 5
What feature do conditional functions inherently rely on?
logical operators
What syntax tells Excel to cross-reference a cell in another sheet?
!
For the following data, what will =LEFT(A1, 3) return? "correct"
"cor"
Gladys applies the AVERAGE function to the following comma-delimited data: 1, 1, , , 1, 2, 2, 3, 4
2.166666667
Which formula contains an example of an Excel reference?
=SUM B1:B7
What indicates a range covering first five headers for a typical spreadsheet?
A1:E1
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
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?
IF
Which function will work on a string?
RIGHT
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?
TEXTJOIN
What must be included in any TEXTJOIN
TRUE or FALSE for ignore_empty
Which of these is a string?
all of these r strings
What is the main advantage of TEXTJOIN over CONCAT?
allows delimitator to be included
What selects the range of cells that will be used in an Excel calculation?
an arguement
Excel's major weakness is an inability to create forecasts or predictions with its workbooks.
false
Excel's most commonly used logical operator is SUM.
false
PEMDAS is an abbreviation to help with which mathematical concept?
order of opertaions
What symbol is used to mark a nested function in Excel?
parenthesis
Which keyboard characters are used to help determine order of operations?
parenthesis
What is an example of an exponent?
sqrt 16
In Excel, the formulae 5+5*3 and (5+5)*3 provide different answers because of:
syntax
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?
textjoin
#NUM! represents an error somewhere in a formula or function.
true
A formula won't do anything without an argument for another cell or range.
true
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?
the function will return 5
A cell that performs a formula or function will always have an equal sign in it.
true
An IF function basically checks whether something is TRUE or FALSE for a particular cell.
true
Even text can be used with some functions.
true
Excel's advantage over specialized inventory software is that it's easier to learn.
true
Operators fundamentally look at the relationship between values.
true
The MID function requires three arguments to work.
true
The UPPER function won't change anything if the argument is for cells containing just numbers.
true
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
Carlos wants to know how much money 15 Euros is in US dollars. What does he need to do?
convert currency
For the following data, what will =RIGHT(A1, 3) return? "correct"
ect
For a function like RIGHT, if no number is specified, the default argument value is zero.
false
Formulas update automatically when data is changed, but functions are static and have to be re-applied to data each time.
false
Functions are more restrictive than formulas in terms of the complexity of the calculations they can perform.
false
If the ignore_empty value is set to YES, blank cells will be ignored in a TEXTJOIN.
false
LEFT and RIGHT functions don't work on cells containing numbers.
false
References in Excel are similar to the quotations, footnotes, or sources you would see in Word and other word processors.
false
The CONCAT command is new to Excel and preforms an improved version of the original CONCATENATE command.
false
There are just over 100 functions built into Excel.
false
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?
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
Given the comma-delimited data 1, 5, 10, 120 what would Excel return for the following formula? =CONCAT(B1-D1)
-115
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
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 ____.
1-one
Max applies the SUM function to the following comma-delimited data: 1, 2, 3, 4, five What will the function return?
10
Given the comma-delimited data 1, 5, 10, 12 what would Excel return for the following formula? =SUM(A1:C1)-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?
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?
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?
2
Given the comma-delimited data 1, 5, 10, 120 what would Excel return for the following formula? =LEFT(C1,1)*RIGHT(D1,2)
20
Based on Excel's naming conventions for functions, what would you expect the function SQRT to return for the number 16?
4
Candice applies the COUNT function to the following comma-delimited data: 1, 1, 3, 5, five What will the function return?
4
Lisa applies the MAX function to the following comma-delimited data: 1, 10, -50, 49 What will the function return?
49
Silas applies the MIN function to the following comma-delimited data: 5, 7, What will the function return?
5
Given the comma-delimited data 1, 5, 10, 12 what would Excel return for the following formula? =(SUM(A1,C1*B1))
51
For the following comma-delimited data string: 1, 2, 6, 8, 10 =SUM(A2, A5)/2 would return which answer?
6
Given the comma-delimited data 1, 5, 10, 12 what would Excel return for the following formula? =SUM (A1, C1)-5
6
What is an example of an operator that would return a FALSE entry in Excel for the given values?
6
What is an example of a logical operator in Excel?
=0>A2
What is an example of a string of characters that would create a formula in Excel?
=B4 + 2*F1
Given the comma-delimited data 1, 5, 10, 120 what would Excel return for the following formula? =CONCAT(B1:D1)
"510120"
For the following data, what will =MID(A1, 2, 3) return? "corrects"
"orr"
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 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?
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).
What will happen if a function is performed on a range that contains filtered data?
The entire range, filtered or not, will be included.
