Microsoft Office Tutorials and References
In Depth Information
APPENDIX 1 - ALPHABETICAL FUNCTION REFERENCE
INDEX(reference,row_num,column_num,area_num) [Category: Lookup &
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.
Returns the interest rate for a fully invested security.