Accountant in Nigeria... Olajide And Associates - Chartered Accountants, Tax Consultants


Accountant in Nigeria... Olajide And Associates, experts in; Corporate Auditing Services, Accountancy Services, Taxation Services, Financial Services, Recruitments, Consultancy, Business Proposals etc... Contact us today and enjoy our quality services.

INVESTMENT MANAGEMENT

WORKING CAPITAL MANAGEMENT

STOCK TAKING ATTENDANT BY AUDITORS

PROGRAMING IN EXCEL

Excels functions are built-in formulae that perform specialized calculations. Their general format is:
Name (arg1, arg2,…..)

Where ‘name’ is the function name ,and ‘arg1’ arg2’ etc., are the arguments required for the evaluation of the function. Arguments must appear in a parenthesized list as shown above and their exact number depends on the function being used. However, some functions do not require arguments and used without parentheses. Examples of these are: FALSE, NA, NOW, PI,RAND, TODAY AND TRUE.

There are four types of arguments used with functions: numeric values , range values , string values and conditions, the type used being dependent on the type of function. Numeric value arguments can be entered either directly as numbers, as a cell address, a cell range name or as a formula. Range value arguments can be entered either as arrange address or a range name, while string value arguments can be entered as an actual value (a string in a double quotes), as a cell address, a cell name, or a formula. Condition arguments normally use logical operators or refer to an address containing a logical formula.

Types of Functions
There are several types of functions in excel 97, namely,financial,date and time, mathematical and trigonometric, statistical, lookup and reference, database, text, logical, and information. Each type of function requires its own number and type of arguments. These are listed on the next few pages under the various function categories. To find out in detail how these functions can be used, click the Edit Formula button to display:
Click the down arrow to the left of the formula bar reveals the most recently used functions. Selecting the last item on the list will display the paste function dialogue box which lists all the available function dialogue box which lists all the available functions. Next, choose a function from the displayed list, and activate the assistant from within this dialogue box. The characteristic yellow banner of the assistant appears, as shown below, on which you can select to get ‘Help with this feature’.
The Assistant then invokes the excel 97 Help system which displays a whole page of information on each selected function, with examples on how to use it. In what follows, we list all the functions and what they return in a concise form for ease of selection, and so that you can see at a glance what is available.

Financial Functions:
Financial functions evaluate loans, annuities, depreciation and cash flows over a period of time, using numeric arguments. where an optional parameter[TP] is given the function will calculate for either an ordinarily annuity or an annuity due, depending on the value you specified for type TP. Percentages should be entered either as a decimal (for example,0.155) or with a percent sign (for example,15.5%). The various functions and what they return are as follows:

Function                                                                              Return
DB(Ct, Sg,Lf,Pd)                                                                The depreciation allowance of an asset with an                initial value of Ct, life Lf, a final salvage value Sg for a specified period Pd, using the declining balance method.

DDB(Ct,Sg,Pd)                                                                    The double-declining depreciation allowance of an asset, with original cost Ct,predicted salvage value Sg, life Lf, and period Pd.

FV(Rt,Tm,Pt)                                                                       The future value of a series of equal payments, each of equal amount Pt, earning a periodic interest rate Rt, over a number of payment periods in term Tm.

IPMT(Rt,Pr,Tm,Pv)                                                            The interest payment for a given period Pr(which must be between 1 and Tm) of a total term Tm of a loan with present value Pv at a constant interest rate Rt.

IRR(Rg,Gs)                                                                         The internal rate of return of range Rg of cash flows, based on the appropriate percentage guess Gs.

MIRR(Rg,Fr,Rr) The modified internal rate of return for a series of cash flows in a range Fr, paid on money used in cash flows and Rr received on reinvested cash flows.

NPER(Rt,Pt,Pv,Fv) The number of periods required for a series of equal payments Pt, with a present value Pv, to accumulate a future-value Fv, at a periodic interest rate Rt.

NPV(Rt,Rg) The net present value of the series of future cash flows in range Rg, discounted at a periodic interest rate Rt.

PMT(Rt,Tm,Pv,Fv) The payment on a loan with present value Pv, at interest rate Rt,for Tm number of payments and future value Fv.

