Exam 1
Which tool allows you to work backwards to determine what input value is required for a specific output value?
Goal-seeking
A1= TRUE, A2= FALSE, A3 = TRUE What is the result of =NOT(A1,A2,A3)
None of the above
Nesting can be used with an IF function to decide between more than two different results. According to the Course Notes, how many levels of nesting can be used with the IF function in Excel 2019?
64
How would you enter a formula to calculate the result of $25,000 times 2 in a cell such that the formula produces no error and shows $50,000 (assume the cell is formatted to display as currency)?
=25000*2
Which of the following will find and count all instances of values that are less than the value in cell C2 in the continuous range C2 to C10?
=COUNTIF(C2:C10, "<"&C2)
What would the formula be in cell F12 if the formula =B2-C7 is copied from cell D9 into F12?
=D5-E10
Constructing a decision tree to visually represent the logic in a multi-outcome IF problem is often recommended before translating it into Excel pseudo code and then the nested IF function.
True
Data (variables) should never appear more than once in a spreadsheet.
True
Evaluate this function: ="CSE" ="cse"
True
In the SUMIF( ) function, if the sum range is the same as the criteria range, then it may be omitted as the last argument.
True
In the order of operations for Excel (Order of Precedence), the relational operators (=, <>, <=, >=) are last.
True
It is possible to change the orientation of the data in a worksheet using paste special with values only and the transpose option.
True
Named ranges are always treated as absolute cell references.
True
Relational operators compare two values to determine if the relational expression is TRUE or FALSE.
True
The COUNTA( ) function determines the number of numeric and non-numeric values in a list and the order of the arguments in the list does not matter.
True
The IF function evaluates a certain condition and returns the value you specify if the condition is TRUE, and another value if the condition is FALSE.
True
The formula =OR( D2:D6 <= C2:C6) is not valid syntax and will result in a #VALUE! error or an incorrect result from Excel.
True
The syntax rules for the criteria in a SUMIF( ) function and AVERAGEIF( ) function are the same as the syntax rules for the COUNTIF( ) function.
True
When using TRUE or FALSE as criteria in a function, they are considered to be boolean values and therefore should not be put in quotes.
True
When you change the format of how a cell is displayed it doesn't affect the actual value stored by the computer.
True
You can change the orientation of the data using Paste Special and choose Values Only and the Transpose option. This adjustment is a temporary solution.
True
An analysis in which alternative values are substituted for data inputs is known as ____________ .
What-if
Which of the following is a limitation when using 3-D formulas and a range of cells spanning multiple worksheets?
the sheets must be adjacent to each other in the workbook
How do the AVERAGE( ), MIN( ), MAX( ), COUNT( ), and SUM( ) functions handle cells in a range that are blank or contain labels?
they are ignored
Why use the SUM function with a range of cells when adding the values together accomplishes the same thing?
-it requires no modification if row or column is added to range -range is adjusted when copying function to another cell -it saves time
What is the answer to this function? A1= FALSE =IF(A1="False",20,0)
0
Cell Value/Function D1 =25.4 D2 =D1(formatted to zero decimal places) D3 =D2+1.4+1.2 What is the actual value of the cell D3, not the displayed value?
28
Which of the following keywords or phrases would NOT be used to indicate the use of an OR function in the answer?
All
The logical IF function takes a Boolean value (TRUE of FALSE) as its first argument. This value can be: -the result of a relational expression -the result of a Boolean function -a reference to a cell containing a Boolean value -all answers
All answers
Which of the following is true about the IF function in Excel?
All answers -value_if_true and value_if_true can both be omitted, meaning logical_test is the only required argument. Also, you can add/subtract to these two arguments)
Which of the following is true about cell referencing in Excel? -by default, all cell references are relative references (when copied they change based on the relative position of the rows and columns) -an absolute cell reference is a cell address that contains a $ in the row and column -mixed cell referencing is when either the row or column is made absolute (anchored in) -absolute references remain constant no matter where they are copied -all are true
All are true
Which of the following is a possible reason why a spreadsheet solution may require changes? -it doesn't allow values to be easily changed -additional information is needed in it -it doesn't allow additional variables to be easily included in it -it is difficult to read and use -all reasons
All reasons
Which of the following is true for the SUMIFS( ) and AVERAGEIFS( ) functions? -criteria using a relational expression (>, <, >=, <=, <>) must be in quotes -mixed or absolute cell referencing often applies -the criteria ranges must all be the same array size (i.e., the same number of cells) -optional arguments are treated as an "AND" scenario meaning every criteria must be evaluated to true in order for the item to be included -all reasons
All reasons
Which of the following will find and count all instances of values that are less than 40 in the continuous range C2 to C10?
COUNTIF(C2:C10,"<40")
When writing formulas for spreadsheets in Excel, you do NOT need to pay attention to or worry about the order of precedence.
False
What will happen if, in an IF function, you accidentally omit the quotes from the value_if_true and value_if_false arguments as follows? =IF(C4, eligible, ineligible)
Excel will look for a range named eligible or ineligible (depending on which condition was met) and either returns the value from that range or gives a #NAME? error if the named range does not exist
Evaluate the following function: NOT(30<35)
FALSE
Data inputs should appear more than once in a spreadsheet.
False
Formulas or functions cannot be nested inside of each other in Excel.
False
In the AVERAGEIFS( ) function, the average_range argument comes last after the criteria_range and the criteria arguments.
False
In the COUNTIFS( ) function, every optional argument is treated as an "OR" scenario, meaning only one criteria must be evaluated to true in order for the item to be counted.
False
In this nested IF function example, =IF(C4<1000,0,IF(C4<2000,0.5,1))*C$1 if the value in C4 is 1200, the result is 0.
False
It is not possible to display multiple worksheets simultaneously on your screen in Excel.
False
It is not possible to nest Boolean logical functions for a logical test in the logical_test argument of the IF function in Excel.
False
One major difference between the SUMIF( ) and the SUMIFS( ) functions is that the sum_range argument comes last in the SUMIFS( ) function.
False
Only some, not all, of the predefined functions in Excel have an underlying algorithm that is programmed into the software.
False
The logical value TRUE is treated the same as the text string "TRUE".
False
The value that Excel displays for a cell is always the precise value you enter in the spreadsheet or the precise value resulting from a formula in that cell that is stored by the software.
False
When copying a formula down the column, it is necessary to put $ before each column address.
False
Which of the following functions has more than one type of argument?
ROUND( )
How would you refer to a cell that is on worksheet Sheet3 in the second column, third row in an Excel workbook?
Sheet!3B
How would you refer to a cell that is on worksheet Sheet5 in the fourth column, second row in an Excel workbook?
Sheet5!D2
Determine if the following formula equates to a TRUE or FALSE and mark the appropriate box (TRUE=True, FALSE=False) indicating the answer below: =OR( A1, A5<A4, MIN(A1:A5)=2 ) where A1=FALSE, A2=2, A3=4, A4=TRUE, A5=TRUE
TRUE
Which of the following is NOT true of the basic features of Excel?
The cell address first lists the row name and then the columns name
Arguments to the AND or OR functions many consist of any combination of cell references, values, and ranges such that each reduce to a single TRUE or FALSE value.
True
What are spreadsheets such as Excel used for in business?
all of the choices listed are uses of Excel
Only being able to adjust or change values in cells that do not contain formulas is a limitation of __________ .
goal-seeking
Why is it important to use cell references in functions or formulas in Excel?
it greatly improves the readability, maintenance and reliability of the spreadsheet
Which of the following is often true in cases where formulas are copied in two directions (across and down)?
mixed cell referencing is often needed for the formula to result in the correct solution