excel

Question 1

spreadsheet program

Answer 1

computer program for organization, analysis, and storage of data

Question 2

name box

Answer 2

the box in the corner where you can see the cell names

Question 3

4 types of data in excel

+ alignment

Answer 3

logical type:TRUE or FALSE, aligned centre
number type: whole or decimal numbers, aligned right
text type: characters, aligned left
error type: #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!

Question 4

formulas

what do they contain?

Answer 4

equations that work in combination with data from other cells in the spreadsheet

combine data, functions, arithmetic ops, or logical comparisons

Question 5

function

Answer 5

preset formulas intended to carry out specific functions, ex. if, sum

Question 6

syntax

Answer 6

specifies arguments and the correct punctuation when using a function

Question 7

IF

purpose

Answer 7

tests for specific conditions, returns one value if true and one value if false

Question 8

nested IF

purpose

Answer 8

uses a combination of IFs for >2 possible results

Question 9

MAX

purpose

Answer 9

returns the largest value in a set of values

Question 10

MIN

purpose

Answer 10

returns the smallest value in a set of values

Question 11

SUM

purpose

Answer 11

sums the values

Question 12

AND

purpose

Answer 12

used to evaluate if ALL the arguments/logicals are true; returns TRUE if it is

Question 13

OR

purpose

Answer 13

tests if at least 1 condition is true; returns TRUE if it is

Question 14

COUNTIFS

purpose

Answer 14

counts the number of observations that satisfy a condition within a given range

Question 15

what do you need to add when comparing numbers within a IF, COUNTIFS, or SUMIFS?

Answer 15

quotation marks, ex. “>B3”

Question 16

SUMIFS

purpose

Answer 16

adds the values in a specified column that satisfy a condition

Question 17

COUNTIFS/SUMIFS

exceptions to syntax for COUNTIFS and SUMIFS (3)

Answer 17

1. cell references, ex. =COUNTIFS(A1:A5, C1) -> no “”
2. equality -> eliminate “=”
3. directly specifying the number, ex. COUNTIFS(A1:A5, 5) -> no “”

Question 18

VLOOKUP

purpose

Answer 18

finds a match in the first range of cells and returns a corresponding value from a cell on the same row

Question 19

VLOOKUP syntax:

what types of referencing should you use? which argument is optional?

Answer 19

use absolute referencing for table_array. range_lookup is optional: TRUE (approx) or FALSE (exact)

Question 20

VLOOKUP

formatting the lookup table for approx matches

Answer 20

first column has to be in ascending, specifying the smallest possible value for each category

Question 21

VLOOKUP

possible N/A errors?

Answer 21

exact match: when value DNE or when col_index_num is out of bounds
approx: lookup table is not properly constructed, or lookup_value < first value

Question 22

INDEX

purpose

Answer 22

returns the value of the cell at a particular row/column intersection

Question 23

INDEX syntax:

what does =INDEX(B2:C6, 4, 2) do?

Answer 23

=INDEX(array, row_num, column_num)
returns cell in 4th row, 2nd column

row and column numbers are relative indexes

Question 24

special cases for INDEX

in what situations can arguments be omitted?

Answer 24

1. column_num can be omitted if the values have 1 column
2. row_num can be omitted if the values have 1 row

Question 25

#REF! error for INDEX

Answer 25

if row_num or column_num is outside the range of the array

Question 26

MATCH | purpose

Answer 26

returns the **relative position** of the value within a range of cells

Question 27

# MATCH conditions for lookup_array

Answer 27

1-dimensional; if row, then excel returns column number; if column, then excel returns row number

Question 28

# MATCH conditions for match_type

Answer 28

1 = approx match in ascending order; default 0 = exact match, = FALSE -1 = approx match in descending order

Question 29

# INDEX and MATCH what does =INDEX(A2:A6, MATCH(A9, B2:B6, 0)) do?

Answer 29

finds A9's exact match within B2:B6, and then returns the corresponding value in A2:A6 | INDEX and MATCH are like decomposed VLOOKUP

Question 30

what does =INDEX(B3:D5, MATCH(A8, A3:A5, 0), MATCH(B8, B2:D2, 0)) do?

Answer 30

finds within B3:D5 the cell that matches A8's relative row position, and B8's relative column position

Question 31

# MATCH when does N/A error come up?

Answer 31

**approx:** value entered < first value on lookup_array **exact:** value DNE **both:** if lookup_array is 2-dimensional

Question 32

LEFT and RIGHT | purpose

Answer 32

LEFT: returns the **first** x number of characters in a **string** RIGHT: returns the **last** x number of characters in a **string**

Question 33

# INDEX and MATCH which of the following is impossible: - nest INDEX in MATCH - nest MATCH in MATCH - nest INDEX in INDEX

Answer 33

all of them are possible as long as they're all numerical values

Question 34

# LEFT and RIGHT what will happen if num_chars > the actual # of characters in the cell?

Answer 34

it will return the available characters

Question 35

MID | purpose

Answer 35

returns characters in a string of text starting at a specified position and based on the specified number of characters | =MID(text, start_num, num_chars)

Question 36

CONCATENATE | purpose

Answer 36

joins 2 strings into 1

Question 37

& operator | purpise

Answer 37

alternative to concatenate

Question 38

LEN | purpose

Answer 38

returns the number of characters in a text string, incl. spaces | =LEN(text)

Question 39

TRIM | purpose

Answer 39

removes blank spaces except for single spaces between words | =TRIM(text)

Question 40

SUBSTITUTE | purpose

Answer 40

substitutes an old text string for a new text string

Question 41

# SUBSTITUTE which argument of SUBSTITUTE is optional? what happens if you don't specify?

Answer 41

instance_num is optional; it specifies which instance of a text character should be replaced. if not specified, it'll substitute all instances.

Question 42

# SUBSTITUTE what happens if SUBSTITUTE cannot find old_text?

Answer 42

original text will be returned

Question 43

REPLACE | purpose

Answer 43

replaces part of a text string with a new text string, starting from a specific location and based on a specific number of characters

Question 44

# SUBSTITUTE and REPLACE case sensitivity for SUBSTITUTE and REPLACE

Answer 44

SUBSTITUTE is **case-sensitive** REPLACE is not

Question 45

# SUBSTITUTE/REPLACE difference between SUBSTITUTE and REPLACE

Answer 45

SUBSTITUTE: you know exactly what **text** you want to take out REPLACE: you don't know what text you want to take out, but you know the **location**

Question 46

FIND and SEARCH | purpose

Answer 46

looks for a substring inside a string, and returns the character number of the starting position of the substring

Question 47

# FIND and SEARCH when does #VALUE! error return?

Answer 47

if find_text is not within_text, and if start_num is < =0 or greater than the length of within_text

Question 48

# FIND and SEARCH difference between FIND and SEARCH

Answer 48

1. FIND is case-sensitive, SEARCH is not 2. SEARCH allows wildcard characters

Question 49

what are the wildcard characters?

Answer 49

? = single character * = any number of characters, including 0 ~ = escape character for ~?, ~*, ~~

Question 50

how do you find the starting position of the second occurence of a substring?

Answer 50

=SEARCH(find_text, within_text, SEARCH(find_text, within_text)+1)

Question 51

# FIND and SEARCH what does =MID(A2,FIND(",",A2),2) do?

Answer 51

finds the comma within the text in A2 and returns the 2 consecutive characters