EXCEL CRASH COURSE EXAM ACTUAL TEST PAPER QUESTIONS CORRECT ANSWERS
GRADED A PLUS
Question:
To SUMPRODUCT an array where you only want certain criteria to be output.
Answer:
use the TRUE/FALSE (1,0) test as the first array, then the SUMPRODUCT arrays. Where the data
points don't meet the boolean test, they will be multiplied by 0 and therefore not summed SUMIF
requires hardcodes. Answer: to be in quotations ABS. Answer: the absolute value of a cell
CEILING. Answer: allows you to round to a specified level of signficance FLOOR. Answer: rounds
down to a specified level of significance ROUND. Answer: round to a set number of decimal places
MIN. Answer: returns the smallest value in a set of values MAX. Answer: returns the largest value
in a set of values COUNT. Answer: counts the number of cells that contain numbers but not text
COUNTA. Answer: counts the number of cells with numbers and text COUNTIF you have to.
Answer: put > and < in quotation marks then & to concatenate the reference PV vs NPV. Answer:
PV assumes constant payments whereas NPV cash flows can be different from period to period
NPV assumptions (2). Answer: 1) equal periods between payments
Question:
2) payments occur at the end of period When does XNPV perform better than NPV?
Answer:
When the cashflows are not paid in equal time periods What is not discounted in XNPV? Answer:
The first cashflow IRR. Answer: the discount rate that makes the NPV of future cashflows zero Why
is XIRR different to IRR? Answer: IRR assumes that time periods between cashflows are equal,
XIRR does not LEN. Answer: returns the number of characters in a text string LEFT. Answer:
Returns the specified number of characters from the start of a text string UPPER/LOWER. Answer:
converts text to all upper or lower case TRUNC. Answer: truncates to the number of digits specified
(decimal places) Flash fill. Answer: Alt +A + F + F DATEVALUE. Answer: turns text to date
format VALUE. Answer: to get Excel to treat numbers extracted as text as numbers Sorting.
Answer: Alt + A + S + S Subtotal. Answer: Alt + A + B Filter. Answer: Alt + A + T, to remove
filters Alt + A + T Insert Pivot Table. Answer: Alt + N + V + T Record and stop macro. Answer: Alt
+ L + R COUNTIF. Answer: counts cells with one criteria COUNTIFS. Answer: counts cells with
multiple AND criteria, i.e. count if the range is above 20 AND greater than the average COUNTIF
this OR that. Answer: Write a regular COUNTIF formula to count each item individually, and then
GRADED A PLUS
Question:
To SUMPRODUCT an array where you only want certain criteria to be output.
Answer:
use the TRUE/FALSE (1,0) test as the first array, then the SUMPRODUCT arrays. Where the data
points don't meet the boolean test, they will be multiplied by 0 and therefore not summed SUMIF
requires hardcodes. Answer: to be in quotations ABS. Answer: the absolute value of a cell
CEILING. Answer: allows you to round to a specified level of signficance FLOOR. Answer: rounds
down to a specified level of significance ROUND. Answer: round to a set number of decimal places
MIN. Answer: returns the smallest value in a set of values MAX. Answer: returns the largest value
in a set of values COUNT. Answer: counts the number of cells that contain numbers but not text
COUNTA. Answer: counts the number of cells with numbers and text COUNTIF you have to.
Answer: put > and < in quotation marks then & to concatenate the reference PV vs NPV. Answer:
PV assumes constant payments whereas NPV cash flows can be different from period to period
NPV assumptions (2). Answer: 1) equal periods between payments
Question:
2) payments occur at the end of period When does XNPV perform better than NPV?
Answer:
When the cashflows are not paid in equal time periods What is not discounted in XNPV? Answer:
The first cashflow IRR. Answer: the discount rate that makes the NPV of future cashflows zero Why
is XIRR different to IRR? Answer: IRR assumes that time periods between cashflows are equal,
XIRR does not LEN. Answer: returns the number of characters in a text string LEFT. Answer:
Returns the specified number of characters from the start of a text string UPPER/LOWER. Answer:
converts text to all upper or lower case TRUNC. Answer: truncates to the number of digits specified
(decimal places) Flash fill. Answer: Alt +A + F + F DATEVALUE. Answer: turns text to date
format VALUE. Answer: to get Excel to treat numbers extracted as text as numbers Sorting.
Answer: Alt + A + S + S Subtotal. Answer: Alt + A + B Filter. Answer: Alt + A + T, to remove
filters Alt + A + T Insert Pivot Table. Answer: Alt + N + V + T Record and stop macro. Answer: Alt
+ L + R COUNTIF. Answer: counts cells with one criteria COUNTIFS. Answer: counts cells with
multiple AND criteria, i.e. count if the range is above 20 AND greater than the average COUNTIF
this OR that. Answer: Write a regular COUNTIF formula to count each item individually, and then
