FUNCTIONS For Functions, describe what the function does, give its syntax, descr
ID: 3710514 • Letter: F
Question
FUNCTIONS For Functions, describe what the function does, give its syntax, describe its inputs, and whether the inputs are required or optional. Example: PMT Function The PMT Function is a financial function that calculates the payment for a loan based on constant payments and a constant interest rate. The formula syntax is PMT(rate, nper, pv, [fv], [type]). Rate is the interest rate for the loan. Required. Nper is the total number of payments for the loan. Required. Pv is the present value. Required. [Fv] is the future value. Optional. [Type] indicates when the payments are due. Optional. DEFINITIONS For Definitions, describe what happens when the cell is copied and filled to other cells. Give an example. Example: Relative cell reference. Relative cell references change when a formula is copied or filled to another cell. An example of a relative cell reference is A1. Functions (2 points each) 1. VLOOKUP 2. SUMIF Definitions (1 point each) 3. Absolute Cell References 4. Mixed Cell References
Explanation / Answer
1. VLOOPUP()- It is a function to loopup and retrieve data from a specific column of a table.
Syntax: =VLOOKUP(value, table, column_index, [range_lookup]
value- The value to look for in the first column of the table.
table- Table from where the value is to be retrive.
column_index - The column number of the table from which the value is ti be retrive.
range_lookup- [Optional] TRUE- Approximate match (default). FALSE = Exact match
2) SUMIF(): This function adds all numbers in a range based on the given criteria.
Syntax: =SUMIF( range, criteria, [sum_range] )
where,
range: Range of cells where criteria is to be applied for sum..
criteria: Criteria to use which cells for addition
sum_range: [OPTIONAL] It is the range of the cells to sum together, If omitted range will be used for sum.
3)
Absolute reference: When the cells are copied or autofilled, there is a requirement not to change the cell reference. This is achieved by absolute reference. Dollar ($) sign is used to hold the cell, column or row value constant.
Example- $A$3 - this will keep the row and column both constant
$A3- This will keep the column constant
A$3- This will keep the row constant.
4)
Mixed cell reference: In mixed cell reference both the absolute and relative references are used in a single formula.
Example: Finding the percentage of contribution- =A3/$A$10. Here A3 contains the individual value, if the percentage is reuired, it is to be divided with the total value of cell A10.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.