25 terms

# Working with Formulas and Functions

Ex 3
###### PLAY
syntax
which specifies how the function should be written.
nested
functions can also be placed inside another function.
median
middle
mode
values that may be repeated several times.
You need to reference cell Q57 in a formula. What is the absolute cell reference? What are the two mixed references?
Absolute: =\$Q\$57
Mixed: =\$Q57 or Q\$57
Assume that cell R10 contains the formula =R1+R2. What formula is entered if this formula is copied and pasted into cell S20?
=S11+S12
Assume that cell T10 contains the formula =\$T1+T\$2. what formula is entered if this formula is copied and pasted into cello U20?
=U11+U2
Assume that cell V10 contains the formula =AVERAGE(\$U1:\$U5). What formula is entered if this formula is copied and pasted into cell W20?
=AVERAGE(V11,\$U15)
What are optional arguments? What happens if you do not include an optional argument in a function?
They are not required for the function to return a value but can be included to provide more control over how Excel calculates the returned value. If an optional argument is not included, Excel assumes a default value for it. [arg1,arg2...]
What function formula can you use to add the numbers in the range X1:X10?
=SUM(X1:X10)
The range of a set of values is defined as the maximum value minus the minimum value. What formula with functions can you enter to calculate the range of the values in Y1:Y10?
=MAX(Y1:Y10)
=MIN(Y1:Y10)
What formula with functions can you enter to calculate the ratio of the maximum value in the range Z1:Z10 to minimum value?
=MAX(Z1:Z10)/MIN(Z1:Z10)
comparison operator
is a symbol that indicates the relationship between two values.
principle
is the amount of money being loaned.
interest
is the amount of money added to the principle by the lender.
simple interest
the interest is equal to a percentage of principle for each period that the money has been lent.
compound interest
the interest is applied not only to the principle but also to any accrued interest.
How do you use AutoFill to copy a set of cell values, but not the formatting?
Drag the box to where you want to stop, then by the options bar expand it and click fill without formatting.
Is the first three selected values in a series are 3,6, and 9, what are the next three values that will be inserted using AutoFill?
12,15,18
If cell P5 contains the text "Mon," and then you select the cell and drag the fill handle over the range P6:P8, what text will be entered into those cells?
Tues. , Wed. , Thurs.
If cell Q3 is greater than cell Q4, you want to display "OK"; otherwise, you want to display "RETRY". Write the formula that accomplishes this.
=IF(Q3>Q4,"OK","RETRY")
Write the formula to display the current date.
=TODAY()
Write the formula to display the current date and time.
=NOW()
You want to take out a loan for \$130,000. The interest on the loan is 5% compounded monthly. You intend to pay back the loan in 20 years. Write the formula to calculate the monthly payment required to pay off the loan under those conditions.
=PMT(0.05/12, 20*12, 130000)
What financial function do you use to determine how many payments periods are required to pay off a loan?
NPER