PPMT(Rt,Pr,Tm,Pv,Fv) The principal portion of the periodic payment on a loan of present value Pv, at interest rate Rt, for payment periods Pr(the number of payment periods in an annuity ),leading to a future value Fv.

PV(Rt,Tm,Pt) The present value of a series of payments, each of amount Pt, discounted at a periodic interest rate Rt,over a number of payment periods in term Tm.

RATE(Tm,Pt,Pv,Fv) The periodic interest rate necessary for a present value Pv to grow to a future value Fv, over the number of compounding periods in term Tm at Pt payments per periods.

SLN(Ct,Sg,Lf) The straight line depreciation of an asset of cost Ct for one period, given its predicted salvage value Sg, and life Lf.

SYD(Ct,Sg,Lf,Pd) The sum-of-years’ digits depreciation of an asset of cost Ct,given its predicted salvage value Sg, life Lf, and period Pd.

VDB(Ct,Sg,Lf,S,E,d,s) The depreciation of an asset of cost Ct, salvage value Sg, life Lf, over a period from start S to end E. depreciation-factor d and switch s, are optional. If s is 1 it returns declining balance depreciation for life, else straight-line used after E.

Date and Time Functions:
These generate and use serial numbers with dates having integer serial numbers between 1 and 65380 to represent dates between 1 January, 1990 and 31 December 2078, and time having decimal serial numbers starting with 0.000 at midnight and ending with 0.99999 next midnight. The various functions are:

Function Returns
DATE(Yr,Mh,Dy) The date number of argument Yr,Mh,Dy.

DATEVALUE(Ts) The number of days from 1 January 1900 of date string Ts.

DAY360(Sn,En) The number of days between Sn and En, based on a year of 12 months, each of 30 days.

HOUR(Tn) The hour number (0-23)of time number Tn.

MINUTE(Tn) The minute number (0-59) of time number Tn.

MONTH(Dn) The month number (1-12) of date number Dn.

NOW() The serial number for the current date and time.

SECOND(Tn) The second number(0-59) of time number Tn.

TIME(Hr,Ms,Ss) The time number of argument Hr,Ms,Ss.

TIMEVALUE(Ts) The time number of string Ts.

TODAY() The current date number.

WEEKDAY(Dn) The day of the week from date number Dn in integer form; 0 (Monday) through 6 (Sunday).

YEAR(Dn) Returns the year number (0-199) of date number Dn.

Mathematical and Trigonometric Functions:
These functions evaluate a result using numeric arguments. The various functions and what they return are as follows.

Functions Returns
ABS(X) The absolute value X

ACOS(X) The angle in radians, whose cosine is X (arc cos of X)

ASCOH(N) The arc (inverse) hyperbolic cosine of number N.

ASIN(X) The angle in radians, whose sine is X (arc sin of X ).

ASIN(N) The arc (inverse) hyperbolic sine of number N.

ATAN(X) The angle in radians, between ∏/2 and - ∏/2 whose tangent is X (arc tan of X -2 quadrant).

ATAN2( X,Y) The angle in radians, between ∏ and -∏, whose tangent is Y/X (arc tan of Y/X -4 quadrant).

ATANH(N) The arc (inverse ) hyperbolic tangent of number N.

CEILING(N,Sig) The rounded value of N to nearest integer or nearest multiple of significance Sig.

COMBIN(N.Obj) The number of combinations N for a given number of objects Obj.

COS(X) The cosine of X (X in radians)

COSH(X) The hyperbolic cosine of X..

DEGREES (X) The value in degrees of X radians.

EVEN(X) The rounded value of X away from 0 to the nearest even integer.

EXP(X) The value of a e raised to the power of X.

FACT(X) The factorial of X.
FLOOR(N,Sig) A number N rounded down towards zero by nearest multiple of significance Sig.

INT(X) The integer part of X.

LN(X) The natural log (base e) of X.

LOG(X,N) The log of X to a specified base N.

LOG10(X) The log(base 10) of X.

MDETERM(Ar) The matrix determination of an array.

MINVERSE(Ar) The matrix inverse of an array.

