In this article we will discuss about the five main functions of spreadsheet. The functions are: 1. Mathematical Functions 2. Financial Functions 3. Logical Functions 4. Special Functions 5. Statistical Functions
1. Mathematical Functions:
A number of mathematical functions are available which uses numeric values as arguments. To use them, move the cell pointer to the required cell and type the function with appropriate arguments — you can provide cell address, an arithmetic expression, or a value as the arguments as required.
The functions are:
@ABS(x): returns the absolute value of x by stripping its sign
ADVERTISEMENTS:
@EXP(x): returns exponential value, e raised to the power of x
@INT(x): returns integer portion, truncating the decimal part
@LN(x): logarithm of x with base e [natural logarithm]
@LOG(x): logarithm of x with base 10
ADVERTISEMENTS:
@MOD(x,y): modular operation. Returns the remainder obtained by dividing x with y
@PI: returns the value of 3.14159
@RAND: returns a random number with values between 0 and 1
@ROUND(x,y): x rounded to y decimal places
ADVERTISEMENTS:
@SQRT(x): returns square root of x
2. Financial Functions:
Some of the financial functions available in spreadsheet are:
1. @FV (payment, interest, term):
It gives the future value of “payment” of a fixed amount for certain periods called “term”, earning an “interest” rate. In short, if you pay Rs. 5,000/ per year for 5 years, earning an interest of 12% per annum, the money you will get at the end will be @FV(5000,0.12,5) = Rs 31,764.
ADVERTISEMENTS:
2. @IRR (guess, range):
It returns the rate of discount which will balance the cash inflows and outflows over a period (range). It is called Internal Rate of Return. Used in investment decisions.
3. @NPV (interest, range):
It is just opposite of IRR. It gives the Net Present Value of cash inflows and outflows discounted with the rate of “interest”. Also used in project appraisal [investment decisions].
ADVERTISEMENTS:
4. @PMT (principal, interest, term):
It tells you the amount to be paid in each period for the “principal” borrowed, at the “interest” rate applicable and taking into account the “term” to pay it. For example, if you borrow Rs. 50,000 repayable in 5 years and the interest charged is 12%, then @PMT(50000,0.12,5) = Rs. 13,870, which you will have to pay per year.
5. @PV (payment, interest, term):
It would tell you what will be the present value of a sum, for certain “payment” made for “term” at “interest” rate. For example, if you decide to pay Rs. 5,000 per year for 5 years, which earns an interest of 12%, its present value is @PV(5000,0.12,5) = Rs. 18,024. The future value would be Rs. 31,764 as calculated earlier.
3. Logical Functions:
The three logical functions which considered in spreadsheet are:
@IF(condition, x, y): the function tests the “condition” and returns “x” if the condition is true, else returns “y”. For example, if you write @IF(Total > 100, Total-50, Total + 50), then if Total = 90, the condition evaluates to false, and (90+50) i.e. 140 will be displayed.
©TRUE: it will return 1 if true
©FALSE: will return 0 if false
We can combine these as @IF(condition, @TRUE, ©FALSE) and it will display 1 or 0 depending on whether the “condition” is true or false.
4. Special Functions:
@CHOOSE(x, v0, v1, ..,vN):
It would return the value from the list v0 to vN depending on the value of x, which is used as an offset. If x is 3, then offset is 3 and the third item from the list will be chosen. For example, ©CHOOSE(2,3,6,9,12) will display 9. It is useful for selecting a specific rate from a number of rates under a specific condition.
@ERR:
It displays ERR when an error condition develops in a cell — it is used for error trapping. For example, @IF(A2 = 0, ©ERR, 10/A2) will display 5 if the cell A2 contains 2 and ERR if A2 is 0.
@NA:
It displays NA in a cell where the data is not available. Instead of keeping a cell blank, when the data is not available, use @NA to display NA, so the system will know it and will display NA in all cells which will reference the cell with @NA.
5. Statistical Functions:
@AVG(list): it averages the value given in a list, which can be a range.
@COUNT(list): it counts the number of items in the list.
@MAX(list): returns the maximum value from the list.
@MlN(list): returns the minimum value from the list.
@STD(list): computes the standard deviation from the values of the list.
@SUM(list): sums the values of the list.
@VAR(list): computes the variance from the list of values.
While specifying the list, you can use the addresses of the first cell and the last ceil, separated by a period.