Module 5 Flashcards
How to make the cell interpret a text as text and not a formula
Single Apostrophe infront of it ‘
‘+text will read +text
Formula text vs reg text
‘+text is reg
=”+text” is formula now
Concatenation Operator
&
used when working with text,means glue these things together
=”Your total is”&D4
Put & between text things
=”Your total is” &D4&”Please pay!”
Convert a number to text
=TEXT(D4,”$0.00”)
Multiple Worksheets
Sheet name ! Cell reference is syntax
=Sheet1!B2+Sheet2!B2
Use ! to indicate sheet
-between sheets it is still relative references, if i have 3 sheets, and under b2 for the first two i put the number 100, if i drag on the third sheet, it would put 200 because it adds them like the formula above
-Single quotes go around the sheet name if there is spaces
Boolean Logic
Capital B
-True or False options
-can use < > = or things below
Operators that work on boolean values:
a) NOT
if x= T
not(x) = F
b) AND
if and(x,y)
for it to give T, BOTH x and y must be true
c) OR
if or(x,y)
only ONE has to be true to return TRUE, but if two true it returns TRUE
has a subcategory:
function XOR: means exclusive or,
if XOR(x,y)
same as OR except, if both are true, it returns FALSE
How is XOR written in fullhand
OR(AND(X,NOT(Y)),AND(NOT(X),Y))
Does IF function need to have a boolean value for the logical test parameter
YES
=IF(logical_test, [value_if_true], [value_if_false]
If u are wanting a function to return back a text, what must u do when u type it
” “
ie. =IF(D7>9, “y”, D4)
so if it is true, it will return the text y but if not it will reference d4
IF and Or used together
=IF(OR(D7=”Y”,A9=”Y”),”Discount”,”no discount”)
means, if d7= y or a9 = y, a discount text will be returned, but if both are false then no discount will be returned
Conditional Formatting button
Used when u want cell to change appearance if something is true
can use operations like < > = greater than equal to etc
-can make it light red fill with dark red text, etc stuff like that, can also add in symbols instead of colour.
-usually do this for a range of data
COUNTIF
=Countif(range, criteria)
criteria is in quotations
ex
=Countif(c10:c12, “>199”)
or
=COUNTIF(b10:b12),”ON”)
gives back a number/count
AVERAGEIF
=Averageif(range,criteria,[average_range])
the average range is the range of cells that we want to calculate the average for
the other parameters same as count if
COUNTIFS
=COUNTIFS(range,criteria1,range2,critera2,etc)
bascially saying count if there are some that have both as true
=VLOOKUP
=vlookup(lookup_value,table_array,col_index_num,[range_lookup])
lookup value: what we want to look for
table: which table
col_index_num: what column do we want to use (not the one with the lookup value)
range: two options to put here, either true:approx match, or false:exact match
What is the difference between true and false in the range_lookup of vlookup
True: gives approx match based on the value in the table that is smaller than the value give
ie: if my value is 88 but the range is 50 or 100, it will pick 50
HOWEVER, entries in first column must be sorted or else wont work
from smallest to largest
False: finds exact match, if not doesnt return anything
-table does not have to be sorted
