CSE Exam 1

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? 64 unlimited 8 128 4

64

Data inputs should appear more than once in a spreadsheet False True

False

Does this IF function return the value of 20? A1= FALSE (a boolean value) =IF(A1="False",20,5) True False

False

Evaluate the following function: NOT(30<35) FALSE AND(TRUE) OR(NOT(A),NOT(B)) TRUE

False

What is a possible cause of the error in cell A2 in Sheet1 containing the formula =SUM(10, Sheet2!F4) if the error shown is #REF! ? Sheet2!F4 is not a valid cell reference. The incorrect function is being used. 10 is not allowed in the SUM function. The cell width is too small to display the value.

Sheet2!F4 is not a valid cell reference

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 False

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 False

True

What is the difference between a formula and a function in excel? functions are predefined formulas that perform specific calculations functions never use numbers there is no difference formulas are user defined functions that perform various calculations formulas only use numbers

functions are predefined formulas that perform specific calculations

A 3-D formula in Excel allows you to simultaneously select multiple adjacent worksheets. True False

true

Evaluate the following function: =IF(AND(10<20, 20>5, 30<10), C$2*D$3,0) 0 No answer is correct C$2*D$3 #DIV/0!

0

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)? $25,000*2 25000x2 =$25,000*2 =25000*2

=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) =COUNTIF(C2:C10, "< C2") =COUNTIF(C2:C10, < C2) =COUNTIF(C2:10, "<&C2")

=COUNTIF(C2:C10, "<"&C2)

What function should be used to count the number of items in a list that meet one and only one specified criterion? COUNTA( ) COUNTIF( ) COUNT( ) SUMIF( ) all of the answers listed will provide a count of items that meet a specific criterion

COUNTIF( )

Which of the following functions has more than one type of argument? ROUND( ) SUM( ) AVERAGE( ) COUNT( ) MAX( )

ROUND ( )

Which of the following is NOT true of the basic features of Excel? rows are named by numbers columns are named by letters the intersection of a row and column is referred to as a cell The cell address first lists the row name and then the column name

The cell address first lists the row name and then the column name

Evaluate this function: ="CSE" ="cse" True False

True

Excel treats capital and small letters as the same, so specifying "USA" is the same as specifying "usa", "Usa", etc. True False

True

What types of questions do Boolean functions answer? Is the total cost of the trip within budget? Does the Boston trip cost more then the New York trip? Is travel the largest component of the total cost of the trip? Was last year's income greater than this year's income? all of the responses listed are the types of questions Boolean functions can answer

all of the responses listed are the types of questions Boolean functions can answer

When copying a formula down the column, it is necessary to put $ before each column address. True False

false

How do the AVERAGE( ), MIN( ), MAX( ), COUNT( ), and SUM( ) functions handle cells in a range that are blank or contain labels? they are ignored they are treated the same as any arithmetic values it depends on the cell format it doesn't matter what is in the cell it depends on the size of the range used

they are ignored

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 False

true

Assuming B3 contains the value "Text" and cells A1:A4 contain numbers, =SUM(A1:A4, B3) is still valid in Excel. True 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 False

true

When you change the format of how a cell is displayed, it doesn't affect the actual value stored by the computer. True False

true

named ranges are always treated as absolute cell references true false

true

Relational operators compare two values to determine if the relational expression is TRUE or FALSE. True False

True

Only being able to adjust or change values in cells that do not contain formulas is a limitation of __________ . 3-D cell referencing input/output analysis transposing using multiple worksheets goal-seeking

goal seeking

How do you reference a cell from a different worksheet, other than the one you are currently working on, if the desired cell is on a sheet named 'Other Sheet' in row 5 and column H? =OtherSheet!H5 ='Other Sheet'5H ='Other Sheet'!H5 ='Other Sheet'&H5

='Other Sheet'!H5

How do you reference a cell from a different worksheet, other than the one you are currently working on, if the desired cell is on a sheet named 'Sheet 8' in row 2 and column C? ='Sheet 8'!C2 =Sheet8!C2 ='Sheet 8'&C2 ='Sheet 8'!2C

