Melbourne Client List

Each question below has one correct answer. Select the correct answer by changing the FALSE to TRUE. $3$ The Last Order Dates have been changed to centre align, by default they would align:

A Top Left

FALSE

B Top Right

FALSE

C Bottom Left

FALSE

D Bottom Right

$4$ the Formula $=$AVERAGE$($J$16)$ will equal:

A $41145\mathrm{.}6$

FALSE

B $55552$

FALSE

C #DIV$/0!$

FALSE

D $0$

FALSE

$5$ Which syntax is NOT correct for adding values in cells $J6,J7$ and $J?$

A $\frac{?}{S}=UM(J6,J7,J8)$

FALSE

B $\frac{?}{S}=UM(J6$:$J7)+J8$

$C=J6+J7+J8$

FALSE

D $=J6$:$J8$

FALSE

FALSE

$6=90-10\%$ returns which answer?

A $80$

FALSE

B $81$

FALSE

C $89\mathrm{.}9$

FALSE

D $89\mathrm{.}1$

FALSE

$7$ Which of the following would calculate the most recent Order Date?

A $=$ RECENT$($C$6$:C$15)$

B $=$DATE$($C$6$:C$15)$

C $=$VLOOKUP$($C$6$:C$15)$

D $=$MAx$(C6$:$C15)8$ The cells H$8$:$18$ are empty. What will the formaula $=$COUNT$($H$6$:$115)$ return?

A $0$

B An error

C $9$

D $18$

$9$ Which of the following statements about named ranges is true?

A Named ranges automatically extend to include values added directly to the right.

FALSE

B Named ranges automatically extend to include values added directly below.

C If you change the name or range of a named range, all formulas using it will update.

D If you delete a named range, formulas will revert to using original cell references.

$10$ Which of the following options is NOT available on the Tables ribbon?

A Insert Slicer

B Remove Duplicates

C Add Column

D Convert to Range

$11$ When you first opened this workbook, which was true?

A There are no tables

B There are no named ranges or tables

C There are no named ranges

D There are more tables than named ranges

$12$ Which chart type would be the best to show daily stock prices over a year?

A Pie Chart

B Line Chart

C Stacked Bar Chart $13$ Which Highlight cell values Conditional formatting rule could have been applied to H$6$:H$15?$

A Greater Than

FALSE

B Less Than

C Duplicates

D Equal To

$14$ Which calculation will return the number of customers who have NOT placed an order in the last $90$ days?

A $=$ COUNTIF$($D$6$:D$15,>90)$

FALSE

B $=$ COUNTIFS$($D$6$:D$15,">90")$

C $=$ COUNTIFS$($D$6$:D$15)>90$

D $=$ COUNTIF$($D$6$:D$15,90)$

$15$ The following formula $=$AVERAGEIFS$($D$6$:D$15,$F$6$:F$15,">3188")$ will return...

A $2$

B $16$

C $30$

D An Error

FALSE

$16$ What will the formula $=|F(16>0,16**J6,0)$ return?

A $0$

B $200$

C $2000$

D FALSE

FALSE

$17$ The correct formula to calculate if Medium and Large stores achieved their targets is$?$

A $=$IF$($H$6=$"Large",IF$($J$6>50000,"$Y$","$N$"),$IF$($J$6>35000,"$Y$","$N$"))$

B $=$IF$($AND$($H$6=$"Large",J$6>50000),"$Y$",$AND$($H$6=$"Medium",J$6>35000,"$Y$"),"$N$")$

C $=$IF$($OR$($H$6=$"Large",J$6>50000),"$Y$",$OR$($H$6=$"Medium",J$6>35000,"$Y$"),"$N$")$

D $=$IFS$($H$6=$"Large","Y$",$J$6>50000,"$Y$","$N$",$H$6=$"Medium","Y$",$J$6>35000,"$Y$","$N$")18$ Which formula would calculate the total spend for just the Northern and Southern Metro Areas?

A $=$SUMIF$($E$6$:E$15,$J$6$:J$15,$"Northern Metro" & "Southern Metro"$)$

B $=$SUMIFS$($J$6$:J$15,$E$6$:E$15,$OR$("$Northern Metro","Southern Metro"$))$

$C==$SUMIFS$($J$6$:J$15,$E$6$:EEastern Metro"$)$

FALSE

D $=$SUMIF$($J$6$:J$15,$E$6$:E$15,$"Northern Metro" AND "Southern Metro"$)$

FALSE

FALSE

FALSE

$19$ Using the formula $=$ VLOOKUP $(3055,F6$:$K15,5)$ to return the last year spend for client at postcode $3055$ is incorrect...

A because $3055$ should be in quotation marks, i$.$e$."3055"$

FALSE

B because the look up range should be B$6$:K$15$

FALSE

C because it should be an exact match

FALSE

D because it should use absolute cell referencing

FALSE

$20$ The formula $=$VLOOKUP$("$C$1042",$B$6$:J$15,$IF$($D$12>90,5,9),0)$ will return

A $51104$

B $3185$

C $137$

D $9$

FALSE

FALSE

FALSE

FALSE