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