Module 5

Question 1

How to make the cell interpret a text as text and not a formula

Answer 1

Single Apostrophe infront of it ‘
‘+text will read +text

Question 2

Formula text vs reg text

Answer 2

‘+text is reg
=”+text” is formula now

Question 3

Concatenation Operator

Answer 3

&

used when working with text,means glue these things together

=”Your total is”&D4

Put & between text things
=”Your total is” &D4&”Please pay!”

Question 4

Convert a number to text

Answer 4

=TEXT(D4,”$0.00”)

Question 5

Multiple Worksheets

Answer 5

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

Question 6

Boolean Logic

Answer 6

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

Question 7

How is XOR written in fullhand

Answer 7

OR(AND(X,NOT(Y)),AND(NOT(X),Y))

Question 8

Does IF function need to have a boolean value for the logical test parameter

Answer 8

YES

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

Question 9

If u are wanting a function to return back a text, what must u do when u type it

Answer 9

” “
ie. =IF(D7>9, “y”, D4)

so if it is true, it will return the text y but if not it will reference d4

Question 10

IF and Or used together

Answer 10

=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

Question 11

Conditional Formatting button

Answer 11

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

Question 12

COUNTIF

Answer 12

=Countif(range, criteria)

criteria is in quotations
ex
=Countif(c10:c12, “>199”)
or
=COUNTIF(b10:b12),”ON”)
gives back a number/count

Question 13

AVERAGEIF

Answer 13

=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

Question 14

COUNTIFS

Answer 14

=COUNTIFS(range,criteria1,range2,critera2,etc)

bascially saying count if there are some that have both as true

Question 15

=VLOOKUP

Answer 15

=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

Question 16

What is the difference between true and false in the range_lookup of vlookup

Answer 16

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