Excel Test 2

Front 1

What does the AND function do?

Back 1

Evaluates a list of logical arguments to determine if all arguments are true. the AND function returns a value of TRUE if all arguments in the function are true.

Front 2

What does the OR function do?

Back 2

Evaluates a list of logical arguments to determine if at least one argument is TRUE. Theh OR function returns a value of false if only all of the arguments in the function are false.

Front 3

What does the NOT function do?

Back 3

Evaluates only one logical argument to determine if it is FALSE. The NOT function essentially changes the value TRUE to FALSE or the value FALSE to TRUE

Front 4

Evaluate:
=AND(False,true,true)

Back 4

False. Because the AND function tests to determine if all of the arguments are true.

Front 5

Evaluate
=OR(3>5,true)

Back 5

True. At least one of the arguments in the formula (true) is true.

Front 6

Evaluate:
=NOT(OR(false,false,false))

Back 6

True. Since the OR function tests to see if any of the arguments are true, the OR test results in a FALSE. but the NOT function switches it to TRUE.

Front 7

Evaluate
=AND(A2>6,NOT(false)), where A2=25

Back 7

True. Since the AND function tests to see if ALL of the arguments are true, 25>6, and NOT(false) = true. So they are both true.

Front 8

Describe how you would format a cell so that if the value is greater than 50 it would be automatically bolded

Back 8

1. Select a cell from the data
2. On the Home ribbon, click "conditional formatting"
3. On the drop-down menu, roll over "highlight cells rules", then click "greater than"
4. Insert value "50", then select "custom format"
5. Click "bold" and press Ok
6. Double Click "Format painter" on the home ribbon and drag it down the cells it will affect

Front 9

If trying to determine if the actual price is within budget for a number of items, what is a formula you could write?

Back 9

=actual<budget

Front 10

Write a formula to determine if all of the items are within the budget or not

Back 10

=and(A1:A5)

Front 11

Write a formula to determine if at least one item is within budget

Back 11

=OR(E3:E8)

Front 12

Write a formula to determine if the items are not within budget

Back 12

=NOT(actual<budget)

Front 13

What formula would you use if you wanted to set a conditional format of the items using the formula is method-such that the item n ame would be shaded in yellow if this item had an actual cost of at least $400?

Back 13

1. Highlight the cells in the "items" catagory
2. click "conditional formatting" on the home ribbon
3. select "highlight cells rules" then at the bottom click "more rules"
4. click "use a formula to determine which cells to format"
5. in the box, write a formula "=d3>=400 (the actual cost was greater or equal to 400)
6. click "format", then "fill" and click yellow, and click "ok"

Front 14

Write a formula to determine if none of the items are in budget

Back 14

=NOT(OR(E3:E8))
the OR function results in a TRUE, since at least one of the values are true in the selection, but the NOT makes it False.

Front 15

Are the two boolean expressions equivalent? why or why not?
=NOT(OR(E3:E8))
=AND(NOT(e3),NOT(e4),NOT(e5),NOT(e6),NOT(e7),NOT(e8))

Back 15

No. The first formula,
=NOT(OR(E3:E8))
Is first using the "or" function to see if any values in e3:e8 are true. then it uses the NOT function to give the opposite answer.
The second equation,
=AND(NOT(e3),NOT(e4),NOT(e5),NOT(e6),NOT(e7),NOT(e8))
Checks each result in column e, then switches it to the opposite (true or false) using the NOT function. Then the result of the AND function checks to see if ALL of the values are true.

Front 16

Is the following formula valid? Why or why not?
=NOT(E3:E8)

Back 16

No. the NOT function evaluates only one logical argument to determine if it is false. this formula asks it to evaluate all of those cells. This results in a #VALUE? error

Front 17

True or false: the lookup_value of a VLOOKUP function can be a contiguous cell range.

Back 17

False. It must be an exact value

Front 18