='Sheet 8'!C2

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 Excel will default to putting in zero (0) for the if_value_true argument and zero (0) for the if_value_false argument when the condition is met Excel will not allow you to complete the function Excel will automatically put quotes around these for you and the function will execute without error

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

In this nested IF function example, =IF(C4<1000,0,IF(C4<2000,2,1))*C$1 if the value in C4 is 1200, the result is 1*C$1. False True

False

It is possible to change the orientation of the data in a worksheet using paste special with values only and the transpose option. True False

True

The relational operators (>, <, =, >=, <=, <>) have a lower order of precedence than addition and subtraction in Excel formulas. True False

True

When using a specific text value as the criteria type in a COUNTIF( ) function, that text value must be placed in double quotes as the first argument to COUNTIF( ). True False

False

When writing formulas for spreadsheets in Excel, you do NOT need to pay attention to or worry about the order of precedence. True False

False

You are required to provide three arguments to the IF function in Excel. True False

False

The cell containing the formula =4*12-2 > 100/2+2 will evaluate to TRUE. True False

False

The formula =SUM(Job1!B15:Job6!B15) works if the sheets are not adjacent to each other in workbook. False True

False

The logical value TRUE is treated the same as the text string "TRUE". True False

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. True False

False

how would you refer to a cell that is on a worksheet Sheet5 in the fourth column, second row in an excel workbook? 5!2D Sheet5!D2 Sheet5!2D Sheet 52D 5:D2

Sheet5!D2

Given the table: Answer the following question using an Excel formula: Does Milo have a dog and a cat (T/F)? =AND(B2, C2) =OR(B2, C2) =AND(B2) + AND(C2) =NOT(B2 + C2)

=AND(B2, C2)

Aaron will only go outside if the temperature is between 62 and 78 degrees inclusive. If cell B2 contains the current temperature, write a formula that will display "may go outside" if the temperature is within the range; otherwise, it will display "will not go outside". =IF(OR(B2>=62, B2<=78), "may go outside", "will not go outside") =IF(AND(B2>=62, B2<=78), "may go outside", "will not go outside") =IF(B2>=62, IF(NOT(B2<=78), "may go outside"), "will not go outside") =IF(AND(B2<=62, B2>=78), "may go outside", "will not go outside")

=IF(AND(B2>=62, B2<=78), "may go outside", "will not go outside")

Elina has a GPA given in cell G6. If Elina has a GPA greater than 3.0, then she is in Good Standing. If Elina has a GPA less than or equal to 3.0, but greater than 2.0, then she is in OK Standing. Otherwise, she is in Bad Standing. The result should ONLY SHOW either "Good Standing", "OK Standing" or "Bad Standing". Which formula correctly shows this? =IF(G6 > 3.0, "Good Standing"), IF(G6 > 2.0, "OK Standing", "Bad Standing") =IF(G6 > 3.0, IF(G6 > 2.0, "OK Standing", "Bad Standing"), "Good Standing") =IF(G6 > 3.0, "Good Standing", IF(G6 > 2.0, "OK Standing", "Bad Standing")) None of the choices listed are correct.

=IF(G6 > 3.0, "Good Standing", IF(G6 > 2.0, "OK Standing", "Bad Standing"))

If you are given a list of numbers (A1:C10), and you want the 23rd largest number in that list, which formula is correct? =RANK.EQ(A1:C10, 23, 1) =RANK.EQ(A1:C10, 23) =SMALL(A1:C10, 23) =LARGE(A1:C10, 23)

=LARGE(A1:C10, 23)

Given the table: Answer the following question using an Excel formula: Do BOTH Milo and Hina NOT have cats? =OR(NOT(C2), NOT(C3)) =NOT(AND(C2, C3)) =NOT(C2, C3) =OR(NOT(C2, C3))

=NOT(AND(C2, C3))

