Intermediate

Question 1

Formula that will multiply corresponding components in the given arrays, and will then return the sum of the products.

Answer 1

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

Question 2

Option that allows you to restrict the type of data and number of characters that the user enters into a cell. If a person ignores limitations and types them in anyway, an error message will pop up.

Answer 2

Go to Data tab, click on Data Validation.

Question 3

Formula that will return the absolute value of a number in the location you desire

Answer 3

= ABS(number)

Question 4

Formula that returns the character specified by a number. It automatically translates code page numbers into characters.

Answer 4

=CHAR (number)

Question 5

Formula removes all characters that cannot be printed

Answer 5

=CLEAN(cell reference)

Question 6

Formula that returns a code (numeric) for the first character used in a text string

Answer 6

=CODE(“text”)

Question 7

Formula that compares two text strings and returns the word TRUE if they are exactly the same, or FALSE if they are not.

Answer 7

=EXACT(text1, text2)

Question 8

Formula that returns the arithmetic average (aka mean) of a list of numbers. Even if there are letters, it counts them as zero.

Answer 8

=AVERAGEA(value1, value2,…)

Question 9

Formula that calculates the arithmetic average (aka mean) of a set of values, only of values that meet certain criteria.

Answer 9

=AVERAGEIF(range, criteria, [average_range]

Question 10

Formula that calculates the arithmetic average (aka mean) of a set of values, only of values that meet multiple criteria.

Answer 10

=AVERAGEIFS(average_range,criteria_range1, criteria1, [criteria_range2, criteria 2].

Question 11

An informative formula that tells you about the contents, location, formatting, and properties of a specific cell.

Answer 11

=CELL(“address”, cell location), to find the location of the cell
=CELL(“col”, cell location), to find the column number.
=CELL(“row”, cell location), to find the row number
=CELL(“type”, cell location), where b is for blank, l is for text, and v is for value.
=CELL(“prefix”, cell location), where it will return a ‘ for left, ^ for centre, and “ for right. When there is nothing, it will be displayed as numeric entries.
=CELL(“width”, cell location), to find out the width of a cell.
=CELL(“format”, cell location), which will tell you the number format of the cell. It is necessary to download the number codes for format for future reference.
=CELL(“parentheses”, cell location), where 1 is yes and 0 is no.
=CELL(“color”, cell location,) will tell you if the cell is formatted for coloured negatives. It will return 1 for yes, 0 for no.
=CELL(“protect”), will tell you if the cell is locked (1) or unlocked (0).
=CELL(Filename, D5), will tell you the name of the file.

Question 12

Formula that will return the Kth largest value in a previously defined data set. You can use this function to select a value based on its standing when compared to the other values, including the highest score, second-place or third-place score.

Answer 12

=LARGE(array,k)

Question 13

Formula that returns the largest value in a set of values.

Answer 13

=MAX(number1, number 2,…)

Question 14

Formula that returns the median number of a set of numbers.

Answer 14

=MEDIAN(number1, number 2,…)

Question 15

Formula that returns the smallest value in a set of values.

Answer 15

=MIN(number1, number 2,…)

Question 16

Formula that will return the most repeated value in an array or range of data.

Answer 16

=MODE(number1, number 2,…)

Question 17

Formula that allows you to obtain a value converted to a number. This is relevant for numbers, a date, true or false, or an error value. Anything else will return 0.

Answer 17

=N(value)

Question 18

Formula that returns random integer numbers between minimum and maximum limits you specify. A different random number is calculated every time.

Answer 18

=RANDBETWEEN(bottom, top)

Question 19

Formula that returns the rank of a number being referred to in a list of values, taking into account the size of the number relative to other values in a list.

Answer 19

=RANK(number,ref,[order])

Question 20

Sometimes you have duplicate values in a range of data that are not useful to you. Rather than deleting them one by one you can remove duplicate values using an Excel function.

Answer 20

Select a cell or a range of cells. Then go to the Data tab, and find the Data Tools group. Here, click Remove Duplicates. Then, under Columns you can select one or more columns where you would like data removed. You can select all columns by clicking Select All. You can clear all columns by clicking Unselect All.

Question 21

The formula for rounding a number up is:

The formula for rounding down a number is:

To round a number to the nearest number or nearest fraction, you can use the following formula:

Answer 21

=ROUNDUP(argument1, argument2)

=ROUNDDOWN(argument1, argument2)

=ROUND(argument1, argument2)

The first argument is the number to be rounded. The second argument is the number of decimal places to which you want to round the result (0 is valid if you only want whole numbers).

Question 22

To change the capitalisation of certain text, you can use a formula instead of wasting time deleting and retyping everything.

Proper case refers to capitalising the first letter in a string and any letters that follow are lower case, like you might want to do for a title or name.

Answer 22

=LOWER(reference value)

=UPPER(reference value)

=PROPER(reference value)

Question 23

Some symbols and special characters are not available on the keyboard. Some of these include the copyright symbol (©), or trademark symbol (™), or Unicode characters, among others. In these cases, you can use the Symbol dialog box.

Answer 23

click the cell where you want the symbol inserted. Then, on the Insert tab, find the Text group and click on Symbol.

Question 24

if you want to keep your text in a limited amount of cells.

Answer 24

highlight the cells, click fill, justify, ok