Mod 5 - Advanced Excel Functions Flashcards
What do you use to ensure excel is interpreting a label, such as, + tax, or = total, correctly?
Use an apostrophe before the label so excel will interpret it correctly.
ex. ‘+ tax
What do you use so something is interpreted as text and not a formula?
Use quotation marks surrounding the phrase.
ex. “=Total”
What is concatenation?
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
What is an ampersand (&) used for?
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
What is the TEXT function?
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.
What is the UPPER function?
It changes words in a cell to uppercase lettering.
=UPPER(text)
ex. =UPPER(C7)
How do you select part of a sentence to be shown?
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)
What is the FIND function?
It finds a certain part of the text and tells you which character it is.
=FIND(find text, within text)
ex. =FIND(“@”, C13)
How do I reference a cell from one worksheet in another?
Use an exclamation mark after the sheet name.
ex. Sheet1!B2
What are the possible Boolean values?
everything is either true or false
What are the 3 primary Boolean operators?
or, and, not
How does NOT work?
Basically opposites - you cannot have something.
not(x) is true, then x is false
not(x) is false, then x is true
How does AND work?
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
What kind of OR does Boolean logic use?
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)
How does OR work?
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
Which operators produce Boolean results?
= … will show true or false
ex. =8>9 shows: FALSE
What is the IF function?
=IF(logical test, value if true, value if false)
ex. =IF(D7=”Y”, C7*HST, 0)
What is the ISBLANK function?
It returns true if a cell is empty, otherwise it returns false.
=ISBLANK(value)
ex. =ISBLANK(C7)
What is the ISERROR function?
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.
Why are IS functions useful?
It provides you with information on a cell before performing a calculation or other action.
What is the IFERROR function?
=IFERROR(value, value if error)
ex. =IFERROR(AVERAGE(E3:E13), 0)
What is the COUNTIF function?
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
What is the AVERAGEIF function?
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)
What is the VLOOKUP function?
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)
What is the SUM function?
It adds values together.
=SUM(number 1, number 2, etc.)
ex. =SUM(B4, 7, D12)
What is the SUMIF function?
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.