MMULT(Ar1,Ar2) The matrix product of two arrays

MOD(X,Y) The remainder of X/Y.

ODD(X) The rounded value of X away from 0 to the nearest odd integer.

PI() The value of ∏ (3.1415926).

POWER(X,N) The value of X raised to the power of N.

PRODUCT(Ls) The result of multiplying the values in lists Ls.

RADIANS(X) The value in radians of X degrees.

RAND() A random number between 0 and 1.

ROMAN(N,Fm) The roman format Fm (as text) of number N.

ROUND(X,N) The value of X rounded to N places.

ROUNDDOWN(X,N) The rounded value of X down to the nearest multiple of the power of 10 specified by N.

ROUNDUP(X,N) The rounded value of X up to the nearest multiple power of 10 specified by N.

SIGN(X) The value of 1 if X is a positive, 0 if X is 0, and -1 if X is negative.

SIN(X) The sine of angle X (X in rads).

SINH(X) The hyperbolic sine of angle X (X in rads).
SQRT(X) The square root of X.

SUBTOTAL(Ls) The subtotal in a list Ls or a database.

SUM(Rg) The sum of values in range Rg.

SUMIF(Rg,Cr) The sum in range Rg that meet a given criteria.

SUMPRODUCT(Ar1,Ar2) The sum of the products of array components.

SUMSQ(N1,N2) The sum of the squares of the arguments.

SUMX2MY2(Ar1,Ar2) The sum of the difference of squares of corresponding values in two arrays.

SUMX2PY2(Ar1,Ar2) The sum of the sum of squares of corresponding values in two arrays.

SUMXMY2(Ar1,Ar2) The sum of squares of differences of corresponding values in two arrays.

TAN(X) The tangent of angle X(X in rads)

TANH(X) The hyperbolic tangent of angle X (X in rads).

TRUNC(X,N) The truncated value of X to N decimal places.

Statistical Functions:
Statistical functions evaluate list of values using numeric arguments or cell ranges. The various functions and what they return are as follows:

Function Return
AVEDEV(Ls) The average of the absolute deviations of values in lists Ls.

AVERAGE(Rg) The average of values in range Rg.

AVERAGE(Rg) The average (arithmetic mean) of values in range Rg, including logical values and text –evaluating text and FALSE as 0,and TRUE as 1.

BETADIST(X,AL,Bt,A,B) The cumulative beta probability density function.

BETAINV(Pb,Al,Bt,A,B) The inverse of the cumulative beta probability function.

BINOMDIST(Sc,Tr,Pb,Tb) The cumulative distribution function if Tp is TRUE, else the probability mass function, with Tr independent trails and Sc successes in trails and Pr probability of success per trial.

CHIDIST(X,Fr) The chi-square distribution, evaluated at X and Fr degrees of freedom for the sample .

CHINV(X,Fr) The inverse of the one-tailed probability of the chi-squared distribution.

CHITEST(Rg1,Rg2) The chi-square test for independence on the data in range Rg1, or a chi-square test for goodness of fit on the data in ranges Rg and Rg2.

CONFIDENCE(Al,Sd,Sz) The confidence interval for a population mean.

CORREL(Rg1,Rg2) The correlation coefficient of values in ranges Rg1 and Rg2.

COUNT(Ls) The number of values in a list.

COUNTA(Rg) The number of non-blank values in a range Rg.

COUNTBLANK(Rg) The number of blank cells within a range Rg.

COUNTIF(Rg,Cr) The number of non-blank cells within a range Rg.

COVAR(Rg1,Rg2) The sample covariance of the values in ranges Rg1 and Rg2.

CRITBINOM(Tr,Pb,Al) The largestest integer for which the cumulative binomial distribution is less than or equal to Al, with Tr Bernoulli trials and a probability of success for a single Bernoulli trial Pb.

DEVSQ(Ls) The sum of squared deviations of the values in lists Ls, from their mean.

EXPONDIST(X,Lm,Ds) The exponential distribution.

FDIST(X,Fr1,Fr2) The F-distribution at value X with Fr1 and Fr2 degrees of freedom for the first and second samples.

