Excel functions

Question 1

=IF

Answer 1

=IF(logical_test, [value_if_true], [value_if_false])

logical test: condition one wants to test.

value_if_true: value that Excel will return if the condition is true.

value_if_false: value that it will return if the condition is false.

Example:
=IF(A2>100, “Yes, “No”)

Question 2

=IF output

Answer 2

Letters or numbers according to the [value_if_true] and the [value_if_false]

Question 3

=LEFT

Answer 3

=LEFT(text, num_chars)

text: text string to extract characters from.

num_chars: number of characters to extract.

Example:

=LEFT(A1, 2)

Question 4

=LEFT output

Answer 4

Specified numbers of characters.

Question 5

=RIGHT

Answer 5

=RIGHT(text, num_chars)

text: text string to extract characters from (cell).

num_chars: number of characters to extract.

Question 6

=RIGHT output

Answer 6

Specified number of characters.

Question 7

=MID

Answer 7

=MID(text, start_num, num_chars)

text: string extracting characters from.

start_num: the position in the text string to start extracting characters from - counting from the left.

num_chars: the number of characters you want to extract.

Example: =MID(A1,4,3)

Question 8

=MID output

Answer 8

Specific characters.

Question 9

=CONCAT

Answer 9

=CONCAT(text1, [text2], …)

text1: the first text value (a cell).

Example: =CONCAT(A2, “ “, B2, “, “, C2)
Equivalent: C2&” “&D2

Question 10

=TRIM

Answer 10

=TRIM(text)

Example: =TRIM(A1)

Question 11

=TRIM output

Answer 11

A specific text string with no spaces at the beginning nor the end. Only one space in the middle of words.

Question 12

=SUBSTITUTE

Answer 12

=SUBSTITUTE(text, old_text, new_text, instance_num)

text: text string to replace characters in.

old_text: the character to replace.

new_text: new character to replace it with.

=SUBSTITUTE(D2,”COMM”,”BUSI”)

Question 13

=SUBSTITUTE output

Answer 13

A new string with the specific “substitute text” in it.

Question 14

=REPLACE

Answer 14

=REPLACE(old_text, start_num, num_chars, new_text)

old_text: original text string to replace characters in.

start_num: the position within the text string to start replacing characters

num_chars: the number of characters to replace (# of characters to replace).

new_text: the new text string to replace the old characters with.

Example:
A1: hello
=REPLACE(A1, 2, 1, “o”)
=> hollo

Question 15

=COUNTSIF

Answer 15

=COUNTIFS(range1, criteria1, [range2, criteria2]…)

range1: the first range of cells to evaluate using criteria1.

criteria1: the condition that must be met by the cells in range1 for the cells to be counted.

range2, criteria2: optional - additional ranges and criteria to include in the count

Example:
=COUNTIFS(A1:A10,”>10”,A1:A10,”<20”)

Question 16

=COUNTIFS output

Answer 16

The number of cells that meets the criterias.

Question 17

=SUMIFS

Answer 17

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2,…

sum_range: range of cells to sum up based on the given criteria (column that has the data values)

criteria_range1: range of cells that contains the first set of criteria (the column with the representing criteria)

criteria1: The first criterion to use to filter the data in the criteria_range1.

Example: =SUMIFS(D4:D8,C4:C8,”>1000”)

Question 18

=SUMIFS output

Answer 18

A number that sums up values in a range based on multiple criteria.

Question 19

=VLOOKUP

Answer 19

=VLOOKUP(lookup_value, table_array,col_index_num, range_lookup)

lookup_value: the value to look up in the first column of the table.

table_array: the range of cells that makes up the table one is searching. The column of the range must contain the lookup value, and the columns to the right must contain the values one wants Excel to return.

col_index_num: the column number of the table from which one wants Excel to return a value.

-> TRUE = exact match.
-> FALSE = approximate match.

Example: =VLOOKUP(A7, A3:E:5, 4, FALSE)

Question 20

=LEN

Answer 20

=LEN(text)

text: the string to find the length for.

Question 21

=VLOOKUP output

Answer 21

A number from another column relative to the lookup value (both are in the same row).

Question 22

=LEN output

Answer 22

A number that represents the number of characters in the specific text string.

Question 23

=FIND

Answer 23

=FIND(find_text, within_text, [start-num])

find_text: the text string to find within another text string.

within_text: the text string in which to search for the find_text.

start_num: the character position within “within_text’ where the search should begin. If omitted, the default value is 1.

Example: =FIND(“M”,A1)
Another: =FIND(“M”,A23,FIND(“M”,A23)+1)

Question 24

=FIND output

Answer 24

Returns the value of the position of the first character of the ‘find_text’ text string begins (based on the start_num specified).

Question 25

=SEARCH

Answer 25

=SEARCH(find_text, within_text, [start_num])

find_text: the text string to find within another text string.

within_text: the string in which to search for the ‘find_text’ string (a cell).

Start_num (optional): the character position within the ‘within_text” where the search should begin.

Example: =SEARCH(“2?5”,A12)
where “?” = anything.

Question 26

=SEARCH output

Answer 26

The position of the first character of the found text string in the searched text string.

Question 27

=OR

Answer 27

=OR(condition1, condition2,…)

Example: =OR(A1>10, A1<-10)

Question 28

=OR output

Answer 28

TRUE if one of the conditions is true, and FALSE if both of the conditions are false.

Question 29

=AND

Answer 29

=AND(condition1, condition2,…)

condition1: logical expressions, or cell references that contain certain values.

condition 2: logical expressions, or cell references that contain certain values.

Example: =AND(A1>10, A1<100)

Question 30

=AND output

Answer 30

If the specific logical conditions are true -> TRUE
- Both or true.
If it is not -> FALSE
- One is false.
- Both are false.