Functions are simply complex formulas that allow you to complete calculations th
ID: 3812392 • Letter: F
Question
Functions are simply complex formulas that allow you to complete calculations that are extremely difficult to resolve with a formula. To effectively use functions, you need to understand how they work and how they are copied to other cells.
1.Choose a function to evaluate. Describe how the function is used and write out the function in its generic syntax. Define the function’s arguments.
2.What is nesting and how is it used in your function?
3.What is relative cell referencing? Describe what happens when you copy a function to another cell using relative cell referencing.
4.What is absolute cell referencing? How do assign absolute cell referencing to a cell.
5.What is a mixed cell reference? When would you use a mixed cell reference
Explanation / Answer
1. Function - As you already said functions are complex formula that help us in making calculations and make the process easy.
let's take a function Count and Sum --
Count and Sum: The most used functions in Excel are the functions that count and sum. You can count and sum based on one criteria or multiple criteria
a:Count
To count the number of cells that contain numbers, use the COUNT function.
b:Countif
To count cells based on one criteria (for example, higher than 9), use the following COUNTIF function
c:Sum
To sum a range of cells, use the SUM function.
for using function you just have to write the name of function which you wan to use and the rows in which you want to apply that function
like fx = COUNT(A1:A5)
fx = COUNTIF(A1:A5,">9")
Function's argument - Most of the functions found in Excel require some input or information in order to calculate correctly. For example, to use the AVERAGE function, you need to give it a range of numbers to average.
Any input you give to a function is called an argument.
The basic construct of a function is:
Function_Name(argument1, argument2,…)
2. Nesting - A nested function is a function which is defined within another function.The nested functions are invisible
outside its enclosing functions but can access all local objects like data,functions.
Example - void sort(int *a, int size) {
void quickSort(int first, int last) {
void swap(int p, int q) {
int tmp = a[p];
a[p] = a[q];
a[q] = tmp;
}
int partition() {
int pivot = a[first], index = first;
swap(index, last);
for (int i = first; i < last; i++) if (a[i] < pivot) swap(index++, i);
swap(index, last);
return index;
}
if (first < last) {
int pivotIndex = partition();
quickSort(first, pivotIndex - 1);
quickSort(pivotIndex + 1, last);
}
}
quickSort(0, size - 1);
}
explanation - in this the swap is nested within quicksort and quicksort is nested within sort.Sort's parameter a and size are visible in both quicksort and swap likewise quicksort's parameter first and last are visible in swap function.
3. Relative cell referencing - relative cell reference describes how far away a cell or group of cells is from some other cell in the same spreadsheet.
when we copy a formula using relative cell referencing all the cells start behaving same as that cell or we say the formula which we write in a particualr cell will help us to calculate all the rows without even writing it on the rows.
example - If cell A1 contains value 2, and cell B1 contains formula =A1+2 (referring to cell A1), then the formula B1 contains value 4. If you change the value in cell A1 to 5, then the value in cell B1 automatically changes to 7.
4. Absolute cell referencing - absolute references do not change when copied or filled. You can use an absolute reference to keep a row or column constant.
An absolute reference is designated in a formula by the addition of a dollar sign ($). It can precede the column reference, the row reference, or both.
to assign absolute cell referencing -
a: Select the cell that will contain the formula.
b: Enter the formula to calculate the desired value.
c: Press Enter on your keyboard. The formula will calculate, and the result will display in the cell.
d: Locate the fill handle of the desired cell.
e: Drag the fill handle over the cells you want to fill.
f: Release the mouse. The formula will be copied to the selected cells with an absolute reference, and the values will be calculated in each cell.
5. Mixed cell reference - A mixed reference is a reference that refer to a specific row or column. For example, $A1 or A$1.A mixed cell reference is either an absolute column and relative row or absolute row and relative column.
Use -
when users need to make a cell reference mixed. In that case, they lock either the row or the column by preceding it with the $ sign as in $A3 or B$3.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.