module 2

Question 1

if

Answer 1

Returns TRUE outcome if the condition is
correct, otherwise returns FALSE outcome

IF(A1 > 0, B1, C1)

“A1 > 0” Any value or expression that can be evaluated to
TRUE or FALSE

“B1” output of condition true

“C1” output if
condition false

Question 2

sumif

Answer 2

-The SUMIF function sums the values in a range that meet
criteria that you specify

= SUMIF (A3:A17, “>30”, B3:B17)

“A3:A1” is the range of cells that you want evaluated by criteria

“>30” is the criteria in the form of a number, expression, a cell
reference, text, or a function

“B3:B17” is the Sum-Range, the range to be summed if the criterion is
met in the Range column

Question 3

and

Answer 3

Returns TRUE if all conditions are correct
= AND(A1 > 0, B1 > 0)

“A1 > 0” The first condition that you want to test that can
evaluate to either TRUE or FALSE

“B1 > 0” Additional conditions that you want to test that can
evaluate to either TRUE or FALSE, up to a maximum of 255
conditions

Question 4

or

Answer 4

Returns TRUE if at least one of the conditions are
correct
= OR (A1 > 0, B1 > 0)

Question 5

vlookup

Answer 5

Looks vertically for the lookup value in a table
and returns the corresponding items from
another column

= VLOOKUP (B10, A5:B8, 2, False)

“B10” is the value that is being looked for

“A5:B8” is the table that is being looked in

“2” is the number of columns to move across once the value being looked for is found

“False” specifies whether you want an exact match or
not. False finds exact. True finds closest that is not
more than

Question 6

hlookup

Answer 6

HLOOKUP is the exact same function as VLOOKUP , but looks up data that has been formatted by rows instead of
columns.

Question 7

date

Answer 7

– Generates a serial number from the year (a), month

b), and day (c

Question 8

edate

Answer 8

– Returns the serial number of the date that is (b)
months away from (a)

= EDATE (1/3/2014, 3)

Question 9

today

Answer 9

– Returns the serial number of the current date

Question 10

year

Answer 10

– Returns the year of the given date (from 1900 to
1999)

= YEAR (1/3/2006)

Question 11

yearfrac

Answer 11

– Returns the fraction of a year between (a) and (b)

= YEARFRAC (1/3/2006, 21/7/2007)

Question 12

daverage

Answer 12

Returns the average of selected database
entries

=DAVERAGE (A3 : L13, 3, A17:L18)

• a is the range of cells that makes up the list or database

b is the column used in the function

c is the range of cells that contain the conditions you
specify

Question 13

dvar

Answer 13

Estimates variance based on a sample from

selected database entries

Question 14

dstdev

Answer 14

Estimates the standard deviation based on a

sample of selected database entries

Question 15

rank

Answer 15

Returns the rank of a number in a list of numbers
= RANK (2, A1:A50, 0)

“2” is the rank you are looking after

“A1:A50” is the list of numbers to look through

“0” specifies whether (a) is sorted by descending or
ascending order. If 0 then descending. If non-0 then
ascending

Question 16

arrays

Answer 16

• Array formulae enable the calculation of answers
using fewer steps than traditional formulae.

• Array formulae must be entered by typing Ctrl Shift
Enter.

• Array formulae creates the same formula over multiple cells.

Question 17

SUMIFS

Answer 17

– adds the cells specified by a given set of conditions or criteria

Question 18

MATCH

Answer 18

MATCH(Lookup_value,Lookup_array,MatchType)