Accounting 218 In-Class Exam 3

Front 1

How many characters can be stored in a cell, how many will be displayed?

Back 1

32,767 and 1,024

Front 2

How many functions are in excel?

Back 2

461

Front 3

How many arguments can functions contain?

Back 3

255

Front 4

How many nested IFs can a function have?

Back 4

64

Front 5

How many characters can a formula contain?

Back 5

8,192

Front 6

How many criteria and criteria ranges can a conditional IF have?

Back 6

127

Front 7

What can a conditional IF only have one of?

Back 7

sum/average range

Front 8

F1

Back 8

help

Front 9

F2

Back 9

Edit

Front 10

F3

Back 10

Past Name/ Paste List/ Start with = and use F3 to pick name to Paste link

Front 11

F4

Back 11

Absolute $$

Front 12

F5

Back 12

Go To

Front 13

F6

Back 13

Windows Key (move between split windows)

Front 14

F7

Back 14

Spell Checker

Front 15

F8

Back 15

Move through lines of Macro code when debugging a macro; when in Visual Basic, Pressing F8 will also start the debugger

Front 16

Alt F8

Back 16

Macro menu

Front 17

F11

Back 17

Use the selected data to build a chart on chart sheet, use the default chart type (columns)

Front 18

Alt F11

Back 18

VB menu opens visual basic applications

Front 19

How do you copy and paste validation rules?

Back 19

Paste Special, Validation

Front 20

How do you copy and paste conditional format rules?

Back 20

format painter or paste special formats

Front 21

What can be filled down?

Back 21

conditional formats and data validation rules along with the data in the cell

Front 22

How do you copy data validation from one cell to another?

Back 22

Paste Special, Validation

Front 23

What can you do in the Excel Options, Formulas Tab?

Back 23

1) Turn on iterative calculations for a circular reference formula.

2) Turn off Formula AutoComplete (lists of names which can be chosen)

3) Turn off the Use table names in fromlas

Front 24

When it comes to conditional formatting where are new rules added on the list?

Back 24

at the top and therefore have higher precedence, unless moved

Front 25

How do you turn the print attribute option on or off for an object, text box, or macro button?

Back 25

right click and look at its properties under format control

Front 26

Defined Name

Back 26

a named that represents a cell, range of cells, formula, or constant value. You can create your own defined name, and Excel sometimes creates a defined name for you, such as set a print area.

Front 27

Table Name

Back 27

a name for an Excel table, which is a collection of data about a particular subject that is stored in records (rows) and fields (columns). Excel creates a table name of Table 1, Table 2 and so on, each time that you insert an Excel table, but you can change a table's name to make it more meaningful.

Front 28

For defined name and a Table name what must the first character be? and What is listed first?

Back 28

first character must be a letter or an underscore or a backslash; \ first, _ Second

Front 29

What can defined names, Table Names, and macro names not be the same as?

Back 29

a cell reference i.e. R1C1, Z$100

Front 30

What are not allowed in defined names, Table Names, and Macro Names?

Back 30

spaces

Front 31

How many characters can a defined name range, Table name, Field names have?

Back 31

255

Front 32

what letters can't you use in a defined named range, Table names, or Macro Names?

Back 32

C,c,R, or r because they are all used as a short hand for selecting a row or column for the currently selected cell when you enter them in a Name of Go To text box.

Front 33

What type of name can have spaces?

Back 33

field names

Front 34

What type of name can have any character used?

Back 34

field names

Front 35

For a Macro name, how many characters can the name contain?

Back 35

64

Front 36

F3

Back 36

Formula/Paste Name

Front 37

Ctrl + Shift+ F3

Back 37

create from selection

Front 38

Ctrl + F3

Back 38

Name Manager

Front 39

What are the 4 ways to past a name in a formula or function?

Back 39

Start with =

1) paste F3 to pick a name

2) Select a cell with a name and the name will be pasted in the formula

3) use the Ribbon, Formulas, Use in Formula and chose Paste Names and pick a name

4) Type the first letter of the name and choose the name from the list (AutoComplete)

Front 40

Where are table names listed?

Back 40

name box at the bottom, no in a Table of Names put on a Worksheet

Front 41

What is the Name Manager for?

Back 41

edit or delete an existing name

Front 42

What can you use the filter button, to look at names "scoped" to the workbook?

Back 42

defined names that are global, available to use in a formula on any sheet in the workbook

Front 43

Scoped to a specific sheet

Back 43

print area, print title, extract and criteria (set by Excel during an advanced filter)

Front 44

What can you use the filter in Name Manager for?

Back 44

to show just the table names in the file or the defined names

Front 45

What will all illegal characters be replaced with for naming?

Back 45

an underscore

Front 46

What are the advantages of using names over cell references?

Back 46

save time, better understand the formula, and make formula more accurate

Front 47

How do you change Macro security settings?

Back 47

Excel Options, Trust Center, Tust Center Settings button

Front 48

What does a relative line of code look like in a Macro?

Back 48

1) ActiveCell.Range("A1:D1").Select

2) ActiveCell.Offset(3,0).Range("A1").Select

3) ActiveCell.Offset(0,-1).Columns("A:B").EntireColumn.Select

