5. Spreadsheet Formulas And Functions

Question 0

What are some common error codes encountered with formulas?

Answer 0

1. # DIV/0!
2. # NAME!
3. # NULL!
4. # NUM!
5. # REF!
6. # VALUE!
7. # NA!

Question 1

What does a formula start with?

Answer 1

An “=” sign

Question 2

What does the error code #DIV/0 mean?

Answer 2

Trying to divide a cell by zero or the cell is empty

Question 3

What does #NAME! Mean?

Answer 3

Does not recognize text in formula

Question 4

What does #NULL! Mean?

Answer 4

When a space is used instead of a comma to separate arguments

Question 5

What does #NUM! Mean?

Answer 5

When number result is too large or too small

Question 6

What is #REF!?

Answer 6

Invalid cell reference

Question 7

What’s #VALUE!?

Answer 7

When the wrong argument or operator is used

Question 8

How is a value added from another sheet?

Answer 8

A1+Sheet1!A1

Question 9

How are values and text combined?

Answer 9

By using “&”

Question 10

What is a relative reference?

Answer 10

When a formula is copied across a range, the row and column reference will be adaptable

Question 11

What is an absolute reference?

Answer 11

Where copying a formula it won’t change

Question 12

How is an absolute revenue made?

Answer 12

$ before row or column

Question 13

What is an argument?

Answer 13

The information needed by function

Question 14

What are the reference operators for functions?

Answer 14

1. Colon: used to select cells in between

2. Comma: used to select specified cells

Question 15

Round function?

Answer 15

=ROUND(cell,number of digits)

Used to round of a number to certain number of digits

Question 16

What is the financial function?

Answer 16

=

1. Rate
2. Nper
3. Pmt
4. PV
5. FV
6. Type 1 if payment at begin, type 2 if payment at end

Question 17

What is the logical function?

Answer 17

=IF(test,if true,if false)

Question 18

Function to return certain characters?

Answer 18

Left right and mid

Question 19

What is the formula used in order to return specific characters?

Answer 19

The 
1. LEFT
2. MID
3. RIGHT
Formulas

Question 20

How does the LEFT formula work?

Answer 20

=LEFT(cell,number of characters)

Will return the characters specified

Question 21

How does the MID formula work?

Answer 21

=MID(cell,cell start,number of characters)

Question 22

How does the right formula work?

Answer 22

=RIGHT(cell,number of characters)

Will start from the right side of sheet and return characters

Question 23

What are the look up functions for?

Answer 23

To find certain values within a specified range

Question 24

What is the formula for vertical lookup?

Answer 24

=VLOOKUP(lookup value,table array,column index number,range lookup)

Question 25

What are the different variables of the vertical lookup function?

Answer 25

1. Look up value: the value needed to find
2. Table array: the specified range to look in
3. Column dances number: the column that the value must be returned to
4. Range lookup: this is a choice wether the value looking for must be exact or approximate

Question 26

What are the different options for “range lookup” for the vertical lookup function?

Answer 26

TRUE: approximate or exact
FALSE: only exact

Question 27

What is the order the financial function must be input?

Answer 27

1. RATE: interest rate
2. NPER:
3. PMT
4. PV
5. FV
6. type: either 1 or 2

Question 28

If you want to make an absolute reference for the column, where would you out the symbol?

Answer 28

The “$” symbol would be put before the column

E.g. $B6