Databases chapter quizzes

If the formula in cell A8 is referencing the cell A7, and we are only copying the cell down to A11, what is the appropriate cell referencing of A7 if we don't want the reference to A7 to change (all cells A8:A11 should reference A7)?

$A7

Evaluate the following function: =IF(AND(10<20, 20>5, 30<10), C$2*D$3,0)

0

Given the following information, Cell A1=45 Cell A2 = blank (no value in cell) Cell A3 = MIN(A1, A2) What is the value in cell A3?

45

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

What is the value in the cell containing the formula =ROUND(67.5, -1) +ROUND(2.25, 1)?

72.3

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

='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 name 'Sheet 8' in row 2 and column C

='Sheet 8'!C2

How would you enter a formula to calculate the result of $15,000 times in 3 cell such that the formula produces no error and shows $45,000 (assume the cell is formatted to display as currency)?

=15000*3

Which of the following is an example of a relational expression that equates to TRUE or FALSE.

=A4>=COUNT(A3:A10)

Answer the following question using an Excel formula: Do BOTH Milo and Hina have cats?

=AND(C2, C3)

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"))

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+D2 >= A2, C2+D2 >= A2)

Answer the following question using an Excel formula: Does Milo have a dog or a cat (T/F)?

=OR(B2, C2)

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 ascending order (we want the smallest number to be the top rank: 1)

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

Answer the following question using an Excel formula: Does Burhan have more fruit (Apples + Oranges + Grapes) than Hunter (T/F)?

=SUM(B3:D3) > SUM(B2:D2)

What cels are contained in the range B8:C9?

B8, B9, C8, C9

What function should be used to count the number of items in a list that meet on and only one specific criterion?

COUNTIF()

Which of the following will find and count all instances of values that are larger than 20 in the continuous range B2 to B5

COUNTIF(B2:B5,">20")

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)

A cell in Excel formatted with percentage style. You enter =10/100 in the cell. What is the displayed value and the actual value in the cell?

Displayed value - 10% Actual value - 0.1

What is a possible cause of the error in cell A2 containing the formula =B4/(C2*C3) if the error shown is #DIV/0?

Either C2 or C3 is equal to 0

The cell containing the formula =20-5*2 evaluates to 30

FALSE

Evaluate the following function: =NOT(30<35)

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 argument

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 COUNTIF() and COUNTIFS() functions is that the count_range argument comes last in the COUNTIFS() functions

False

One major difference between the SUMIF() and the SUMIFS() functions is the sum_range argument comes last in the SUMIFS() function

False

Only some, not all, of the predefined functions in Excel have an underlying algorithim that is programmed into the software.

False

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

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

False

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

False

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

False

Only being able to adjust or change values in cells that do not contain formulas is a limitation of __________ .

Goal-seeking

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

No

Given a list of a finishing times at a marathon, which function should you use to rank each runner's place based on these finishing times?

RANK.EQ

Assuming B3 contains the value "Pencils" and cells C1:C4 contain numbers, =SUM(C1:C4, B3), is still valid in Excel. The numbers in cell C1:C4 will be summed and the text value in B3 will be ignored.

TRUE

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

A1= TRUE, A2= FALSE, A3 = TRUE What is the result of =NOT(AND(A1,A2,A3))

True

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

It is a good idea to put input values into separate sheets when solving a complex problem if it improves readability and usability

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

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 relational operators (>,<,>=,<=,<>) have a lower order of precedence than addition and subtraction in Excel formulas

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 should plan a workbook design before implementing a solution to avoid time consuming changes later

True

The logical IF function takes a Boolean value (TRUE or FALSE) as its first argument. This value can be:

all of the listed answers are possible options

What cells are contained in the range C5:D6

c5, c6, d5, d6

3-D cell referencing is used when

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

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

false

What is the difference between a formula and a function in Excel

functions are predefined formulas that perform specific calculations

Which tool allows you to work backwards to determine what input value is required for a specific output value

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

What is wrong with the following formula if cell C4 contains the logical value of TRUE, cell C1 contains the text string eligible and cell C2 contains the text string ineligible? =IF(C4="TRUE", C1, C2)

since "TRUE" with quotes is a label or text string and not a logical value, it could lead to an incorrect result