FINV(Pb,Fr1,Fr2) The inverse of the F probability distribution.

FISHER(X) The fisher transformation.

FISHERINV(Y) The inverse of fisher transformation.

FORECAST(X,Yo,Xo) The value along a linear trend.

FREQUENCY(Rg,Bin) The frequency distribution as a vertical array bin.

FTEST(Rg1.Rg2) The associated probability of an F-test on data in ranges Rg1 and Rg2. used to determine if two samples have different variances.

GAMMADIST(X,Al,Bt,Cm) The gamma distribution.

GAMMAINV(Pb,Al,Bt) The inverse of the gamma cumulative distribution.

GAMMALN(X) The natural logarithm of the gamma function.

GEOMEAN(Ls) Returns the geometric mean of the values in list Ls.

GROWTH(Yo,Xo,Xn,Ct) The values along an exponential trend.

HARMEAN(Ls) The harmonic mean of the values in lists Ls.

HYPGEOMDIST(Ns,Ssiz,Pp,Psiz) The hypergeometric distribution probability of a given number of success Ns, given the sample size Ssiz, populations success Pp and population size Psiz.

INTERCEPT(Yo,Xo) The intercept of the linear regression line.

KURT(Rg) The kurtosis of the values in range Rg.

LARGE(Arr,K) The largest value in a data set.

LINEST(Yo,Xo,Ct,St) The parameters of a linear trend.

LOGEST(Yo,Xo,Ct,St) The parameters of an exponential trend.

LOGINV(Pb,Mn,Sd) The inverse of the lognormal distribution with parameters mean Mn and standard deviation Sd.

LOGNORMDIST(X,Mn,Sd) The cumulative lognormal distribution with parameters mean Mn and standard deviation Sd.

MAX(Rg) The maximum value in a range.

MAXA(Rg) The maximum value in a range. Does not ignore logical values or text.

MEDIAN(Ls) The median value in list Ls.

MIN(Rg) The minimum value in a range.

MODE(Ls) The most common value in a date set.

NEGBINOMDIST(Nf,Ns,Pb) The negative binomial distribution that there will be a number of failures Nf before the number of success Ns, when the constant probability of success is Pb.

NORMDIST(X,Mn,Sd) The normal cumulative distribution function for X, with a distribution mean Mn and optional standard deviation Sd.

NORMINV(Pb,Mn,Sd) The inverse of the normal cumulative distribution.

NORMSDIST(X) The standard normal cumulative distribution.

NORMSINV(Pb) The inverse of the standard normal cumulative distribution.

PEARSON(Ar1,Ar2) The Pearson product moment correlation coefficient.

PERCENTILE(Rg,K) The Kth sample percentile among the values in range Rg.

PERCENTRANK(Ar,X,Sg) The percentage rank of a value in a data set.

PERMUT(N,Nc) The number of ordered sequences (permutations) of Nc chosen objects that can be selected from a total of N objects.

POISSON(X,Mn,Cm) The Poisson distribution (depending on cumulative factor Cm)of X observed events and Mn expected number o events.

PROB(Rgx,LI,UI) The probability that values in Rgx range are within lower limit LI and upper limit UI of probability Pb.

QUARTILE(Ar,Qrt) The quartile of a data set.

RANK(It,Rg,Od) The relative size or position of a value it in a range Rg, relative to other values in the range, ranked in order Od.

RSQ(Yo,Xo) The square of the Pearson product moment correlation coefficient.

SKEW(Rg) The skewness of the values in range Rg.

SLOPE(Yo,Xo) The slope of the linear regression line.

SMALL(Ar,K) The Kth smallest value in a data set.

STANDARDIZE(X,Mn,Sd) The normalized value of X from a distribution characterized by mean Mn and standard deviation Sd.

STDEV(Rg) The population standard deviation of values in range Rg.

STDEVA(Rg) An estimate of the standard deviation based on a sample, including logical values and text.

STDEVP(Rg) The standard deviation based on the entire population.

STDEVPA(Rg) The standard deviation based on the entire population, including logical values and text.

STEYX(Yo,Xo) The standard error of the predicted Y-value for each X in the regression.

