exam review module 3

Question 1

how to avoid excel trying to interpret a formula

Answer 1

at apostrophes before and after.

‘+x

Question 2

how to tell excel to interpret as text

Answer 2

use quotation marks

Question 3

how to convert text to uppercase

Answer 3

=upper(c7) -> YOUR TOTAL AMOUNT IS 100 PLE….. (Converts text to uppercase)

Question 4

how to show the first 20 characters of a specific cell.

Answer 4

=Left(c9,20) -> would only show the first 20 characters of a specific cell.

Question 5

find text in the middle of something

Answer 5

mid(c9,10,5) -> text, starting number, number of characters

Question 6

C13 = bob@uwaterloo.ca
C14 = sally@uwaterloo.ca 

Extracting the @ sign out of one of these emails:

Answer 6

=Find(“@,C13) -> this will give you a number that represents which character in C13 has the “@“ symbol.

=Left(c13,FIND)”@“,C13)-1 -> C13 would now become “bob”

Question 7

how to use multiple work sheets

Answer 7

sheet tab is at the bottom left of a workbook - you can rename these sheets

when working between sheets you use relative references

but you can use absolute references by using $$

ex: B2+C2 -> B$2$+C$2$

Question 8

true or false

Answer 8

ex: (7=5)=False …. (7>5)=True

Question 9

Boolean

NOT

Answer 9

x=true -> not(x) = false

x=false -> not(X)=true

Question 10

Boolean

AND

Answer 10

x=t, y=t -> and(x,y)=t (only way and(x,y) will be TRUE)
x=t, t=f -> and(x,y) =f
x=f, y=t -> and(x,y)=f
x=f, y=f -> and(x,y)=f

Question 11

Boolean

OR

Answer 11

x=t, y=f -> or(x,y)=t
x=f, y=t -> or(x,y)=t
x=f, y=f -> or(x,y)=f
x=t, y=t -> or(x,y)=t

Question 12

Boolean

Exclusive OR

Answer 12

you only get one

Question 13

Boolean

Inclusive OR

Answer 13

you can have either, or both

x=t, y=t -> xor(x,y) = FALSE

Question 14

Boolean example

E7=$113
BIG=$100

Answer 14

=E7>BIG -> TRUE

Question 15

Boolean example

=if(D7,C7*HST,0

Answer 15

so =if(true),C7 does pay tax, because C7 is multiplied by the 0
so =if(false), C7 does not pay tax because C7 is multiplied by HST value.

=if uses the true or false, what happens if the value is true, and what happens when the value is false.

Question 16

boolean example

If will only interpret boolean values (true or false) it will not interpret Y or N. To get around this:

Answer 16

=If(D7=“Y”

Question 17

boolean example - discount

C7=$100
Discount = 10%
D7= good or bad customer

Answer 17

=if(D7=“Y”,C7*(1-Discount),C7)

Question 18

If(logical test, value if true, value if false)

Answer 18

Student name -> =B3, C3, D3

=B3&” “&if(isblank)(C3),,&” “)&D3

Question 19

if the cells contained no data for a particular student

Answer 19

if(iferror)(AVERAGE(E3:I3)),”not available”,average(E3:I3))

or (to simplify)

=iferror(average(E3:I3),0)

Question 20

give you a number of cells where their values are above 1000.

Answer 20

=COUNTIF(E4,E:10,”>1000”)

Question 21

give you a number of cells containing the words “ON”.

Answer 21

=COUNTIF(C4:C10,”ON”)

Question 22

give you an average for a specific range within a certain group. (Average order for AB).

Answer 22

=AVERAGEIF($C$4:C$10$,”G4,$E$4:$E$103)

Question 23

count only cells that are within a specific range, within a certain group, and are above 1000. COUNTIES is the same as COUNTIF, but with multiple criteria.

Answer 23

=COUNTIFS(C$4$:C$10$,G4,C$4$:C$10$,”>1000”)

Question 24

vlookup

Answer 24

=VLOOKUP(lookup_value, table array, col_index_num, [range_lookup])
=vlookup(value you’re trying to find, your range/array, column, true/false).

Question 25

Vlookup

if the value can’t be found, display error message not in table.

Answer 25

=iferror)(vlookup(“ON”,taxtable,2,FALSE),”not in table”)

Question 26

Example 2
Inexact or Exact lookup

Vlookup

Order Discount
500 2
700 3
100 5

Answer 26

=vlookup(10,discounts,2,TRUE)

Question 27

inexact vs exact lookup

Answer 27

(false=exact match, true=inexact match)

Question 28

true = inexact

false = exact

Answer 28

Table must be sorted from smallest to largest

Table does not have to be sorted.