Microsoft Office Tutorials and References

In Depth Information

**APPENDIX 1 - ALPHABETICAL FUNCTION REFERENCE**

INDEX(reference,row_num,column_num,area_num) [Category: Lookup &

Reference]

Returns a reference to a speciﬁ ed cell or cells within reference. See MATCH for

an example. Also see page 2.

INDIRECT(ref_text,a1) [Category: Lookup & Reference]

Returns the reference speciﬁ ed by a text string. References are immediately

evaluated to display their contents. Use INDIRECT when you want to change the

reference to a cell within a formula without changing the formula itself. Guru Tip:

Guru Asad Ali calls this his favorite function. There are many uses for INDIRECT.

It can be used to point to another worksheet, but fails when you need to point to

another workbook. Some examples: =INDIRECT("A2") will always point to A2.

See pages 5, 20, 27, 30-38, and 130 for more examples.

INFO(type_text) [Category: Information]

Returns information about the current operating environment. Guru Tip: British

Guru Bryony Stewart-Seume uses =INFO("directory") to put the current folder

in a cell. She also uses it in combination with INDIRECT and CONCATENATE

to build complicated lookups and references to other documents.

INT(number) [Category: Math]

Rounds a number down to the nearest integer. Guru Tip: Often used with RAND

to generate random integers: =INT(RAND()*100)+1 will generate a random

integer between 1 and 100. Also, note that INT might not act as you would

expect for negative numbers. The INT(-3.1) is -4. See TRUNC.

INTERCEPT(known_y’s,known_x’s) [Category: Statistical]]

Calculates the point at which a line will intersect the y-axis by using existing x-

values and y-values. The intercept point is based on a best-ﬁ t regression line

plotted through the known x-values and known y-values. Use the intercept when

you want to determine the value of the dependent variable when the independent

variable is 0 (zero). For example, you can use the INTERCEPT function to

predict a metal’s electrical resistance at 0°C when your data points were taken

at room temperature and higher. Guru Tip: See image under FORECAST. Use

with SLOPE to describe the linear regression line. The intercept value is also

the second value returned from the LINEST function.

INTRATE(settlement,maturity,investment,redemption,basis) [Category:

Financial]*

Returns the interest rate for a fully invested security.