TDIST(X,Fr,Tr) The student’s t-distribution, evaluated at X and Fr degrees of freedom for the sample, with test direction Tr.

TINV(Pb,Fr) The inverse of the student’s t-distribution.

TREND(Xo,Yo,Xn,Cn) The values along a linear trend.

TRIMMEAN(Ar,Pb) The mean of the interior of a data set.

TTEST(Rg1,Rg2,TI,Tp) The probability associated with a student’s t-test.

VAR(Rg) The sample variance of values in range Rg.

VARA(Rg) An estimate of the variance based on a sample, including logical values and text.

VARP(Rg) The variance of values in range Rg based on entire population.

VARPA(Rg) The variance of values in range Rg based on entire population, including logical values and text.

WEIBULL(X,AL,Bt,Cm) The weibull distribution.

ZTEST(Arr,X,Sg) Returns the two-tailed P-value of a Z-test.

Lookup and Reference Function:
The group of function return values specified by a range reference or array reference. The various functions available and what they return are as follows:

Functions Returns
ADDRESS(Rn,Cn) The cell address specified by row Rn, and column Cn.

AREAS(R11,R12…) The number of areas in the list of references.

CHOOSE(K,Vo,...Vn) The Kth value in the list Vo,..Vn.

COLUMN(Rf) The column number of a reference.

COLUMNS(Rg) The number of columns in the range Rg.

HLOOKUP(X,Ar,Rn) The value of indicated cell by performing a horizontal array look-up by comparing the value X to each cell in the top index row in array Ar, then moves down the column in which a match is found by the specified row number Rn.

HYPERLINK(Loc,Fn) A shortcut to a document on your hard disc, network server of the internet at specified location and friendly name.

INDEX(Rg,Rn,Cn) The value of the cell in range Rg at the intersection of row-offset Rn,and column-offset Rn, and column-offset Cn.

INDIRECT(Rf) The cell reference specified in reference Rf in A1-style.

LOOKUP(Lv,Vr,Rv) The relative position of an item in an array that matches a specified value in a specified order.

MATCH(Lv,Ar,Mtc) The relative position of an array Ar that matches the specified value Mtc of a lookup value Lv.

OFFSET(Rf,Rn,C,Ht,Wh) A reference of a specified height Ht and width Wh offset from another reference Rt by a specified number of rows Rn and columns Cn.

ROW(Rf) The row number of a reference.

ROWS(Rg) The number of rows in a range.

TRANSPOSE(Ar) The transpose of an array.

VLOOKUP(X,Ar,Cn) The value of indicated cell by performing a vertical table look-up by comparing the value X to each cell in the first index column, in array Ar, then moves across the row in which a match is found by the specified column number Cn.

Database Functions:
Database functions perform calculations on a database. The database, called the input range, consists of records, which include fields and field names, like Fd below. A criterion range must be set up to select the records from the database that each function uses. The various functions and what they return are as follows:

Functions Returns
DAVERAGE(Db,Fd,Cr) The average of the values in the field Fd that meet the criteria in a database Db.

DCOUNT(Db,Fd,Cr) The number of non-blank cells in the field Fd that meet the criteria Cr in a database Db.

DCOUNTA(Db,Fd,Cr) Counts nonblank cells from a specified database and criteria.

DGET(Db,Fd,Cr) The single value in the field Fd that meet the criteria Cr in a database Db.

DMAX(Db,Fd,Cr) The maximum value in the field Fd that meet the criteria Cr in a database.

DMIN(Db,Fd,Cr) The minimum value in the field Fd that meet the criteria Cr in a database Db

DPRODUCT(Db,Fd,Cr) The result of the product of the product of the values in the field Fd that meet the criteria Cr in a database Db.

DSTDEV(Db,Fd,Cr) The standard deviation based on the values in the field Fd that meet the criteria Cr in a database Db.

DSTDEVP(Db,Fd,Cr) The standard deviation based on the entire population of the values in the field Fd that meet the criteria Cr in a database Db.

DSUM(Db,Fd,Cr) The sum of the values in the field Fd that meet the criteria Cr in a database Db.