Given the table: Answer the following question using an Excel formula (T/F): Are you able to pay off your debt if you are able to use all the money in your checking account, and only one of your savings accounts? So, you can either use Savings 1 & Checking or Savings 2 & Checking to pay off your debt. =OR(B2+C2+D2 >= A2) =NOT(B2+C2+D2 >= A2) =AND(B2+D2 >= A2, C2+D2 >= A2) =OR(B2+D2 >= A2, C2+D2 >= A2)

=OR(B2+D2 >= A2, C2+D2 >= A2)

Which is the correct formula to determine the rank of the number in cell B2 within the list of numbers in range B2:B10 with respect to a descending order (we want the largest number to be the top rank: 1)? =RANK.EQ(B2, B2:B10, 0) =RANK.EQ(B2:B10, B2, 1) =RANK.EQ(B2, B2:B10, 1) =RANK.EQ(B2:B10, B2, 0)

=RANK.EQ(B2, B2:B10, 0)

If you are given a list of numbers (B11:C20), and you want the 6th smallest number in that list, which formula is correct? =LARGE(B11:C20, 6) =RANK.EQ(B11:C20, 6, 1) =RANK.EQ(B11:C20, 6) =SMALL(B11:C20, 6)

=SMALL(B11:C20, 6)

Which of the following is true for the SUMIFS( ) and AVERAGEIFS( ) functions? optional arguments are treated as an "AND" scenario meaning every criteria must be evaluated to true in order for the item to be included criteria using a relational expression (>, <, >=, <=, <>) must be in quotes the criteria ranges must all be the same array size (i.e., the same number of cells) mixed or absolute cell referencing often applies all of the answers listed are true

all of the answers listed are true

Why use the SUM function with a range of cells when adding the values together accomplishes the same thing? range is adjusted when copying function to another cell it requires no modification if row or column is added to range all of the choices listed are reasons to use the SUM function it saves time

all of the choices listed are reasons to use the SUM function

Which of the following is true about cell referencing in Excel? mixed cell referencing is when either the row or column is made absolute (anchored in) by default, all cell references are relative references (when copied they change based on the relative position of the rows and columns) absolute references remain constant no matter where they are copied an absolute cell reference is a cell address that contains a $ in the row and column all of the choices listed are true

all of the choices listed are true

The logical IF function takes a Boolean value (TRUE or FALSE) as its first argument. This value can be: the result of a Boolean function the result of a relational expression all of the listed answers are possible options a reference to a cell containing a Boolean value

all of the listed answers are possible options

Which of the following is a possible reason why a spreadsheet solution may require changes? it doesn't allow additional variables to be easily included in it all of the listed choices are possible reasons it is difficult to read and use it doesn't allow values to be easily changed additional information is needed in it

all of the listed choices are possible reasons

Which of the following tasks performed by the AND, OR, or NOT functions is NOT true? the NOT function will return TRUE if the single argument is FALSE or FALSE if the single argument is TRUE the OR function will return TRUE if at least one of the arguments is TRUE all of the tasks listed are true the AND function will return TRUE if all of the arguments are TRUE

all of the tasks listed are true

3-D cell referencing is used when _________________ . comparing and aggregating data where each case is on a different sheet in the workbook you have exactly three cases in multiple worksheets only the inputs are placed in multiple files inputs and outputs are all placed in one worksheet comparing and aggregating data where each case is on the same worksheet

comparing and aggregating data where each case is on a different sheet in the workbook

What cells are contained in the range D4:D8 d5, d6, d7 none of the choices are correct d4, d8 d4, d5, d6, d7, d8

d4, d5, d6, d7, d8

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 the formula or function won't work otherwise it is required when multiple cells are used in a formula or function you won't get accurate results otherwise cell referencing is a requirement in all formulas

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)? -absolute cell referencing should always be used -mixed cell referencing should never be used -mixed cell referencing is often needed for the formula to result in the correct solution -absolute cell referencing should never be used -cell referencing does not apply

mixed cell referencing is often needed for the formula to result in the correct solution

Which tool allows you to work backwards to determine what input value is required for a specific output value? transposing grouping sheets absolute cell referencing goal-seeking 3-D referencing

goal seeking