Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

comm205 > excel > Flashcards

excel Flashcards

1
Q

spreadsheet program

A

computer program for organization, analysis, and storage of data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

name box

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

4 types of data in excel

+ alignment

A

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!

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

formulas

what do they contain?

A

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

combine data, functions, arithmetic ops, or logical comparisons

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

function

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

syntax

A

specifies arguments and the correct punctuation when using a function

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

IF

purpose

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

nested IF

purpose

A

uses a combination of IFs for >2 possible results

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

MAX

purpose

A

returns the largest value in a set of values

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

MIN

purpose

A

returns the smallest value in a set of values

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

SUM

purpose

A

sums the values

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

AND

purpose

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

OR

purpose

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

COUNTIFS

purpose

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

quotation marks, ex. “>B3”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

SUMIFS

purpose

A

adds the values in a specified column that satisfy a condition

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

COUNTIFS/SUMIFS

exceptions to syntax for COUNTIFS and SUMIFS (3)

A
  1. cell references, ex. =COUNTIFS(A1:A5, C1) -> no “”
  2. equality -> eliminate “=”
  3. directly specifying the number, ex. COUNTIFS(A1:A5, 5) -> no “”
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

VLOOKUP

purpose

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

VLOOKUP syntax:

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

VLOOKUP

formatting the lookup table for approx matches

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

VLOOKUP

possible N/A errors?

A

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

22
Q

INDEX

purpose

A

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

23
Q

INDEX syntax:

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

A

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

row and column numbers are relative indexes

24
Q

special cases for INDEX

in what situations can arguments be omitted?

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

REF! error for INDEX

A

if row_num or column_num is outside the range of the array

26
Q

MATCH

purpose

A

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

27
Q

MATCH

conditions for lookup_array

A

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

28
Q

MATCH

conditions for match_type

A

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

29
Q

INDEX and MATCH

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

A

finds A9’s exact match within B2:B6, and then returns the corresponding value in A2:A6

INDEX and MATCH are like decomposed VLOOKUP

30
Q

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

A

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

31
Q

MATCH

when does N/A error come up?

A

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

32
Q

LEFT and RIGHT

purpose

A

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

33
Q

INDEX and MATCH

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

A

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

34
Q

LEFT and RIGHT

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

A

it will return the available characters

35
Q

MID

purpose

A

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)

36
Q

CONCATENATE

purpose

A

joins 2 strings into 1

37
Q

& operator

purpise

A

alternative to concatenate

38
Q

LEN

purpose

A

returns the number of characters in a text string, incl. spaces

=LEN(text)

39
Q

TRIM

purpose

A

removes blank spaces except for single spaces between words

=TRIM(text)

40
Q

SUBSTITUTE

purpose

A

substitutes an old text string for a new text string

41
Q

SUBSTITUTE

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

A

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

42
Q

SUBSTITUTE

what happens if SUBSTITUTE cannot find old_text?

A

original text will be returned

43
Q

REPLACE

purpose

A

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

44
Q

SUBSTITUTE and REPLACE

case sensitivity for SUBSTITUTE and REPLACE

A

SUBSTITUTE is case-sensitive
REPLACE is not

45
Q

SUBSTITUTE/REPLACE

difference between SUBSTITUTE and REPLACE

A

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

46
Q

FIND and SEARCH

purpose

A

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

47
Q

FIND and SEARCH

when does #VALUE! error return?

A

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

48
Q

FIND and SEARCH

difference between FIND and SEARCH

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

what are the wildcard characters?

A

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

50
Q

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

A

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

51
Q

FIND and SEARCH

what does =MID(A2,FIND(“,”,A2),2) do?

A

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

comm205 (2 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions