Mod 5 - Advanced Excel Functions

Question 1

What do you use to ensure excel is interpreting a label, such as, + tax, or = total, correctly?

Answer 1

Use an apostrophe before the label so excel will interpret it correctly.
ex. ‘+ tax

Question 2

What do you use so something is interpreted as text and not a formula?

Answer 2

Use quotation marks surrounding the phrase.

ex. “=Total”

Question 3

What is concatenation?

Answer 3

It is when two or more values are joined together and is often used to combine a few pieces of text that reside in different cells.
=CONCATENATE(text1, text2, etc.)
ex. First + middle + last name > reads as first middle last in a new cell

Question 4

What is an ampersand (&) used for?

Answer 4

It is similar to the CONCATENATE function as it combines different text strings or values.
ex. D5 = 113
=”Your total is”&D5 reads as: Your total is 113

Question 5

What is the TEXT function?

Answer 5

It takes a number and converts it to a certain form.
=TEXT(value, format)
ex. =”Your total is”&TEXT(D5, “$0.00”)&”.”
reads as: Your total is $113.00.

Question 6

What is the UPPER function?

Answer 6

It changes words in a cell to uppercase lettering.
=UPPER(text)
ex. =UPPER(C7)

Question 7

How do you select part of a sentence to be shown?

Answer 7

Can use LEFT, RIGHT, MID. 
=LEFT(text, number of characters)
=MID(text, start at letter number, number of characters)
ex. LEFT(C9, 20)
=MID(C9, 10, 10)

Question 8

What is the FIND function?

Answer 8

It finds a certain part of the text and tells you which character it is.
=FIND(find text, within text)
ex. =FIND(“@”, C13)

Question 9

How do I reference a cell from one worksheet in another?

Answer 9

Use an exclamation mark after the sheet name.

ex. Sheet1!B2

Question 10

What are the possible Boolean values?

Answer 10

everything is either true or false

Question 11

What are the 3 primary Boolean operators?

Answer 11

or, and, not

Question 12

How does NOT work?

Answer 12

Basically opposites - you cannot have something.

not(x) is true, then x is false
not(x) is false, then x is true

Question 13

How does AND work?

Answer 13

You must get both items to be true.
ex. you get fries AND salad with a meal

and(x, y) is true if both x is true and y is true.
and(x, y) is false if x is false
and(x, y) is false if y is false

Question 14

What kind of OR does Boolean logic use?

Answer 14

It uses an inclusive OR where you can have either or both and it is true. (exclusive or is when you may have one or the other but not both)

Question 15

How does OR work?

Answer 15

You can have 1 of the 2 or both and it will be true.

or(x, y) is true if x and y is true
or(x, y) is true if x is true
or(x, y) is true if y is true
or(x, y) is false if both x is false and y is false

Question 16

Which operators produce Boolean results?

Answer 16

= … will show true or false

ex. =8>9 shows: FALSE

Question 17

What is the IF function?

Answer 17

=IF(logical test, value if true, value if false)

ex. =IF(D7=”Y”, C7*HST, 0)

Question 18

What is the ISBLANK function?

Answer 18

It returns true if a cell is empty, otherwise it returns false.
=ISBLANK(value)

ex. =ISBLANK(C7)

Question 19

What is the ISERROR function?

Answer 19

Can be used with the IF function to perform a different action if an error occurs - not very efficient so often an IFERROR function is used.

Question 20

Why are IS functions useful?

Answer 20

It provides you with information on a cell before performing a calculation or other action.

Question 21

What is the IFERROR function?

Answer 21

=IFERROR(value, value if error)

ex. =IFERROR(AVERAGE(E3:E13), 0)

Question 22

What is the COUNTIF function?

Answer 22

It allows you to count the number of cells within a range that meet a specified condition.
=COUNTIF (range, “criteria”)*

ex. =COUNTIF (E4:E103, “>1000”)
* can use COUNTIFS for multiple criteria

Question 23

What is the AVERAGEIF function?

Answer 23

It returns an average of all numbers in a range that meet a specified condition.
=AVERAGEIF(range, “criteria”, average range)

ex. =AVERAGEIF($C$4:$C$103, G4, $E$4:$E$103)

Question 24

What is the VLOOKUP function?

Answer 24

It uses a search key to find an entry with each cell acting as one entry.
=VLOOKUP(lookup value, table, column number, (in)exact)
aka
=VLOOKUP(lookup value/search key, table to search in, column to find the value in with the left most column being 1, whether to find a close match (true) or exact match (false))

ex. =VLOOKUP(C4, tax_table, 2, FALSE)

Question 25

What is the SUM function?

Answer 25

It adds values together.
=SUM(number 1, number 2, etc.)

ex. =SUM(B4, 7, D12)

Question 26

What is the SUMIF function?

Answer 26

It adds values together that meet a certain criteria.
=SUMIF(range, criteria, sum range)

ex. =SUMIF(B2:B7, “John”, C2:C7)
Only sums the values in C2:C7 that have cells corresponding to “John” in cells B2:B7.