EXCEL

Question 1

#########

Answer 1

Not an error, usually means column not wide enough, also results from negative date and time

Question 2

Not an error, usually means column not wide enough, also results from negative date and time

Answer 2

#########

Question 3

VALUE!

Answer 3

Formula cannot work because it was expecting different data type ( multiplying by text column will cause this error)

Question 4

Formula cannot work because it was expecting different data type ( multiplying by text column will cause this error)

Answer 4

VALUE!

Question 5

REF!

Answer 5

Cell results in reference cell that DNE

Question 6

Cell results in reference cell that DNE

Answer 6

REF!

Question 7

NAME?

Answer 7

Referring to a named range that DNE.
Using worksheet function that DNE.
Using text formula without including quotation marks (“text”)

Question 8

Referring to a named range that DNE.
Using worksheet function that DNE.
Using text formula without including quotation marks (“text”)

Answer 8

NAME?

Question 9

NUM!

Answer 9

Using an incorrect argument in worksheet function, or the number is too large/small for Excel

Question 10

Using an incorrect argument in worksheet function, or the number is too large/small for Excel

Answer 10

NUM!

Question 11

N/A!

Answer 11

VLOOKUP/HLOOKUP/MATCH with no match. Using NA() function.

Question 12

VLOOKUP/HLOOKUP/MATCH with no match. Using NA() function.

Answer 12

N/A!

Question 13

NULL!

Answer 13

Intersection of two ranges that DNE. Wrong range separator.

Question 14

Intersection of two ranges that DNE. Wrong range separator.

Answer 14

NULL!

Question 15

VLOOKUP( )

Answer 15

Looks for a given value in a vertical list, and once it has spotted that value, it would use that row and return the value from the specified column number

Question 16

Looks for a given value in a vertical list, and once it has spotted that value, it would use that row and return the value from the specified column number

Answer 16

VLOOKUP( )

Question 17

Syntax VLOOKUP( )

Answer 17

=VLOOKUP( look up value you are trying to find, left most column of the table, table array for the value, column index number which you want to fetch the matching value, exact match = false : approx match = true)

Question 18

IF( )

Answer 18

Evaluates a condition and returns one value if it’s TRUE and another if it’s FALSE

Question 19

Evaluates a condition and returns one value if it’s TRUE and another if it’s FALSE

Answer 19

IF( )

Question 20

syntax IF( )

Answer 20

=IF(logical_test, [value if true], [value if false])

Question 21

SUMIFS( )

Answer 21

Adds the cells in a range that meet multiple criteria

Question 22

Adds the cells in a range that meet multiple criteria

Answer 22

SUMIFS( )

Question 23

syntax SUMIFS( )

Answer 23

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)

Question 24

COUNTIFS( )

Answer 24

Applies criteria to cells across multiple ranges and counts the number of times all criteria are met

Question 25

Applies criteria to cells across multiple ranges and counts the number of times all criteria are met

Answer 25

COUNTIFS( )

Question 26

syntax COUNTIFS( )

Answer 26

=COUNTIFS(criteria_range1, criteria1, [criteria_range1, criteria 2], ... )

Question 27

INDEX( )

Answer 27

Returns the value from a table, based on specified row and column number

Question 28

Returns the value from a table, based on specified row and column number

Answer 28

INDEX( )

Question 29

syntax INDEX( )

Answer 29

=INDEX(array, row_num, [column_num])

Question 30

MATCH( )

Answer 30

Looks for value/string in a list, returns its position

Question 31

Looks for value/string in a list, returns its position

Answer 31

MATCH( )

Question 32

syntax MATCH( )

Answer 32

=MATCH(lookup_value, lookup_array, [match_type])

Question 33

SUMPRODUCT( )

Answer 33

Multiplies corresponding components in the given arrays, and returns the sum of those products

Question 34

Multiplies corresponding components in the given arrays, and returns the sum of those products

Answer 34

SUMPRODUCT( )

Question 35

syntax SUMPRODUCT( )

Answer 35

=SUMPRODUCT(array1, [array2], [array3], ... )