True or false: In a VLOOKUP formula with a TRUE lookup type, the first column of the lookup table referenced must be in ascending order to retrieve the correct value

Back 18

True. If you are doing a TRUE lookup type, excel searches sorted data to find the greatest value that does not exceed your criteria. a FALSE lookup does not need to be sorted because excel is looking for an exact match.

Front 19

True or false: The Result_vector of a LOOKUP function must be sorted in ascending order

Back 19

False. The lookup_vector in a LOOKUP formula is the only one that needs to be sorted in ascending order, since it is what excel is looking to find the result. The only thing a result_vector must have is the same number of cells as the lookup_vector.

Front 20

True or false: Reference and lookup functions may not contain nested functions as arguments

Back 20

False.

Front 21

True or false: The default_range type for VLOOKUP and HLOOKUP functions is false.

Back 21

False. The default_range type is TRUE

Front 22

True or False: The formula =INDEX((B2:D7,B12:d17,B22:d17,2,3,2) Returns the value in D13

Back 22

True. Since the values in the "reference" argument in the INDEX formula are nonadjacent, you would have to use the "area_num" part of the INDEX formula. This formula tells excel that in the 3 reference tables, the last value in the formula "2" tells excel to look in the second table of data, go to the 2nd column, and the 3rd row. Which results in D13

Front 23

True or False: The formula
=AVERAGE(CHOOSE(1,B12:D17,B22:D17)) averages the value 1 with the values in cells B12 to D17 and B22 to D17

Back 23

False. This formula would only average the values in B12:D17. The 1 in the formula tells excel which range (B12:D17 or B22:D17) to look for, and since B12:D17 is the first, it will choose that one, then perform the AVERAGE function on it.

Front 24

True or False: The formula
=MATCH(40,{10,40,50,90},0), Returns the value 2

Back 24

True. In the MATCH formula, the "40" is the lookup_value. Excel takes this value and finds it in the range of data within {} which is the lookup_array. Since 40 is the second value listed and the 0 at the end of the formula indicates we are looking for an exact match, excel will return a 2 for 40 being the second value.

Front 25

Explain the difference between the lookuptables on pg355

Back 25

The first table is sorted in descending order, and the second is sorted in ascending order. Since the grades start with the best "A" and decrease, we want to use the first table with the descending values. This way excel will take a score (850) and try to match it to 900 first. Since it is an approximate match and it is lower than 900, it goes to the next value, 800. Since 850>800, excel chooses the B as the grade. If we used the Ascending table, excel would automatically calculate that 850>500, and give the student an F.

Front 26

What is the function to calculate the interest percentage per period of a financial transaction?

Back 26

RATE

Front 27

What is the function to calculate the value at the beginning of a financial transaction?

Back 27

PV

Front 28

What is the function to calculate to calculate the value at the end of a financial transaction?

Back 28

FV

Front 29

What is the function to calculate the periodic payments into or out of a financial transaction?

Back 29

PMT

Front 30

What is the function to calculate the number of compunding periods in a financial transaction

Back 30

NPER

Front 31

What argument in the IPMT function uses a 1 to indicate that interest will be paid at the beginning of each compounding period?

Back 31

Type

Front 32

What type of interest is calculated based on original principal regardless of the previous interest earned?

Back 32

Simple Interest

Front 33

What type of interest is calculated based on principal and previous interest earned?

Back 33

Compund Interest

Front 34

What is the function to calculate straight line depreciation based on the initial capital investment, number of years to be depreciated and salvage value?

Back 34

SLN

Front 35

What is the function to calculate the amount of a specific periodic payment that is principal in a given period?

Back 35

PPMT

Front 36

What is the function to calculate the cumulative principal paid between two periods?

Back 36

CUMPRINC

Front 37

What is the function to determine that value of a variable set of cash flows discounted to its present value?

Back 37

NPV

Front 38

What is the function to determine the rate of return, where the net present value of the cash flows is 0?

Back 38

IRR

Front 39

What is the function to calculate the amount of a periodic payment that is interest in a given period?

Back 39

IPMT

Front 40

Assume that you are investing $6,000 in a savings plan today and will make payments of $200 per quarter. The plan pays 5% int rate yearly, compounded quarterly. How much will your savings be worth in 5 years?

Back 40

=FV(B3/4,B4*4,-B2,-B1)
Where B3/4 =Int rate/4 quarters
B4*4 = Number of years times number of quarters
-b2 = negative 200$ payments
-b1= initial investment outflow of 6000
Savings will be worth $12,204.82

Front 41

Write an excel formula to determine the yearly interest rate being charged by the bank on a $375,000, 30 year mortgage. Monthly payments of 3,000 and value at end of loan is 0. interest compunded monthly.

Back 41

=RATE(B4*B5,-B2,B1,0)*12
Where:
B4*B5=Number of years times number of periods (30*12)
-B2 = quarterly payments (3000)
Answer = 9%
B1 = Loan amount 375,000 (positive because it was received)
0 = FV
Times it by 12 because the RATE formula gives us the rate per period. We are looking for the rate per year, so we times it by the number of periods.

Front 42

$100,000 mortgage at 5% annual interest rate compounded monthly paid back over 15 years. Loan has a $5,000 balloon payment due at the end. What are the monthly payments?

Back 42

=PMT(B3/B5,B5*B4,B1,-B7)
B3/B5=int rate/number of periods
B5*B4= #of periods*number of years
B1= the PV of the loan
-B7=the Balloon pmt.
To account for a balloon payment (addtnl money owned at the end of a loan) you must specify a negative FV.
Pmts = $772.09

Front 43

How much money do you need to invest now to have $15,000 at the end of 3 years? interest pays 3% compounded quarterly, and no payments will be made

Back 43

=PV(B3/B5,B5*B4,0,B2)
Where:
B3/B5=Int rate/#pds (3%/4)
B5*B4=#pds*#years (4*3)
0=Payments made to acct
B2 = FV of money

This equals $13,713.57 to be deposited today

Front 44

Using straight line depreciation, determine the money that can be depreciated each years. Original cost = $250,000
Useful life = 10 years
Estimated Salvage Value = $10,000

Back 44

=SLN(B1,B3,B2)
Where:
B1=cost (250,000)
B3=salvage (10,000)
B2=life (10 years)

Front 45

True or False: Excel matches the lookup_value "tom" with the entry "TOM" in a lookup table.

Back 45

True. The lookup_value is defined as the data you want to look up. The value can be a number, text, a logical value, or a name or cell reference that refers to a value.

Front 46

True or False: The row and column arguments in the INDEX function can be numeric values, Boolean values, or text

Back 46

False. They need to be actual numbers of columns and rows of data for excel to look up

Front 47

What happens when excel is solving a VLOOKUP formula with a FALSE range_lookup type and does not find an exact match in the lookup table?

Back 47

If excel can not find an exact match, the text #N/A is displayed in the cell.

Front 48

What are the differences between VLOOKUP, HLOOKUP, and LOOKUP?

Back 48

VLOOKUP: Used to look up data in a vertical table. Excel starts by searching for data in the first column. When it finds the required data, it retrieves the value in a specified column that is in the same row as the data found by the lookup.

HLOOKUP: Similar to the VLOOKUP function except that it searches a horizontal lookup table. Excel looks up a value by testing for a criterion accross a row, instead of down a column.

LOOKUP: The LOOKUP function looks up the greatest value that does not exceed a specified value anywhere in a table or range. Only uses a TRUE lookup type, so the column or row containing the lookup values must be inascending order. You can only use a LOOKUP function when you want to retrieve a value that is stored to the left of a key data column in a vertical lookup talbe or above a key row in a horizontal lookup table.

Front 49

What does the OR function do?

Back 49

It evaluates a list of logical arguments to determine if at least one argument is TRUE. An OR function is FALSE only if all arguments are false.