Front 49

What does an absolute line of code in a Macro look like?

Back 49

1)Range("A1"). Select

2)Columns("A:B").Select

Front 50

How do you comment out a line of code in a Macro?

Back 50

add an apostrophe in front of the line

Front 51

How do you save a macro file?

Back 51

save as a Excel Macro Enabled Workbook (XLSM extension)

Front 52

What are the 3 ways to store a macro?

Back 52

1) save with the workbook they are built in

2) Saved to a new workbook, save all you macros in it and carry that file with you to load when you want to use your macros

3) Store them in a personal workbook

Front 53

What is the advantage of storing macros in the Personal workbook?

Back 53

1) it loads them automatically when excel is loaded

2) they are available to be used in any open excel workbook

Front 54

What are the four different ways macros can be run?

Back 54

1) Alt F8 and the run button

2) Keyboard shortcut

3) macro button

4) use a button on the quick access tool bar

Front 55

What is an advantage of using a macro button on the quick access toolbar over using a macro button on a sheet?

Back 55

1) don't have to look for it on a sheet, won't get resized, deleted, or moved

2) run your macro on any sheet in any open workbook as long as the Excel file with the macro is open

Front 56

DATEDIF()

Back 56

(older date, newer date, "D""M""Y")

Front 57

WEEKDY()

Back 57

(A cell containing a date number)

Front 58

DATE()

Back 58

(year, month, day)

Front 59

How many argument can AND() have?

Back 59

255 logical tests

Front 60

How many arguments can OR() have?

Back 60

255 logical tests

Front 61

VLOOKUO() and HLOOKUP()

Back 61

(cell to look up, table, column/row, exact match or not)

Front 62

When building a table for VLOOKUP and HLOOKUP, what order must the values in the left column be?

Back 62

in ascending order

Front 63

How many criteria can SUMIF, COUNTIF, and AVERAGEIF have?

Back 63

only one

Front 64

How many criteria can SUMIFS, COUNTIFS, and AVERAGEIFS have?

Back 64

127

Front 65

What is the difference between conditional IF and conditional IFS?

Back 65

in conditional IF it looks in one column for criteria, whereas conditional IFS look in multiple columns for criteria

Front 66

For all the columns in a conditional IFS what must be the same?

Back 66

the length of the columns

Front 67

For all the database statistical syntax, what are teh arguments?

Back 67

(Database range, field to use, criteria range)

Front 68

What are the 3 ways a field name can be indicated in the Database statistical functions?

Back 68

1) number of the column containing the data the statistical function is to look at

2) cell address in the database containing the field name

3) field name entered as a string value

Front 69

What are ranges in database statistical functions?

Back 69

database and criteria

Front 70

Why should you use DCOUNTA over DCOUNT?

Back 70

because it can count labels and values when counting records that match the criteria and can be used on any field(column) in the tbale to count matching records

Front 71

What must be true for DGET to work?

Back 71

it will only find information about one thing, therefore the criteria has to be unique in the field

Front 72

Why is using the cell address containing the field name the best way for database statistical functions?

Back 72

becasue the formula won't need to be modified if the field is moves, or the field name is modified

Front 73

Why are dstatistical functions more powerful than conditional IFS?

Back 73

they can do multiple tests within multiple fields

Front 74

What is the difference between AutoFilter and Advanced Filter?

Back 74

the advanced filter will allow you to work with multiple tests in multiple fields

Front 75

What do IFs test?

Back 75

one cell at a time

Front 76

What does a conditional IFS work with?

Back 76

columns of data

Front 77

What does Database statistical function work with?

Back 77

Database, fields, records, and field names

Front 78

For a database statistical function, what is the criteria argument the same as?

Back 78

the range it would be for an advanced filter

Front 79

Why do you use a value error message instead of a string value?

Back 79

so other formulas depend on these formulas will show an error message instead of a math answer

Front 80

What is the difference between Precedent cells and dependent cells?

Back 80

Precendent cells are cells with a formula and the results of that formula is causing an error in another cell. Dependent cells are cells with formulas and a cell with an error that is causing an error in the dependent cell

Front 81

When it comes to circular referencing, what is direct reference?

Back 81

uses the cell address the formula is stored in, in the formula

Front 82

When it comes to circular referencing, what is indirect reference?

Back 82

uses the cell address the formula is stored in but indirectly, through referring to another cell containing a formula that uses the cell address

Front 83

DB()

Back 83

allows you to compute the double declining depreciation amount when an asset is being depreciated for less than a year

Front 84

DDB()

Back 84

when the asset is being depreciated for an entire year

Front 85

What does PMT stand for?

Back 85

an annuity

Front 86

For NPER and RATE what must the payment be?

Back 86

negative

Front 87

0 in the PMT means?

Back 87

ordinary annuity

Front 88

1 in the PMT means?

Back 88

annuity due

Front 89

What does the PV function do?

Back 89

pull the interest out of future payments of the same amount (an annuity) so that those payments are discounted to their present value

Front 90

What does the NPV function do?

Back 90

pull the interest out of future payments varying amounts (not an annuiyt) so that those payments are discounted to their present value

Front 91

What are NPV and IRR only used for?

Back 91

capital budgeting problems