DVAR(Db,Fd,Cr) The estimated variance based on the values in the field Fd that meet the criteria Cr in a database Db.

DVARP(Db,Fd,Cr) The variance based on the entire population of the values in the field Fd that meet the criteria Cr in a database Db.

GETPIVOTDATA(Pt,Nm) Data stored within a named pivotal table.

Text functions:
String functions operate on strings and produce numeric values dependent on the function.

Functions Returns
CHAR(X) The character that corresponds to the code number X.

CLEAN(Sg) The specified string Sg having removed all non-printable characters from it.

CODE(Sg) The code number for the first character in string Sg.

CONCATENATE(Sg1,Sg2) One string made up of several strings.

DOLLAR(N,Dm) A number in text form, using currency format.

EXACT(Sg1,Sg2) The value 1 (TRUE) if strings Sg1 and Sg2 are exactly alike, otherwise 0 (FALSE).

FIND(Ss,Sg,Sn) The position at which the first occurrence of search string Ss begins Sg, starting the search from search number Sn.
FIXED(N,Dm,Nc) A number N formatted as text with a fixed number of decimals Dm. Nc is a logical value and if TRUE prevents the inclusion of commas.

LEFT(Sg) The first (leftmost) N characters in string Sg.

LEN(Sg) The number of characters in string Sg.

LOWER(Sg) A String Sg with all the letters converted to lowercase.

MID(Sg,Sn,N) The N characters from string Sg beginning with the character at Sn.

PROPER(Sg) A string with all words in string Sg changed to first letter in uppercase and rest in lowercase.

REPLACE(O,S,N,Ns) A string with N characters removed from original string O, starting at character S and then inserts new string Ns in the vacated place.

REPT(Sg,N) A repeated string Sg, N times. Unlike the repeating character (\), the output is not limited by the column width.

RIGHTS(Sg,N) The last (rightmost) N characters in string Sg.

SEARCH(Sg1,O,S) String Sg1 in original string O, starting at character S.

SUBSTITUTE(Sg,O,Ns,N) A new string Ns substitute for old string O in a string Sg. N specifies which occurrence of the old text you want replace.

T(X) A value X converted into text.

TEXT(X,Fm) A number X formatted into text.

TRIM(Sg) A string Sg with no leading, trailing or consecutive spaces.

UPPER(Sg) All letters in string Sg converted to uppercase.

VALUE(Sg) The numeric value of string Sg.

Logical Functions:
Logical functions produce a value based on the result of a conditional statement, using numeric arguments. The various functions and what they return are as follows:
Functions Returns
AND(N1,N2,N3…) The logical value 1 (TRUE) if all arguments are TRUE.

FALSE() The logical value 0

IF(Cr,X,Y) The value X if Cr is TRUE and Y if Cr is FALSE.

NOT(N) The reverse logic of its argument N.

OR(N1,N2,…) The logical value 1 (TRUE) if any argument is TRUE.

TRUE() The logical value 1.

Information Functions:
Information functions perform a variety of advanced tasks, such as looking up values in the table, returning information about cells, ranges or the excel environment. The various functions and what they return are as follows:

Functions Returns
CELL(At, Rg) Returns the code representing the attribute At of range Rg.

ERROR.TYPE(X) The error value.

INFO (At) Returns system information based on the attribute At.

ISBLANK(X) The value 1(TRUE), if X is an empty cell.

ISERR(X) 1 (TRUE), if X is an error value except #N/A.

ISERROR(X) 1 (TRUE), if X is any error.

ISLOGICAL(X) 1 (TRUE), if X is a logical value.

ISNA(X) 1 (TRUE), if X contains #N/A.

ISNONTEXT(X) 1 (TRUR), if X is not text.

ISNUMBER(X) 1 (TRUE), if X contains a numeric value.

ISREF(X) 1 (TRUE), if X is a reference.

ISTEXT(X) 1 (TRUE), if X is a text.

N(X) A value converted to a number.

NA() The error value #N/A.

TYPE(X) A number indicating the data type value of X

Prepared By Alh. Y. O. Olajide
Managing Partner
Olajide And Associates
www.olajideassociates.com