Copyright: Copyright


Help Contents
Contents

PolyMap

PolyMap Overview

Here's how PolyMap® works

Working with Maps and Data.

Print out your work

PolyMap DataBase Edition

PolyMap Toolbars

Special commands only on the toolbars

File Menu

Edit Menu

Format Menu

View Menu

Map Menu

Option Menu

Window Menu

Help Menu

Functions

Worksheet Functions


PolyMap Overview

PolyMap® is an easy-to-use desktop mapping and geographic presentation software package for Windows 95 and Windows NT 4.0 to create thematic and informative maps for presentations.

Desktop mapping is a category of software products that allow people to create geographically accurate maps without having to master a complex, high-end GIS (Geographic Information System). However, the idea of "desktop mapping" really stems from the larger category of GIS. You can say, desktop mapping is a technology that can transform the way you do business. It lets you see business information in a new way and discover new trends and patterns in your business information.

PolyMap® makes it easy to integrate data from all over your organization and work with data geographically. PolyMap® gives you the tools to graphically map information for your business. Because it works together with Windows Office Program's, PolyMap® lets you easily incorporate maps into reports, presentations and spreadsheets.

Mapping technology was originally developed for use in large organizations, such as environmental agencies, emergency services and utilities providers. More recently, however, mapping technology has entered the business arena.


Here's how PolyMap® works

PolyMap® makes the process of composing maps easier by giving you a flexible and simple work environment. Use the build in spreadsheet to enter data or paste them from other Windows applications.

PolyMap® provides a rich set of features for displaying and printing maps, and in particular for making the appearance of the map dynamically dependent on data. Use the Map Presentation Wizard for a step-by-step process to customize your map and the map's legend. An easy-to-use labelling feature included in PolyMap® offers you to use any spreadsheet column data for labelling.

PolyMap® includes tip of the days on startup to give you handy hints on using the program more effectively. These can be turned off in the properties dialog box.


Working with Maps and Data.

Use a set of geographic layers in different formats – state and county boundaries, five-digit ZIP codes, US and state highways, cities, rivers, lakes – to add details and to create your new thematic map. State and county boundaries, for instance, can be 'extruded' to 3D prisms, shaded in color, 'sprayed' with a dot-density distribution, embedded with individual pie and bar charts, or placed into a matrix for portfolio analysis.


Print out your work

PolyMap® prints the maps and spreadsheets on any Windows-compatible printer.


PolyMap DataBase Edition

The PolyMap DataBase Edition lets you import any table or text file in a professional way. By using the wizard, you can choose the type of file you want to import (variable, fixed, access, paradox, dbase, foxpro,...) the separator character, the fields you want, the order of the fields, and many other settings.
There is a preview facility to see what you will get and how it will look, the possibility to reuse your import settings, and many more features.


PolyMap Toolbars

Toolbars allow you to access the commands in PolyMap the way you want to so that you can find and use them quickly. For example, you can hide or display toolbars, and move toolbars. Toolbars can contain buttons, menus, combo boxes or a combination of all.

The menu bar is a special toolbar at the top of the screen that contains menus such as File, Edit, and Map. The default menu bar contains menus and commands for working with spreadsheets or maps. If you're working with the map window, some menu options are enabled other disabled.

Some menu commands have images next to them so you can quickly associate the command with the corresponding toolbar button.

When you are opening PolyMap all toolbars are docked.

A docked toolbar is attached to one edge of the program window. You can dock a toolbar below the program title bar. When you drag a toolbar to the edge of the program window, the toolbar outline snaps into place along the length of the program window edge.

To float the toolbar move the pointer over the toolbar handle and drag the toolbar.

After this toolbar is not attached to the edge of the program window. You can change the shape of a floating toolbar.

There are tree toolbars in PolyMap:

General, Spreadsheet and Map.

General Toolbar:

The General Toolbar provide easy access to some of the most common PolyMap menu items.

It contains commands to perform basic functions like open, save, print, copy, paste and others.

Spreadsheet Toolbar:

This toolbar is active if you are using the spreadsheet window.

It helps you to speed up some functions like select font type and size, column width and row height, and other functions.

Map Toolbar:

The map toolbar is divided in 4 sections.

Labels:

The Label section allows you to show  and format labels on the active layer.

You can show all labels or only some at locations you can point out, and select a spreadsheet column to be used for labeling.

To format font type and size, and label background use the last two buttons of the label section.

Edit:

The  Edit tab let you access  the shading wizard turn on and off coloring, select and move the legend, add custom points and change the actual layer.

Select:

The Select tab let you make selections on the current active layer and highlight on the spreadsheet, show information of the map objects and pan in the map.

The combo box let you find and zoom on a object on the map.

View:

The View tab offer you functions of zooming and let you make measurements on the map.


Special commands only on the toolbars

Most of the commands of  PolyMap you will find twice on the menu bars and on the toolbars.

Some are only shown on the toolbar.

Related Topics:

Show  Selections

Add custom points

Measure Distances


Show  Selections

Making selections is a basic PolyMap operation. Using the Select tool is one way of making selections.

You must activate the tool by clicking on it before you can use it. The tool's icon is highlighted in the Map Toolbar and the cursor becomes a pointer with a star.

To select a number of objects from a map click on the desired objects with the Select tool.

Do this operation as often as you want. Every time you do it, the newly chosen object or objects are added to the current selection set. Click again on a selected object to remove it from the selection. To highlight the selected objects in the spreadsheet click on the "Show selection in spreadsheet tool". All rows of the selected objects are now shown as selection in the spreadsheet too.

In the same way you can manually select entry rows in the spreadsheet and let PolyMap show the objects in the map window. To highlight the selected objects in the map click on the "Show selection in map tool" at the spreadsheet toolbar.

Selected objects in the map window or selected rows in the spreadsheet can be deleted using the Edit Delete command.


Add custom points

How to add custom points to the map?

At the fist time using this command PolyMap creates a new custom map point layer. Enter the name of the new layer in the input box shown in this case.

If you choose Add Point from the map toolbar and click on the map, PolyMap displays the Add Point dialog box, which has the following options:

 ID , Name

Enter an ID and the Name for this new point, PolyMap just entered default values like "1" and  "Point 1".

Latitude, Longitude

Coordinate values, such as longitude and latitude, are shown in these fields.  The coordinates are in degrees, any location west of the Prime Meridian has a negative x-coordinate, and any point south of the Equator has a negative y-coordinate. Thus, 54 degrees West is represented by the X value -54.

The Add button inserts the new point in the map and adds a new row in the custom point spreadsheet.


Measure Distances

Click on this button switch on the measure feature.

When you click on the map the info window is shown with the coordinates of the point where you clicked, click again on a second point the distance between this two points are shown in the info window. If you are click on more points the distance is added in the info window.


File Menu

Items on the File menu allow you to create new maps, open existing maps, save maps, preview or print maps and spreadsheets and modify your page and print setup settings.

You can export spreadsheet data and created maps. If you are using the Database Edition of PolyMap, you can also import data from many data sources.

You can  send JPG Bitmaps of your map as attachment, to any e-mail address.

The Properties menu item lets you configure PolyMap and perform the registration.

Related Topics:

New

Open

Save / Save as

Import

Export

Page Setup

Print Preview

Print Setup

Print

Send…

Properties


New

Creates a new map file and displays the unformatted map and the ID and Name column in the spreadsheet.


Open

Opens a  user map file from disk. The map is shown with the data and map configurations as you saved it.


Save / Save as

When you save a map for the first time, you assign a file name and indicate where you want to store the file on your computer's hard disk or in another location. Each time you subsequently save the map, PolyMap updates the map file with your latest changes.


Import

Calls the DataBase Wizard. (PolyMap DataBase Edition only)

The Import Wizard lets you to import any table or text file in a  easy way.  Using the wizard, you can choose the type of file you want to import (variable, fixed, access, paradox, dbase, foxpro, ODBC...) the separator character, the fields you want,  the ordering of the fields, and many other settings.

There is a preview facility to see what you will get, the possibility to reuse saved settings, and many more.


Export

Export the spreadsheet. Files can be saved in Formula One format (.VTS files), Excel 4.0, 5.0, or 7.0 format (.XLS files), HTML format (.HTM), or tabbed text format (.TXT). Maps can be exported to Bitmap format (.bmp) enhanced Metafile (.emf) or JPEG (.jpg)


Page Setup

Displays the Page Setup dialog box. The Page Setup dialog box allows you to define header and footer text, page margins, page print order, page centering, and worksheet-related print options.


Print Preview

This option displays a print preview of the spreadsheet or the map window.

It depends on the active window whether the spreadsheet or the map is previewed.


Print Setup

Displays the standard Windows Print Setup dialog box. The Print Setup dialog box allows you to select the printer to which the document is sent, the page orientation and paper size.


Print

Prints the selected range of the spreadsheet or the map window. It depends on the active window whether the spreadsheet or the map is printed.


Send…

Using the File Send command you can now send JPG Bitmaps of your Map as attachment, to any e-mail address. You can send the currently active map window.

To send an e-mail:

Choose File Send...

You must have installed  Microsoft's Messaging API (MAPI) to use this feature. Most e-mail clients like Outlook Express or Netscape Communicator use MAPI.

When PolyMap displays the JPG compression dialog box, select a value for the image compression; higher values produce better image quality but larger image files.

PolyMap opens a new e-mail form with the attached file of the map image and the map name as subject.


Properties

The Properties menu item lets you configure PolyMap and perform the registration.

The System Tab let you define the default map directories and the units for measurements in the map window.

The Map Tab let you configure how to copy to Clipboard or to save to bitmap file the map window. All labelling options you can set here too. The graphical optimisation lat you redraw the map window faster.

Configure all Spreadsheet settings.

Configure how PolyMap opens. You can define a default map on start.

Enter the user information's on this tab.

Registration tab to enter the Registration Code and register On line.


Edit Menu

The items on the Edit menu allow you to cut, copy, paste, and delete data; insert cells; add and delete columns; find and replace values and go to a specific location in the map layer spreadsheet.

Related Topics:

Cut

Copy

Paste

Delete

Insert

Add Columns

Delete Columns

Find

Replace

Go To


Cut

Cuts the current map layer spreadsheet selection to the clipboard.


Copy

Copies the current map layer spreadsheet selection to the clipboard.

If the map window is selected the copy command copies the actual map window to the clipboard.


Paste

Pastes the contents of the clipboard to the current map layer spreadsheet selection.


Delete

Displays a dialog box that allows you to clear data or objects from the current selection. You can clear only formats, only values (including formulas), or both formats and values.

Selected objects in the map window or selected rows in the spreadsheet can be deleted using the Edit Delete command. You can't undo this operation.


Insert

Inserts cells at the location of the current selection. Cells adjacent to the insertion are shifted to make room for the new cells.


Add Columns

Displays a dialog box that allows you to add columns to the actual layer spreadsheet.


Delete Columns

Displays a dialog box that allows you to remove columns from the current layer spreadsheet.


Find

Find allows you to find data within a layer spreadsheet.

If you wish to locate a particular string of data in a layer spreadsheet, formula or value, you should use the Find dialog.

Find What Text Box	Enter the search string here.
Search Combo Box	Select from the list whether you want to search by row order or by column order.
Look In Combo Box	Select from the list whether you want to search for the string in cell formulas or values. Although you can locate formula and value data, you are not allowed to replace it. You are only allowed to replace data that actually appears within a cell.
Match Case Check Box	Click here if you want the search to match case.
Find Entire Cells Only Check Box	Click here if you want to search for a cell that contains the entire search string.
Find Next Button	Click here to find the next occurrence of the search string in the map layer spreadsheet.
Close Button	Click here to cancel the search and exit the dialog.
Replace Button	Click here to invoke the Replace dialog.


Replace

If you wish to search for data in PolyMap layer spreadsheet and replace it with other data, you have the option of using the Replace dialog.

Find What Text Box	Enter the search string here.
Replace With Text Box	Enter the replacement string here.
Search Combo Box	Select from the list whether you want to search by row order or by column order.
Match Case Check Box	Click here if you want the search to match case.
Find Entire Cells Only Check Box	Click here if you want to search for a cell that contains the entire search string.
Find Next Button	Click here to find the next occurrence of the search string in the map layer spreadsheet.
Close Button	Click here to cancel the replace and exit the dialog.
Replace Button	Click here to replace this instance and search for the next instance of the string.
Replace All	Click here to replace all instances of the search string.


Go To

Displays the Goto dialog box. When you enter a location in this dialog box, PolyMap  moves the visible area of the workbook to display this selection.

To enter multiple ranges, separate ranges with a comma. (A1:C5,D4). You can also enter a defined name if it specifies a range.


Format Menu

Items on the Format menu allow you to format spreadsheet elements.

Related Topics:

Font

Alignment

Number

Border

Patterns

Cell Protection

Column Width

Row Height


PolyMap - Format Menu - Font
Font

Displays the Font dialog box. The Font dialog box allows you to specify the font, point size, font style, and color of data in the selected range.


Alignment

Displays the Alignment dialog box. The Alignment dialog box allows you to specify the horizontal and vertical alignment of data in the selected range. In addition, you can enable and disable word wrapping.


Number

Displays the Custom Number dialog box. The Custom Format dialog box allows you to define custom number formats for data in the selected range.


Border

Displays the Border dialog box. The Border dialog box allows you to specify the placement of borders in the selected range. In addition, you can specify the border line style and color.


Patterns

Displays the Pattern dialog box. The Pattern dialog box allows you to specify the fill pattern as well as the fill pattern's fill color and pattern color for the selected range.


Cell Protection

Displays the Cell Protection dialog box. This dialog box allows you to specify whether the cells in the selected range are locked and/or hidden.

The Cell Protection dialog box allows you to set the locked attributes of a cell and hidden attributes of a formula. When a cell is locked, its contents cannot be altered. When a formula is hidden, formula text is hidden but formula results are still displayed.

After locking cells and hiding formulas, you must enable protection for the workbook before cell locking and formula hiding is enabled. Protection for a workbook is enabled using the Protect Sheet item of the Option menu.


Column Width

Displays the Column Width dialog box. The Column Width dialog box allows you to set the width and units of the selected columns, specify default column widths, and automatically set column widths to display the widest column text. In addition, you can specify whether the selected columns are shown or hidden.


Row Height

Displays the Row Height dialog box. The Row Height dialog box allows you to set the height of the selected rows, specify default row heights, and specify automatic row height. In addition, you can specify whether the selected rows are shown or hidden.


View Menu

Items on the View menu allow you to select a zoom tool and toggle the display of various tool bars.

Related Topics:

Zoom-in (+)

Zoom-out (-)

Zoom  All

Toolbars


Zoom-in (+)

The Zoom-in button allows you to access the Zoom-in tool.  Use the Zoom-in tool to get a closer area view of a map.


Zoom-out (-)

The Zoom-out button allows you to access the Zoom-out tool.  Use the Zoom-out tool to get a wider area view of a map.


Zoom  All

With the Zoom all option, you can reset the zoom and view the entire map.


Toolbars

With this option you can display or hide the Toolbars.

Check the Show box to display the selected Toolbar or clear the Show box to hide the selected Toolbar.


Map Menu

All map functions can be accessed from this menu or from the map toolbar.

Related Topics:

Layers

Layer Control

Shading Wizard

Color Map

Color Line

Clear Map

Unselect

Hide / Delete Items

Font

Labels

Pick Label

Select

Info

Show Legend

Move Legend

Move Map

Map Options


Layers

The Layer submenu shows all loaded layers of the map.

When you load a new map the layers will be checked and visible. If you want to hide a layer  you have uncheck it. To show check it again.

You can't uncheck the actual layer - in this case first move to an other layer.


Layer Control

Specify display attributes for a map layer or delete a map layer:

1.	Choose Layer Control from the map menu, the Layer Control dialog displays.

2. 	Choose the layer you want to hide or delete. Selecting toggles between hide and show.

3. 	Click the Delete button to delete the selected layer from the map. You can't undo this operation.


Shading Wizard

Calls the Shading Wizard.

Use this wizard for shading an object on the maps active layer (such as points, regions, or lines) using a color or pattern based on information about that object. For example, shade county by population, state by annual crop sales, or city by number of customers.

Related Topics:

Individual

Transparent

Simple

Prism

Symbol

Portfolio

Dot Density

Charts


Individual

This function draws the objects of polygon, line or point layers with their appearance settings as individually set by the user.


Transparent

This function is only applicable to polygon layers. The outlines of the polygons are drawn with the corresponding line attributes and all polygons remain transparent so that the layers below remain visible.


Simple

This function is applicable to polygon and line layers. First, set the data-column to be used for colorization. Then determine the number of ranges you wish to use, which will determine how many different appearance settings will need to be described.


Prism

This function is only applicable to polygon layers. First, set the data column on which the colorization will be based. Then determine the number of ranges, which will determine how many colors you want to present the polygons with. You can set the rotation and perspective (inclination) angle of the layer.


Symbol

This function can be used for all layers. First, set the data column parameter that will be the basis for the presentation of the symbols. Then determine the total number of ranges, meaning how many symbols you want to place.


Portfolio

This function is applicable to line and polygon layers. For portfolio analysis, you need a data column to graduate the x-axis (basically, find a median of all of the possible data values, below which the values might be called "low", and above which the values might be called "high"), and another data column to graduate the y-axis.


Dot Density

This function can only be used for polygon layers. The points are drawn inside the respective polygons in a random scatter. Use the Point Value parameter intelligently to determine how many points should be drawn on screen to represent a corresponding set of data points or data value ranges. Take care that a sufficiently large number of (sufficiently visible) points can be drawn per polygon.


Charts

Simple Pie Chart

Each pie segment represents a value. This value is responsible for the size of the segment relative only to the particular pie. All pies are the same size, which is set with the size parameter. With this method, pie segments in each pie are completely independent of pie segments in any other pie.

Scaled Pie Chart

Again, each pie segment represents a value, and this value is responsible for the size of the segment. But in this case, values from the entire column are compared, and the size of the pie segment is scaled accordingly. Just as important, the size of each complete pie is also scaled by comparing the sum of the values within each pie with the sums of the values in all other pies.  BE VERY CAREFUL when using this technique, since a dramatic range in values from one record to the next will cause one or very few pies to appear very large, and the remaining pies to appear microscopic. The size parameter only determines the size of the largest pie, against which all other pies are graphically scaled. This method allows direct comparison of like pie segments across all pies.

Relative Bar Chart

Analogous to simple pie charts (above). Each bar represents a value, and this value is responsible for the size of the bar. With this method, bars in each bar chart are completely independent of bars in any other bar chart. All individual bar charts appear as the same size, which is scaled with the size parameter.

Absolute Bar Chart

Analogous to scaled pie charts (above). In this case, values from the entire column are compared, and the size of each chart's bar is scaled accordingly. This method allows for direct comparison of like bars across all charts. Just as important, the size of each complete bar chart is also scaled by comparing the sum of the values within each bar chart with the sums of the values in all other bar charts.  BE VERY CAREFUL when using this technique, since a dramatic range in values from one record to the next will cause one or  very few bar charts to appear very large, and the remaining bar charts to appear microscopic. The size parameter only determines the appearance size of the largest bar chart, against which all other bar charts are graphically scaled. This method allows direct comparison of like bars across all bar charts.


Color Map

This function brings up a dialog box allowing the user specific control of brush and fill settings for the active layer.

If a point layer is the active layer it calls up the Symbol Style dialog box allowing the end-user specific control over the appearance of symbols (size and style).


Color Line

This function brings up a dialog box allowing the end-user specific control of how lines are drawn: color, thickness, and line style (e.g. dashed, etc.). The function applies only to polygon or line layers; for polygons, it applies only to the outline of the object.


Clear Map

This item is only applicable to polygon layers. The outlines of the polygons are drawn with the corresponding line attributes and all polygons remain transparent.


Unselect

Use this menu option to unselect all marked objects in the map.


Hide / Delete Items

Use the command Hide / Delete Items to show only items with values of your choice. Press Apply to execute.

To return visualizing all the hidden items of the current layer use button Show all.


PolyMap - Map Menu - Font
Font

This function sets the font parameters for the labelling of geographical objects of the actual layer.


Labels

Turns on labelling for all objects of the actual layer. You can select a labelling column on the map toolbar.


Pick Label

Clicking with the mouse on the map, an object on the active layer is labelled at the clicked position. This feature toggles; means, clicking again on the label deletes the label.


Select

Layer objects can be selected (appearing inverted onscreen) by clicking on individual objects or dragging out a rectangular zoom window.


Info

Opens an Info window showing the data of the object in the spreadsheet and indicating the longitude and latitude where you have clicked.


Show Legend

The legend is an object that explains the meanings of different symbols, colors, or fill patterns used on the map.

Use this option to make the legend visible or invisible.


Move Legend

By clicking the legend and simultaneously dragging the mouse, the legend can be dragged anywhere in the map window. The legend size can be modified by pulling the box-points.


Move Map

By clicking and simultaneous dragging the mouse, the map is shifted.


Map Options

The Map Options dialog box lets you configure the Map Background and the Layer and Label Zoom Range.

Related Topics:

Map Background

Layer and Label Zoom Range


Map Background

With this option you can change the appearance of the map background. The default background of the Map window is white.

The Map Background appears where no object is on any visible layer or in polygons set to transparent. Transparent is the default setting for new maps.


Layer and Label Zoom Range

This option is known as zoom layering. When attempting to display a dense concentration of map objects, such as miles of city streets, the map will be quite useless unless intelligent zoom layering is planned. With this option, you can set a hypothetical "height" from the sky, above which the layer will not display.

The lower two text boxes apply only to labelling. They allow the user to create hundreds of labels, but not crowd the screen with them until/unless the map is zoomed at an appropriate level.


Option Menu

This menu let you choose additional spreadsheet options.

Related Topics:

Protect Sheet

Name

Calculation

Set Print Area


Protect Sheet

If this property is checked, protection is enabled. Enabling protection means that any cells marked as hidden or locked are actually hidden and locked. Cells can be marked as locked and hidden using the Protect Cells menu item in the Format Menu.


Name

Displays the Define Name dialog box. The Define Name dialog box allows you to add and delete user-defined names.

Do not type a leading equal sign (=) at the beginning of the defined name formula.


Calculation

Displays the Calculation dialog box. The Calculation dialog box allows you to enable and disable automatic recalculation and specify iteration values for calculating circular references.


Set Print Area

Defines the currently selected range in the active worksheet as the Print Area.


Window Menu

Organize and select the windows.

Related Topics:

Cascade

Tiled


Cascade

When you choose Cascade Windows, only the contents of the topmost window is visible.  The top-most window is always the active window.


Tiled

The Window resizes and arranges all the windows next to one another so that each window is visible on the screen.

The windows that are currently open are numbered and listed at the bottom of the Window menu (e.g. 1. Spreadsheet, 2. Map).


Help Menu

PolyMap Help allows you to quick reference menu commands and options (What is the purpose of this command, How do I make this command active etc.). The tutorial provides step-by-step instructions for create thematic maps.


Worksheet Functions

ABS

ACOS

ACOSH

ASC

ADDRESS

AND

ASIN

ASINH

ATAN

ATAN2

ATANH

AVERAGE

CEILING

CHAR

CHOOSE

CLEAN

CODE

COLUMN

COLUMNS

CONCATENATE

COS

COSH

COUNT

COUNTIF

COUNTA

DATE

DATEVALUE

DAY

DAYS360

DB

DBCS

DDB

DOLLAR

ERROR.TYPE

EVEN

EXACT

EXP

FACT

FALSE

FIND

FINDB

FIXED

FLOOR

FV

HLOOKUP

HOUR

IF

INDEX

INDIRECT

INT

IPMT

IRR

ISBLANK

ISERR

ISERROR

ISLOGICAL

ISNA

ISNONTEXT

ISNUMBER

ISREF

ISTEXT

LEFT

LEFTB

LEN

LENB

LN

LOG

LOG10

LOOKUP

LOWER

MATCH

MAX

MID

MIDB

MIN

MINUTE

MIRR

MOD

MONTH

N

NA

NOT

NOW

NPER

NPV

ODD

OFFSET

OR

PI

PMT

PPMT

PRODUCT

PROPER

PV

RAND

RATE

REPLACE

REPLACEB

REPT

RIGHT

RIGHTB

ROUND

ROUNDDOWN

ROUNDUP

ROW

ROWS

SEARCH

SEARCHB

SECOND

SIGN

SIN

SINH

SLN

SQRT

STDEV

STDEVP

SUBSTITUTE

SUM

SUMIF

SUMSQ

SYD

T

TAN

TANH

TEXT

TIME

TIMEVALUE

TODAY

TRIM

TRUE

TRUNC

TYPE

UPPER

USDOLLAR

VALUE

VAR

VARP

VDB

VLOOKUP

WEEKDAY

YEAR


ABS

Description

Returns the absolute value of a number.

Syntax

ABS ( number )

Parameter	Description
number	Any number.
Remarks

An absolute value does not display a positive or negative sign.

Examples

These functions both return 1:

ABS(1)

ABS(1)


ACOS

Description

Returns the arc cosine of a number.

Syntax

ACOS ( number )

Parameter	Description
number	The cosine of the angle. The cosine can range from 1 to -1.
Remarks

The resulting angle is returned in radians (from 0 to ). To convert the resulting radians to degrees, multiply the radians by 180/PI( ).

Examples

This function returns 1.05:

ACOS(.5)

This function returns 1.77:

ACOS(.2)


ACOSH

Description

Returns the inverse hyperbolic cosine of a number.

Syntax

ACOSH ( number )

Parameter	Description
number	Any number equal to or greater than 1.
Examples

This function returns .62:

ACOSH(1.2)

This function returns 1.76:

ACOSH (3)


ASC

Description

Returns a copy of text in which the double-byte characters (if any) have been converted to single-byte. Any double-byte characters that do not have single-byte equivalents are left in their original form.

Syntax

ASC (text)

Parameter	Description
text	The text containing double-byte characters which is converted to single-byte.


ADDRESS

Description

Creates a cell address as text.

Syntax

ADDRESS ( row, column, ref_type [, a1] [, sheet] )

Parameter	Description
row	The row number for the cell address.
column	The column number for the cell address.
ref_type	The cell reference type. Following are the valid values for this argument.
	Value	Description
	1	Absolute
	2	Absolute row, relative column
	3	Relative row, absolute column
	4	Relative
a1	The reference format. This argument must be TRUE( ) to represent an A1 reference format; Formula One does not support the R1C1 reference format.
sheet	The name of an external worksheet view control. Omitting this argument assumes that the reference exists in the current spreadsheet.
Examples

This function returns $F$5:

ADDRESS(5, 6, 1)

This function returns SALES!F5:

ADDRESS(5, 6, 4, TRUE(), SALES.)


AND

Description

Returns True if all arguments are true; returns False if at least one argument is false.

Syntax

AND ( logical_list )

Parameter	Description
logical_list	A list of conditions separated by commas. You can include as many as 30 conditions in the list. The list can contain logical values or a reference to a range containing logical values. Text and empty cells are ignored. If there are no logical values in the list, the error #VALUE! is returned.
Examples

This function returns True because both arguments are true:

AND(1+1=2, 5+5=10)

This function returns False:

AND(TRUE(), FALSE())


ASIN

Description

Returns the arcsine of a number.

Syntax

ASIN ( number )

Parameter	Description
number	The sine of the resulting angle, ranging from -1 to 1.
Remarks

The resulting angle is returned in radians (ranging from -pi/2 to pi/2). To convert the resulting radians to degrees, multiply the radians by 180/pi().

Examples

This function returns 1.57:

ASIN(1)

This function returns .41:

ASIN(.4)


ASINH

Description

Returns the inverse hyperbolic sine of a number.

Syntax

ASINH ( number )

Parameter	Description
number	Any number.
Examples

This function returns 2.37:

ASINH(5.3)

This function returns -2.09:

ASINH(-4)


ATAN

Description

Returns the arctangent of a number.

Syntax

ATAN ( number )

Parameter	Description
number	The tangent of the angle.
Remarks

The resulting angle is returned in radians, ranging from -pi/2 to pi/2. To convert the resulting radians to degrees, multiply the radians by 180/pi( ).

Examples

This function returns 1.29:

ATAN(3.5)

This function returns -1.33:

ATAN(4)


ATAN2

Description

Returns the arctangent of the specified coordinates.

Syntax

ATAN2 ( x, y )

Parameter	Description
x	The x coordinate.
y	The y coordinate.
Remarks

The arctangent is the angle from the x axis to a line with end points at the origin (0, 0) and a point with the given coordinates (x, y). The angle is returned in radians, ranging from -pi to pi, excluding -pi.

Examples

This function returns 1.11:

ATAN2(3, 6)

This function returns 3.04:

ATAN2(1, .1)


ATANH

Description

Returns the inverse hyperbolic tangent of a number.

Syntax

ATANH ( number )

Parameter	Description
number	A number between -1 and 1, excluding -1 and 1.
Examples

This function returns .55:

ATANH(.5)

This function returns -.26:

ATANH(.25)


AVERAGE

Description

Returns the average of the supplied numbers. The result of AVERAGE is also known as the arithmetic mean.

Syntax

AVERAGE ( number_list )

Parameter	Description
number_list	A list of numbers separated by commas. As many as 30 numbers can be included in the list, and the list can contain numbers or a reference to a range that contains numbers. Text, logical expressions, or empty cells in a referenced range are ignored. All numeric values (including 0) are used.
Examples

This function returns 8.25:

AVERAGE(5, 6, 8, 14)

This function returns 134, the average of the values in the range C15:C17:

AVERAGE(C15:C17)


CEILING

Description

Rounds a number up to the nearest multiple of a specified significance.

Syntax

CEILING ( number, significance )

Parameter	Description
number	The value to round.
significance	The multiple to which to round.
Remarks

Regardless of the sign of the number, the value is rounded up, away from zero. If number is an exact multiple of significance, no rounding occurs.

If the number or significance is non-numeric, the error #VALUE! is returned. When the arguments have opposite signs, the error #NUM! is returned.

Examples

This function returns 1.25:

CEILING(1.23459, .05)

This function returns 150:

CEILING(148.24, 2)


CHAR

Description

Returns a character that corresponds to the supplied ASCII code.

Syntax

CHAR ( number )

Parameter	Description
number	A value between 1 and 255 that specifies an ASCII character.
Remarks

The character and associated numeric code are defined by Windows in the ASCII character set.

Examples

This function returns F:

CHAR(70)

This function returns #:

CHAR(35)


CHOOSE

Description

Returns a value from a list of numbers based on the index number supplied.

Syntax

CHOOSE ( index, item_list )

Parameter	Description
index	A number that refers to an item in item_list.
item_list	A list of numbers, formulas, or text separated by commas. This argument can also be a range reference. You can specify as many as 29 items in the list.
Remarks

index can be a cell reference; index can also be a formula that returns any value from 1 to 29. If index is less than 1 or greater than the number of items in item_list, #VALUE! is returned. If index is a fractional number, it is truncated to an integer.

Examples

This function returns Q2:

CHOOSE(2,"Q1", "Q2", "Q3", "Q4")

This function returns the average of the contents of range A1:A10:

AVERAGE(CHOOSE(1, A1:A10, B1:B10, C1:C10))


CLEAN

Description

Removes all nonprintable characters from the supplied text.

Syntax

CLEAN ( text )

Parameter	Description
text	Any worksheet information.
Remarks

Text that is imported from another environment may require this function.

Examples

This function returns Payments Due because the character returned by CHAR (8) is nonprintable:

CLEAN("Payments " & CHAR(8) & "Due")


CODE

Description

Returns a numeric code representing the first character of the supplied string.

Syntax

CODE ( text )

Parameter	Description
text	Any string.
Remarks

The numeric code and associated string are defined in your computers character set. The character set used by Windows is the ANSI character set.

Examples

This function returns 65:

CODE("A")

This function returns 98:

CODE("b")


COLUMN

Description

Returns the column number of the supplied reference.

Syntax

COLUMN ( reference )

Parameter	Description
reference	A reference to a cell or range. Omitting the argument returns the number of the column in which COLUMN is placed.
Examples

This function returns 2:

COLUMN(B3)

This function returns 4 if the function is entered in cell D2:

COLUMN()


COLUMNS

Description

Returns the number of columns in a range reference.

Syntax

COLUMNS ( range )

Parameter	Description
range	A reference to a range of cells.
Example

This function returns 4:

COLUMNS(A1:D5)


CONCATENATE

Description

Joins several text strings into one string.

Syntax

CONCATENATE ( text1, text2, .... )

Parameter	Description
text1, text2, ...	Up to 30 text items to be joined into a single text item. The text items can be strings, numbers, or single-cell references.
Remarks

The "&" operator can be used instead of CONCATENATE to join text items.

Examples

The following example returns "Sale Price" it is the same as typing "Sale"&" "&"Price":

CONCATENATE ("Sale ", "Price")

Suppose in an inventory worksheet, C2 contains "extruder1", C5 contains " gaskets", and C8 contains the number 15. The following example returns "Inventory currently holds 15 gaskets for extruder1":

CONCATENATE ("Inventory currently holds ", C8, " ", C5," for ", C2)


COS

Description

Returns the cosine of an angle.

Syntax

COS ( number )

Parameter	Description
number	The angle in radians. If the angle is in degrees, convert the angle to radians by multiplying the angle by PI()/180.
Examples

This function returns .126:

COS(1.444)

This function returns .28:

COS(5)


COSH

Description

Returns the hyperbolic cosine of a number.

Syntax

COSH ( number )

Parameter	Description
number	Any number.
Examples

This function returns 4.14:

COSH(2.10)

This function returns 1.03:

COSH(.24)


COUNT

Description

Returns the number of values in the supplied list.

Syntax

COUNT ( value_list )

Parameter	Description
value_list	A list of values. The list can contain as many as 30 values.
Remarks

COUNT only numerates numbers or numerical values such as logical values, dates, or text representations of dates. If you supply a range, only numbers and numerical values in the range are counted. Empty cells, logical values, text, and error values in the range are ignored.

Examples

This function returns 2:

COUNT(5, 6, "Q2")

This function returns 3:

COUNT("03/06/94", "06/21/94", "10/19/94")


COUNTIF

Description

Returns the number of cells within a range which meet the given criteria.

Syntax

COUNTIF ( range, criteria )

Parameter	Description
range	Range of cells you want to count.
criteria	Number, expression, or text that defines which cells are counted.


COUNTA

Description

Returns the number of nonblank values in the supplied list.

Syntax

COUNTA ( expression_list )

Parameter	Description
expression_list	A list of expressions. As many as 30 expressions can be included in the list.
Remarks

COUNTA returns the number of cells that contain data in a range. Null values (" ") are counted, but references to empty cells are ignored.

Examples

This function returns 4:

COUNTA(32, 45, "Earnings", "")

This function returns 0 when the specified range contains empty cells:

COUNTA(C38:C40)


DATE

Description

Returns the serial number of the supplied date.

Syntax

DATE ( year, month, day )

Parameter	Description
year	A number from 1900 to 2078. If year is between 1920 to 2019, you can specify two digits to represent the year; otherwise specify all four digits.
month	A number representing the month (for example, 12 represents December). If a number greater than 12 is supplied, the number is added to the first month of the specified year.
day	A number representing the day of the month. If the number you specify for day exceeds the number of days in that month, the number is added to the first day of the specified month.
Examples

This function returns 34506:

DATE(94, 6, 21)

This function returns 36225:

DATE(99, 3, 6)


DATEVALUE

Description

Returns the serial number of a date supplied as a text string.

Syntax

DATEVALUE ( text )

Parameter	Description
text	A date in text format between January 1, 1900, and December 31, 2078. If you omit the year, the current year is used.
Examples

This function returns 34399:

DATEVALUE("3/6/94")

This function returns 35058:

DATEVALUE("12/25/95")


DAY

Description

Returns the day of the month that corresponds to the date represented by the supplied number.

Syntax

DAY ( serial_number )

Parameter	Description
serial_number	A date represented as a serial number or as text (for example, 06-21-94 or 21-Jun-94).
Examples

This function returns 6:

DAY(34399)

This function returns 21:

DAY("06-21-94")


DAYS360

Description

Returns the number of days between two dates based on a 360-day year (twelve 30-day months). Use this function to help compute payments if your accounting system is based on twelve 30-day months.

Syntax

DAYS360 (start_date, end_date, [ method ] )

Parameter	Description
start_date, end_date	The two dates between which you want to know the number of days.
method	A logical value that specifies whether the European or US method should be used in the calculation. If False (or omitted), the US (NASD) method is used. If True, the European method is used. The default is based on the local translation. It should be correct for your location.
Remarks

start_date and end_date can be text strings using numbers to represent the month, day, and year (for example, "1/30/93" or "1-30-93"), or they can be serial numbers representing the dates.

If start_date occurs after end_date, DAYS360 returns a negative number.

If method is set to False and start_date is the 31st of a month, it becomes equal to the 30th of the same month. If end_date is the 31st of a month and start_date is less than the 30th of a month, the ending date becomes equal to the 1st of the next month, otherwise the ending date becomes equal to the 30th of the same month.

If method is set to True, start_dates or end_dates which occur on the 31st of a month become equal to the 30th of the same month.

Note	To determine the number of days between two dates in a normal year, you can use normal subtraction--for example, "12/31/93"-"1/1/93" equals 364.

Example

DAYS360("1/30/93", "2/1/93") equals 1


DB

Description

Returns the real depreciation of an asset for a specific period of time using the fixed-declining balance method.

Syntax

DB ( cost, salvage, life, period [, months] )

Parameter	Description
cost	The initial cost of the asset.
salvage	The salvage value of the asset.
life	The number of periods in the useful life of the asset.
period	The period for which to calculate the depreciation. The time units used to determine period and life must match.
months	The number of months in the first year of the items life. Omitting this argument assumes there are 12 months in the first year.
Example

This function returns 1451.52:

DB(10000, 1000, 7, 3)


DBCS

Description

Returns a copy of text in which the single-byte characters (if any) have been converted to double-byte characters. Any single-byte characters that do not have double-byte equivalents are left in their original (single-byte) form.

Syntax

DBCS

Parameter	Description
text	The text containing single-byte characters which is converted to double-byte characters.


DDB

Description

Returns the depreciation of an asset for a specific period of time using the double-declining balance method or a declining balance factor you supply.

Syntax

DDB ( cost, salvage, life, period [, factor] )

Parameter	Description
cost	The initial cost of the asset.
salvage	The salvage value of the asset.
life	The number of periods in the useful life of the asset.
period	The period for which to calculate the depreciation. The time units used to determine period and life must match.
factor	The rate at which the balance declines. Omitting this argument assumes a default factor of 2, the double-declining balance factor.
Remarks

The double-declining balance method uses an accelerated rate where the highest depreciation occurs in the first period, decreasing in successive periods.

All arguments for this function must be positive numbers.

Example

This function returns 1457.73:

DDB(10000,1000, 7, 3)


DOLLAR

Description

Returns the specified number as text, using currency format and the supplied precision.

Syntax

DOLLAR ( number [, precision] )

Parameter	Description
number	A number, a formula that evaluates to a number, or a reference to a cell that contains a number.
precision	A value representing the number of decimal places to the right of the decimal point. Omitting this argument assumes the standard number of decimal places for the local currency.
Note

"Local" currency refers to the currency format for the current system, i.e., the one specified in Regional Settings in Control Panel

Remarks

Dollar will return the specified number format as text using currency format for the current system. If you wish to always convert to the USDollar format, regardless of the language of your system, then use the  worksheet function.

Examples

This function returns $1023.79:

DOLLAR(1023.789)

This function returns $500:

DOLLAR(495.301, 2)


ERROR.TYPE

Description

Returns a number corresponding to an error.

Syntax

ERROR.TYPE ( error_ref )

Parameter	Description
error_ref	A cell reference.
Remarks

The following error text or numbers can be returned by this function.

Number	Description
1	#NULL!
2	#DIV/0!
3	#VALUE!
4	#REF!
5	#NAME?
6	#NUM!
7	#N/A
#N/A	Other
Example

This function returns 2 if the formula in cell A1 attempts to divide by zero:

ERROR.TYPE(A1)


EVEN

Description

Rounds the specified number up to the nearest even integer.

Syntax

EVEN ( number )

Parameter	Description
number	Any number, a formula that evaluates to a number, or a reference to a cell that contains a number.
Examples

This function returns 4:

EVEN(2.5)

This function returns 2032:

EVEN(2030.45)


EXACT

Description

Compares two expressions for identical, case-sensitive matches. True is returned if the expressions are identical; False is returned if they are not.

Syntax

EXACT ( expression1, expression2 )

Parameter	Description
expression1	Any text.
expression2	Any text.
Examples

This function returns True:

EXACT("Match", "Match")

This function returns False:

EXACT("Match", "match")


EXP

Description

Returns e raised to the specified power. The constant e is 2.71828182845904 (the base of the natural logarithm).

Syntax

EXP ( number )

Parameter	Description
number	Any number as the exponent.
Examples

This function returns 12.18:

EXP(2.5)

This function returns 20.09:

EXP(3)


FACT

Description

Returns the factorial of a specified number.

Syntax

FACT ( number )

Parameter	Description
number	Any non-negative integer. If you supply a real number, FACT truncates the number to an integer before calculation.
Examples

This function returns 2:

FACT(2.5)

This function returns 720:

FACT(6)


FALSE

Description

Returns the logical value False. This function always requires the trailing parentheses.

Syntax

FALSE ( )


FIND

Description

Searches for a string of text within another text string and returns the character position at which the search string first occurs.

Syntax

FIND ( search_text, text [, start_position] )

Parameter	Description
search_text	The text to find. If you specify an empty string (" "), FIND matches the first character in text.
text	The text to be searched.
start_position	The character position in text where the search begins. The first character in text is character number 1. When you omit this argument, the default starting position is character number 1.
Remarks

FIND is case-sensitive. You cannot use wildcard characters in the search_text.

Examples

This function returns 12:

FIND("time", "Theres no time like the present")

This function returns 19:

FIND("4", "Aisle 4, Part 123-4-11", 9)


FINDB

Description

Searches for a string of text within another text string and returns the byte position at which the search string first occurs.

Syntax

FINDB ( search_text, text [, start_position] )

Parameter	Description
search_text	The text to find. If you specify an empty string (" "), FINDB matches the first byte in text.
text	The text to be searched.
start_position	The byte position in text where the search begins. The first byte in text is byte number 1. When you omit this argument, the default starting position is byte number 1.
Remarks

FINDB is case-sensitive. You cannot use wildcard characters in the search_text.

Examples

This function returns 12:

FINDB("time", "Theres no time like the present")

This function returns 19:

FINDB("4", "Aisle 4, Part 123-4-11", 9)


FIXED

Description

Rounds a number to the supplied precision, formats the number in decimal format, and returns the result as text.

Syntax

FIXED ( number [, precision][, no_commas] )

Parameter	Description
number	Any number.
precision	The number of digits that appear to the right of the decimal place. When this argument is omitted, a default precision of 2 is used. If you specify negative precision, number is rounded to the left of the decimal point. You can specify a precision as great as 127 digits.
no_commas	Determines if thousands separators (commas) are used in the result. Use 1 to exclude commas in the result. If no_commas is 0 or the argument is omitted, thousands separators are included (for example, 1,000.00).
Examples

This function returns 2,000.500:

FIXED(2000.5, 3)

This function returns 2010:

FIXED(2009.5, 1, 1)


FLOOR

Description

Rounds a number down to the nearest multiple of a specified significance.

Syntax

FLOOR ( number, significance )

Parameter	Description
number	The value to round.
significance	The multiple to which to round.
Remarks

Regardless of the sign of the number, the value is rounded down, toward zero. If number is an exact multiple of significance, no rounding occurs.

If number or significance is non-numeric, #NAME? is returned. When the arguments have opposite signs, #NUM! is returned.

Examples

This function returns 1.2:

FLOOR(1.23459, .05)

This function returns 148:

FLOOR(148.24, 2)


FV

Description

Returns the future value of an annuity based on regular payments and a fixed interest rate.

Syntax

FV ( interest, nper, payment [, pv] [, type] )

Parameter	Description
interest	The fixed interest rate.
nper	The number of payments in an annuity.
payment	The fixed payment made each period.
pv	The present value, or the lump sum amount, the annuity is currently worth. When you omit this argument, a present value of 0 is assumed.
type	Indicates when payments are due. Use 0 if payments are due at the end of the period or 1 if payments are due at the beginning of the period. When you omit this argument, 0 is assumed.
Remarks

The units used for interest must match those used for nper. For example, if the annuity has an 8 percent annual interest rate over a period of 5 years, specify 8 percent/12 for interest and 5*12 for nper.

Cash paid out, such as a payment, is shown as a negative number. Cash received, such as a dividend check, is shown as a positive number.

Examples

This function returns 4,774.55:

FV(5%, 8, 500)

This function returns 531,550.86:

FV(10%/12, 240, 700, 1)


HLOOKUP

Description

Searches the top row of a table for a value and returns the contents of a cell in that table that corresponds to the location of the search value.

Syntax

HLOOKUP ( search_item, search_range, row_index )

Parameter	Description
search_item	A value, text string, or reference to a cell containing a value that is matched against data in the top row of search_range.
search_range	A reference to the range (table) to be searched. The cells in the first row of search_range can contain numbers, text, or logical values. The contents of the first row must be in ascending order (for example, 2, 1, 0, 2...A through Z, False, True). Text searches are not case-sensitive.
row_index	The row in search_range from which the matching value is returned. row_index can be a number from 1 to the number of rows in search_range. If row_index is less than 1, the error #VALUE! is returned. When row_index is greater than the number of rows in the table, the error #REF! is returned.
Remarks

HLOOKUP compares the information in the top row of search_range to the supplied search_item. When a match is found, information located in the same column and supplied row (row_index) is returned.

If search_item cannot be found in the top row of search_range, the largest value that is less than search_item is used. When search_item is less than the smallest value in the first row of the search_range, the error #REF! is returned.

Examples

The following examples use this worksheet.

This function returns 22.63:

HLOOKUP("Northeast", B1:E5, 3)

This function returns #REF!:

HLOOKUP("Pacific", B1:E5, 7)


HOUR

Description

Returns the hour component of the specified time in 24-hour format.

Syntax

HOUR ( serial_number )

Parameter	Description
serial_number	The time as a serial number. The decimal portion of the number represents time as a fraction of the day.
Remarks

The result is an integer ranging from 0 (12:00 AM) to 23 (11:00 PM).

Examples

This function returns 9:

HOUR(34259.4)

This function returns 23:

HOUR(34619.976)


IF

Description

Tests the condition and returns the specified value.

Syntax

IF ( condition, true_value, false_value )

Parameter	Description
condition	Any logical expression.
true_value	The value to be returned if condition evaluates to True.
false_value	The value to be returned if condition evaluates to False.
Example

This function returns Greater if the contents of A1 is greater than 10 and Less if the contents of A1 is less than 10:

IF(A1>10, "Greater", "Less")


INDEX

Description

Returns the contents of a cell from a specified range.

Syntax

INDEX ( reference [, row] [, column] [, range_number] )

Parameter	Description
reference	A reference to one or more ranges. If reference specifies more than one range, separate each reference with a comma and enclose reference in parentheses. For example, (A1:C6, B7:E14, F4). If each range in reference contains only one row or column, you can omit the row or column argument. For example, if reference is A1:A15, you can omit the column argument INDEX(A1:A15, 3,, 1).
row	The row number in reference from which to return data.
column	Column number in reference from which to return data.
range_number	Specifies the range from which data is returned if reference contains more than one range. For example, if reference is (A1:A10, B1:B5, D14:E23), A1:A10 is range_number 1, B1:B5 is range_number 2, and D14:E23 is range_number 3.
Remarks

If row, column, and range_number do not point to a cell within reference, #REF! is returned. If row and column are omitted, INDEX returns the range in reference specified by range_number.

Examples

The following examples use this worksheet.

This function returns $1415.35:

INDEX(A2:B6, 2, 2)

This function returns $1634.58:

INDEX((A2:B6, D2:E6), 4, 2, 2)


INDIRECT

Description

Returns the contents of the cell referenced by the specified cell.

Syntax

INDIRECT ( ref_text [, a1] )

Parameter	Description
ref_text	A reference to a cell that references a third cell. If ref_text is not a valid reference, the error #REF! is returned.
a1	The reference format. This argument must be TRUE() to represent an A1 reference format; Formula One does not support the R1C1 reference format.
Example

This function returns the contents of the cell that C1 references. If C1 contains "D1," then the contents of D1is returned:

INDIRECT(C1)


INT

Description

Rounds the supplied number down to the nearest integer.

Syntax

INT ( number )

Parameter	Description
number	Any real number.
Examples

This function returns 10:

INT(10.99)

This function returns -11:

INT(10.99)


IPMT

Description

Returns the interest payment of an annuity for a given period, based on regular payments and a fixed periodic interest rate.

Syntax

IPMT ( interest, per, nper, pv, [fv], [type] )

Parameter	Description
interest	The fixed periodic interest rate.
per	The period for which to return the interest payment. This number must be between 1 and nper.
nper	The number of payments.
pv	The present value, or the lump sum amount the annuity is currently worth.
fv	The future value, or the value after all payments are made. If this argument is omitted, the future value is assumed to be 0.
type	Indicates when payments are due. Use 0 if payments are due at the end of the period or 1 if payments are due at the beginning of the period. When you omit this argument, 0 is assumed.
Remarks

The units used for interest must match those used for nper. For example, if the annuity has an 8 percent annual interest rate over a period of 5 years, specify 8 percent/12 for interest and 5*12 for nper.

Cash paid out, such as a payment, is shown as a negative number. Cash received, such as a dividend check, is shown as a positive number.

Examples

This function returns -117.87:

IPMT(8%/12, 2, 48, 18000)

This function returns -117.09:

IPMT(8%/12, 2, 48, 18000, 0, 1)


IRR

Description

Returns internal rate of return for a series of periodic cash flows.

Syntax

IRR ( cash_flow [, guess] )

Parameter	Description
cash_flow	A reference to a range that contains values for which to calculate the internal rate of return. The values must contain at least one positive and one negative value. During calculation, IRR uses the order in which the values appear to determine the order of the cash flow. Text, logical values, and empty cells in the range are ignored.
guess	The estimate of the internal rate of return. If no argument is supplied, a rate of return of 10 percent is assumed.
Remarks

The internal rate of return is the interest rate received for an investment consisting of payments (specified by negative numbers) and investments (specified by positive numbers).

IRR is calculated iteratively, cycling through the calculation until the result is accurate to .00001 percent. If the result cannot be found after 20 iterations, #NUM! is returned. When this occurs, supply a different value for guess.

Examples

The following examples use this worksheet.

This function returns 3.72 percent:

IRR(B1:B6)

This function returns -49.26 percent:

IRR(B1:B3, 20%)


ISBLANK

Description

Determines if the specified cell is blank.

Syntax

ISBLANK ( reference )

Parameter	Description
reference	A reference to any cell.
Remarks

If the referenced cell is blank, True is returned. False is returned if the cell is not blank.

Example

This function returns True if A1 is a blank cell:

ISBLANK(A1)


ISERR

Description

Determines if the specified expression returns an error value.

Syntax

ISERR ( expression )

Parameter	Description
expression	Any expression.
Remarks

If the expression returns any error except #N/A!, True is returned. Otherwise, False is returned.

Example

This function returns True if A1 contains a formula that returns an error such as #NUM!:

ISERR(A1)


ISERROR

Description

Determines if the specified expression returns an error value.

Syntax

ISERROR ( expression )

Parameter	Description
expression	Any expression.
Remarks

If the expression returns any error value, such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!, True is returned. Otherwise, False is returned.

Examples

This function returns True:

ISERROR(4/0)

This function returns False if A1 contains a formula that does not return an error.

ISERROR(A1)


ISLOGICAL

Description

Determines if the specified expression returns a logical value.

Syntax

ISLOGICAL ( expression )

Parameter	Description
expression	Any expression.
Remarks

If the expression returns a logical value, True is returned. Otherwise, False is returned.

Example

This function returns True because ISBLANK returns a logical value:

ISLOGICAL(ISBLANK(A1))


ISNA

Description

Determines if the specified expression returns the value not available error.

Syntax

ISNA ( expression )

Parameter	Description
expression	Any expression.
Remarks

If the expression returns the #N/A! error, True is returned. Otherwise, False is returned.

Example

This function returns True if cell A1 contains the NA ( ) function or returns the error value #N/A!:

ISNA(A1)


ISNONTEXT

Description

Determines if the specified expression is not text.

Syntax

ISNONTEXT ( expression )

Parameter	Description
expression	Any expression.
Remarks

If the expression returns any value that is not text, True is returned. Otherwise, False is returned.

Examples

This function returns True if cell F3 contains a number or is a blank cell:

ISNONTEXT(F3)

This function returns False:

ISNONTEXT("text")


ISNUMBER

Description

Determines if the specified expression is a number.

Syntax

ISNUMBER ( expression )

Parameter	Description
expression	Any expression.
Remarks

If the expression returns a number, True is returned. Otherwise, False is returned. If expression returns a number represented as text (for example, 12), False is returned.

Examples

This function returns True:

ISNUMBER(123.45)

This function returns False:

ISNUMBER("123")


ISREF

Description

Determines if the specified expression is a range reference.

Syntax

ISREF ( expression )

Parameter	Description
expression	Any expression.
Remarks

If the expression returns a range reference, True is returned. Otherwise, False is returned.

Example

This function returns True:

ISREF(A3)


ISTEXT

Description

Determines if the specified expression is text.

Syntax

ISTEXT ( expression )

Parameter	Description
expression	Any expression.
Remarks

If the expression returns text, True is returned. Otherwise, False is returned.

Example

This function returns True:

ISTEXT("2nd Quarter")


LEFT

Description

Returns the leftmost characters from the specified text string.

Syntax

LEFT ( text [, num_chars] )

Parameter	Description
text	Any text string.
num_chars	The number of characters to return. This value must be greater than or equal to zero. If num_chars is greater than the number of characters in text, the entire string is returned. Omitting this argument assumes a value of 1.
Examples

This function returns 2:

LEFT("2nd Quarter")

This function returns 2nd:

LEFT("2nd Quarter", 3)


LEFTB

Description

Returns the leftmost byte from the specified text string.

Syntax

LEFTB ( text [, num_chars] )

Parameter	Description
text	Any text string.
Num_chars	The number of  bytes to return. This value must be greater than or equal to zero. If num_chars is greater than the number of bytes in text, the entire string is returned. Omitting this argument assumes a value of 1.
Examples

This function returns 2:

LEFTB("2nd Quarter")

This function returns 2nd:

LEFTB("2nd Quarter", 3)


LEN

Description

Returns the number of characters in the supplied text string.

Syntax

LEN ( text )

Parameter	Description
text	Any text string. Spaces in the string are counted as characters.
Examples

This function returns 11:

LEN("3rd Quarter")

This function returns 3:

LEN("1-3")


LENB

Description

Returns the number of bytes in the supplied text string.

Syntax

LENB ( text )

Parameter	Description
text	Any text string. Spaces in the string are counted as bytes.
Examples

This function returns 11:

LENB("3rd Quarter")

This function returns 3:

LENB("1-3")


LN

Description

Returns the natural logarithm (based on the constant e) of a number.

Syntax

LN ( number )

Parameter	Description
number	Any positive real number.
Remarks

LN is the inverse of the EXP function.

Examples

This function returns 2.50:

LN(12.18)

This function returns 3.00:

LN(20.09)


LOG

Description

Returns the logarithm of a number to the specified base.

Syntax

LOG ( number [, base] )

Parameter	Description
number	Any positive real number.
base	The base of the logarithm. Omitting this argument assumes a base of 10.
Examples

This function returns 0:

LOG(1)

This function returns 1:

LOG(10)


LOG10

Description

Returns the base-10 logarithm of a number.

Syntax

LOG10 ( number )

Parameter	Description
number	Any positive real number.
Examples

This function returns 2.41:

LOG10(260)

This function returns 2:

LOG10(100)


LOOKUP

Description

Searches for a value in one range and returns the contents of the corresponding position in a second range.

Syntax

LOOKUP ( lookup_value, lookup_range, result_range )

Parameter	Description
lookup_value	The value for which to search in the first range.
lookup_range	The first range to search and contains only one row or one column. The range can contain numbers, text, or logical values. To search lookup_range correctly, the expressions in the range must be placed in ascending order (for example, 2, 1, 0, 1, 2...A through Z, False, True). The search is not case-sensitive.
result_range	A range of one row or one column that is the same size as lookup_range.
Remarks

If lookup_value does not have an exact match in lookup_range, the largest value that is less than or equal to lookup_value is found and the corresponding position in result_range is returned. When lookup_value is smaller than the data in lookup_range, #N/A is returned.

Examples

The following examples use this worksheet.

This function returns Detroit:

LOOKUP("North", A2:A7, B2:B7)

This function returns #N/A:

LOOKUP("Alabama", A2:A7, B2:B7)


LOWER

Description

Changes the characters in the specified string to lowercase characters. Numeric characters in the string are not changed.

Syntax

LOWER ( text )

Parameter	Description
text	Any string.
Examples

This function returns 3rd quarter:

LOWER("3rd Quarter")

This function returns john doe:

LOWER("JOHN DOE")


MATCH

Description

A specified value is compared against values in a range. The position of the matching value in the search range is returned.

Syntax

MATCH ( lookup_value, lookup_range, comparison )

Parameter	Description
lookup_value	The value against which to compare. It can be a number, text, or logical value or a reference to a cell that contains one of those values.
lookup_range	The range to search; it contains only one row or one column. The range can contain numbers, text, or logical values.
comparison	A number that represents the type of comparison to be made between lookup_value and the values in lookup_range. When you omit this argument, comparison method 1 is assumed. When comparison is 0, the first value that is equal to lookup_value is matched. When using this comparison method, the values in lookup_range can be in any order. When comparison is 1, the largest value that is less than or equal to lookup_value is matched. When using this comparison method, the values in lookup_range must be in ascending order (for example, ...-2, -1, 0, 1, 2..., A through Z, False, True). When comparison is -1, the smallest value that is greater than or equal to lookup_value is matched. When using this comparison method, the values in lookup_range must be in descending order (for example, True, False, Z through A, ...2, 1, 0, -1, -2...).
Remarks

When using comparison method 0 and lookup_value is text, lookup_value can contain wildcard characters. The wildcard characters are * (asterisk), which matches any sequence of characters, and ? (question mark), which matches any single character.

When no match is found for lookup_value, #N/A is returned.

Examples

The following examples use this worksheet.

This function returns 5:

MATCH(7600, B2:B7,1)

This function returns 2:

MATCH("D*", A2:A7,0)


MAX

Description

Returns the largest value in the specified list of numbers.

Syntax

MAX ( number_list )

Parameter	Description
number_list	A list of as many as 30 numbers, separated by commas.
	The list can contain numbers, logical values, text representations of numbers, or a reference to a range containing those values. Error values or text that cannot be translated into numbers return errors.
.	If a range reference is included in the list, text, logical expressions, and empty cells in the range are ignored If there are no numbers in the list, 0 is returned.
Examples

This function returns 500:

MAX(50, 100, 150, 500, 200)

This function returns the largest value in the range:

MAX(A1:F12)


MID

Description

Returns the specified number of characters from a text string, beginning with the specified starting position.

Syntax

MID ( text, start_position, num_chars )

Parameter	Description
text	The string from which to return characters.
start_position	The position of the first character to return from text.
	If start_position is 1, the first character in text is returned. If start_position is greater than the number of characters in text, an empty string (" ") is returned.  If start_position is less than 1, #VALUE! is returned.
num_chars	The number of characters to return. If num_chars is negative, #VALUE! is returned.
Remarks

If start_position plus the number of characters in num_chars exceeds the length of text, the characters from start_position to the end of text are returned.

Examples

This function returns Expenses:

MID("Travel Expenses", 8, 8)

This function returns 45:

MID("Part #45-7234", 7, 2)


MIDB

Description

Returns the specified number of bytes from a text string, beginning with the specified starting position.

Syntax

MIDB ( text, start_position, num_chars )

Parameter	Description
text	The string from which to return bytes.
start_position	The position of the first byte to return from text.
	If start_position is 1, the first byte in text is returned. If start_position is greater than the number of bytes in text, an empty string (" ") is returned.  If start_position is less than 1, #VALUE! is returned.
num_chars	The number of bytes to return. If num_chars is negative, #VALUE! Is returned.
Remarks

If start_position plus the number of bytes in num_chars exceeds the length of text, the bytes from start_position to the end of text are returned.

Examples

This function returns Expenses:

MIDB("Travel Expenses", 8, 8)

This function returns 45:

MIDB("Part #45-7234", 7, 2)


MIN

Description

Returns the smallest value in the specified list of numbers.

Syntax

MIN ( number_list )

Parameter	Description
number_list	A list of as many as 30 numbers, separated by commas. The list can contain numbers, logical values, text representations of numbers, or a reference to a range containing those values.
	Error values or text that cannot be translated into numbers return errors. If a range reference is included in the list, text, logical expressions, and empty cells in the range are ignored. If there are no numbers in the list, 0 is returned
Examples

This function returns 50:

MIN(50, 100, 150, 500, 200)

This function returns the smallest value in the range:

MIN(A1:F12)


MINUTE

Description

Returns the minute that corresponds to the supplied date.

Syntax

MINUTE ( serial_number )

Parameter	Description
serial_number	The time as a serial number. The decimal portion of the number represents time as a fraction of the day.
Remarks

The result is an integer ranging from 0 to 59.

Examples

This function returns 36:

MINUTE(34506.4)

This function returns 48:

MINUTE(34399.825)


MIRR

Description

Returns the modified internal rate of return for a series of periodic cash flows.

Syntax

MIRR ( cash_flows, finance_rate, reinvest_rate )

Parameter	Description
cash_flow	A reference to a range that contains values for which to calculate the modified internal rate of return. The values must contain at least one positive and one negative value.
	Values that represent cash received should be positive; negative values represent cash paid. During calculation, MIRR uses the order in which the values appear to determine the order of cash flow. Text, logical values, and empty cells in the range are ignored.
finance_rate	The interest rate paid on money used in the cash flow.
reinvest_rate	The interest rate received on money reinvested from the cash flow.
Remarks

The modified internal rate of return considers the cost of the investment and the interest received on the reinvestment of cash.

Examples

The following examples use this worksheet.

This function returns 5.20 percent:

MIRR(B1:B6, 12%, 8%)

This function returns 40.93 percent:

MIRR(B1:B3, 12%, 8%)


MOD

Description

Returns the remainder after dividing a number by a specified divisor.

Syntax

MOD ( number, divisor )

Parameter	Description
number	Any number.
divisor	Any nonzero number. If divisor is 0, #DIV/0! is returned.
Examples

This function returns 1:

MOD(23, 3)

This function returns -2:

MOD(23, 3)


MONTH

Description

Returns the month that corresponds to the supplied date.

Syntax

MONTH ( serial_number )

Parameter	Description
serial_number	The date as a serial number or as text (for example, 06-21-94 or 21-Jun-94).
Remarks

MONTH returns a number ranging from 1 (January) to 12 (December).

Examples

This function returns 6:

MONTH("06-21-94")

This function returns 10:

MONTH(34626)


N

Description

Tests the supplied value and returns the value if it is a number.

Syntax

N ( value )

Parameter	Description
value	A value or a reference to a cell containing a value to test.
Remarks

Numbers are returned as numbers, serial numbers formatted as dates are returned as serial numbers, and the logical function TRUE() is returned as 1. All other expressions return 0.

Examples

This function returns 32467:

N(32467)

This function returns 1 if A4 contains the logical function TRUE:

N(A4)


NA

Description

Returns the error value #N/A, which represents not available.

Syntax

NA ( )

Remarks

Use NA to mark cells that lack data without leaving them empty. Empty cells may not be correctly represented in some calculations.

Although NA does not use arguments, you must supply the empty parentheses to correctly reference the function.


NOT

Description

Returns a logical value that is the opposite of its value.

Syntax

NOT ( logical )

Parameter	Description
logical	An expression that returns a logical value such as True or False.
Remarks

If logical is false, NOT returns True. Conversely, if logical is true, NOT returns False.

Examples

This function returns False:

NOT(TRUE())

This function returns False:

NOT(MONTH("12/25/94") = 12)


NOW

Description

Returns the current date and time as a serial number.

Syntax

NOW ( )

Remarks

In a serial number, numbers to the left of the decimal point represent the date; numbers to the right of the decimal point represent the time. The result of this function changes only when a recalculation of the worksheet occurs.


NPER

Description

Returns the number of periods of an investment based on regular periodic payments and a fixed interest rate.

Syntax

NPER ( interest, pmt, pf [, fv] [, type] )

Parameter	Description
interest	The fixed interest rate.
pmt	The fixed payment made each period. Generally, pmt includes the principle and interest, not taxes or other fees.
pf	The present value, the lump-sum amount that a series of future payments is currently worth.
fv	The future value, the balance to attain after the final payment. Omitting this argument assumes a future balance of 0.
type	Indicates when payments are due. Use 0 if payments are due at the end of the period or 1 if payments are due at the beginning of the period. When you omit this argument, 0 is assumed.
Examples

This function returns 36.67:

NPER(12%/12, 350, 300, 16000, 1)

This function returns 36.98:

NPER(1%, 350, 300, 16000)


NPV

Description

Returns the net present value of an investment based on a series of periodic payments and a discount rate.

Syntax

NPV ( discount_rate, value_list )

Parameter	Description
discount_rate	The rate of discount for one period.
value_list	A list of as many as 29 arguments or a reference to a range that contains values that represent payments and income. During calculation, NPV uses the order in which the values appear to determine the order of cash flow. Numbers, empty cells, and text representations of numbers are included in the calculation. Errors and text that cannot be translated into numbers are ignored.  If value_list is a range reference, only numeric data in the range is included in the calculation. Other types of data in the range, such as empty cells, logical values, text, and error values are ignored.
Remarks

The time span NPV uses for calculation begins one period before the first cash flow date and ends when the last cash flow payment is made. This function is based on future cash flows. When your first cash flow occurs at the beginning of the first period, the first value must be added to the NPV result, not supplied as a value in value_list.

Example

This function returns 811.57:

NPV(8%, 12000, 3000, 3000, 3000, 7000)


ODD

Description

Rounds the specified number up to the nearest odd integer.

Syntax

ODD ( number )

Parameter	Description
number	Any number, a formula that evaluates to a number, or a reference to a cell that contains a number.
Examples

This function returns 5:

ODD(3.5)

This function returns 7:

ODD(6)


OFFSET

Description

Returns the contents of a range that is offset from a starting point in the spreadsheet.

Syntax

OFFSET ( reference, rows, columns [, height] [, width] )

Parameter	Description
reference	A reference to a cell from which the offset reference is based. If you specify a range reference, #VALUE! is returned.
rows	The number of rows from reference that represents the upper-left cell of the offset range. A positive number represents rows below the starting cell; a negative number represents rows above the starting cell. If rows places the upper-left cell of the offset range outside the spreadsheet boundary, #REF! is returned.
columns	The number of columns from reference that represents the upper-left cell of the offset range. A positive number represents columns right of the starting cell; a negative number represents columns left of the starting cell. If columns places the upper-left cell of the offset range outside the spreadsheet boundary, #REF! is returned.
height	A positive number representing the number of rows to include in the offset range. Omitting this argument assumes a single row.
width	A positive number representing the number of columns to include in the offset range. Omitting this argument assumes a single column.
Remarks

OFFSET does not change the current selection in the worksheet. Because it returns a reference, OFFSET can be used in any function that requires or uses a cell or range reference as an argument.

Examples

This function returns the contents of cell D4:

OFFSET(B1, 3, 2, 1, 1)

This function returns the sum of the values in the range E3:F5:

SUM(OFFSET(A1, 2, 4, 3, 2))


OR

Description

Returns True if at least one of a series of logical arguments is true.

Syntax

OR ( logical_list )

Parameter	Description
logical_list	A list of conditions separated by commas. You can include as many as 30 conditions in the list. The list can contain logical values or a reference to a range containing logical values. Text and empty cells are ignored. If there are no logical values in the list, the error value #VALUE! is returned.
Example

This function returns True because one of the arguments is true:

OR(1 + 1 = 1, 5 + 5 = 10)


PI

Description

Returns the value of pi (p), which is approximately 3.14159265358979 when calculated to 15 significant digits.

Syntax

PI ( )

Remarks

Although PI does not use arguments, you must supply the empty parentheses to correctly reference the function.


PMT

Description

Returns the periodic payment of an annuity, based on regular payments and a fixed periodic interest rate.

Syntax

PMT ( interest, nper, pv [, fv] [, type] )

Parameter	Description
interest	The fixed periodic interest rate.
nper	The number of periods in the annuity.
pv	The present value, or the amount the annuity is currently worth.
fv	The future value, or the amount the annuity will be worth. When you omit this argument, a future value of 0 is assumed.
type	Indicates when payments are due. Use 0 if payments are due at the end of the period or 1 if payments are due at the beginning of the period. When you omit this argument, 0 is assumed.
Remarks

PMT returns only the principal and interest payment, it does not include taxes or other fees.

The units used for interest must match those used for nper. For example, if the annuity has an 8 percent annual interest rate over a period of 5 years, specify 8 percent/12 for interest and 5*12 for nper.

Cash paid out, such as a payment, is shown as a negative number. Cash received, such as a dividend check, is shown as a positive number.

Examples

This function returns -439.43:

PMT(8%/12, 48, 18000)

This function returns -436.52:

PMT(8%/12, 48, 18000, 0, 1)


PPMT

Description

Returns the principle paid on an annuity for a given period.

Syntax

PPMT ( interest, per, nper, pv, [fv], [type] )

Parameter	Description
interest	The fixed periodic interest rate.
per	The period for which to return the principle.
nper	The number of periods in the annuity.
pv	The present value, or the amount the annuity is currently worth.
fv	The future value, or the amount the annuity will be worth. When you omit this argument, a future value of 0 is assumed.
type	Indicates when payments are due. Use 0 if payments are due at the end of the period or 1 if payments are due at the beginning of the period. When you omit this argument, 0 is assumed.
Remarks

The units used for interest must match those used for nper. For example, if the annuity has an 8 percent annual interest rate over a period of 5 years, specify 8 percent/12 for interest and 5*12 for nper.

Examples

This function returns -321.56:

PPMT(8%/12, 2, 48, 18000)

This function returns -319.43:

PPMT(8%/12, 2, 48, 18000, 0, 1)


PRODUCT

Description

Multiplies a list of numbers and returns the result.

Syntax

PRODUCT ( number_list )

Parameter	Description
number_list	A list of as many as 30 numbers, separated by commas. The list can contain numbers, logical values, text representations of numbers, or a reference to a range containing those values. Error values or text that cannot be translated into numbers return errors. If a range reference is included in the list, text, logical expressions, and empty cells in the range are ignored. All numeric values, including 0, are used in the calculation.
Example

This function returns 24:

PRODUCT(1, 2, 3, 4)


PROPER

Description

Returns the specified string in proper-case format.

Syntax

PROPER ( text )

Parameter	Description
text	Any string.
Remarks

In proper-case format, the first alphabetic character in a word is capitalized. If an alphabetic character follows a number, punctuation mark, or space, it is capitalized. All other alphabetic characters are lowercase. Numbers are not changed by PROPER.

Examples

This function returns 3Rd Quarter:

=PROPER("3rd Quarter")

This function returns John Doe:

=PROPER("JOHN DOE")


PV

Description

Returns the present value of an annuity, considering a series of constant payments made over a regular payment period.

Syntax

PV ( interest, nper, pmt [, fv] [, type] )

Parameter	Description
interest	The fixed periodic interest rate.
nper	The number of payment periods in the investment.
pmt	The fixed payment made each period.
fv	The future value, or the amount the annuity will be worth. When you omit this argument, a future value of 0 is assumed.
type	Indicates when payments are due. Use 0 if payments are due at the end of the period or 1 if payments are due at the beginning of the period. When you omit this argument, 0 is assumed.
Remarks

The units used for interest must match those used for nper. For example, if the annuity has an 8 percent annual interest rate over a period of 5 years, specify 8 percent/12 for interest and 5*12 for nper.

Cash paid out, such as a payment, is shown as a negative number. Cash received, such as a dividend check, is shown as a positive number.

Examples

This function returns -17999.89:

PV(8%/12, 48, 439.43)

This function returns 17999.89:

PV(8%/12, 48, -439.43)


RAND

Description

Returns a number selected randomly from a uniform distribution greater than or equal to 0 and less than 1.

Syntax

RAND ( )

Remarks

Although RAND does not use arguments, you must supply the empty parentheses to correctly reference the function.

Example

This function returns a random number greater than or equal to 0 and less than 10:.

RAND()*10


RATE

Description

Returns the interest rate per period of an annuity, given a series of constant cash payments made over a regular payment period.

Syntax

RATE ( nper, pmt, pv [, fv] [, type] [, guess] )

Parameter	Description
nper	The number of periods in the annuity.
pmt	The fixed payment made each period. Generally, pmt includes only principle and interest, not taxes or other fees.
pv	The present value of the annuity.
fv	The future value, or the amount the annuity will be worth. When you omit this argument, a future value of 0 is assumed.
type	Indicates when payments are due. Use 0 if payments are due at the end of the period or 1 if payments are due at the beginning of the period. When you omit this argument, 0 is assumed.
guess	Your estimate of the interest rate. If no argument is supplied, a value of 0.1 (10 percent) is assumed.
Remarks

RATE is calculated iteratively, cycling through the calculation until the result is accurate to .00001 percent. If the result cannot be found after 20 iterations, #NUM! is returned. When this occurs, supply a different value for guess.

Example

The following example returns the monthly interest rate of .0067; the annual interest rate (.0067 multiplied by 12) is 8 percent:

RATE(48, 439.43, 18000)


REPLACE

Description

Replaces part of a text string with another text string.

Syntax

REPLACE ( orig_text, start_position, num_chars, repl_text )

Parameter	Description
orig_text	The original text string.
start_position	The character position where the replacement begins. If start_position is greater than the number of characters in orig_text, repl_text is appended to the end of orig_text. If start_position is less than 1, #VALUE! is returned.
num_chars	The number of characters to replace. If this argument is negative, #VALUE! is returned.
repl_text	The replacement text string.
Examples

This function returns "For the year: 1994":

REPLACE("For the year: 1993", 18, 1, "4")


REPLACEB

Description

Replaces part of a text string with another text string.

Syntax

REPLACEB ( orig_text, start_position, num_chars, repl_text )

Parameter	Description
orig_text	The original text string.
start_position	The byte position where the replacement begins. If start_position is greater than the number of bytes in orig_text, repl_text is appended to the end of orig_text. If start_position is less than 1, #VALUE! is returned.
num_chars	The number of bytes to replace. If this argument is negative, #VALUE! Is returned.
repl_text	The replacement text string.
Examples

This function returns "For the year: 1994":

REPLACEB("For the year: 1993", 18, 1, "4")


REPT

Description

Repeats a text string the specified number of times.

Syntax

REPT ( text, number )

Parameter	Description
text	Any text string.
number	The number of times you want text to repeat. If number is 0, empty text (" ") is returned.
Remarks

The result of REPT cannot exceed 255 characters.

Example

This function returns error-error-error-:

REPT("error-", 3)


RIGHT

Description

Returns the rightmost characters from the given text string.

Syntax

RIGHT ( text [, num_chars] )

Parameter	Description
text	Any text string.
num_chars	The number of characters to return. The value must be greater than or equal to zero. If num_chars is greater than the number of characters in text, the entire string is returned. Omitting this argument assumes a value of 1.
Examples

This function returns r:

RIGHT("2nd Quarter")

This function returns Quarter:

RIGHT("2nd Quarter", 7)


RIGHTB

Description

Returns the rightmost bytes from the given text string.

Syntax

RIGHTB ( text [, num_chars] )

Parameter	Description
text	Any text string.
num_chars	The number of bytes to return. The value must be greater than or equal to zero. If num_chars is greater than the number of bytes in text, the entire string is returned. Omitting this argument assumes a value of 1.
Examples

This function returns r:

RIGHTB("2nd Quarter")

This function returns Quarter:

RIGHTB("2nd Quarter", 7)


ROUND

Description

Rounds the given number to the supplied number of decimal places.

Syntax

ROUND ( number, precision )

Parameter	Description
number	Any value.
precision	The number of decimal places to which number is rounded.  When a negative precision is used, the digits to the right of the decimal point are dropped and the absolute number of significant digits specified by precision are replaced with zeros. If precision is 0, number is rounded to the nearest integer.
Example

This function returns 123.46:

ROUND(123.456, 2)

This function returns 9900:

ROUND(9899.435, 2)


ROUNDDOWN

Description

Rounds a number down.

Syntax

ROUNDDOWN ( number, numberOfDigits )

Parameter	Description
number	Any real number you want to round.
numberOfDigits	The number of decimal places to which number is rounded.  When a negative precision is used, the digits to the right of the decimal point are dropped and the absolute number of significant digits specified by precision are replaced with zeros. If precision is 0, number is rounded down to the nearest integer.
Example

This function returns 3.141:

ROUNDDOWN(3.14159, 3)

This function returns 31400:

ROUNDDOWN(31415.92654, -2)


ROUNDUP

Description

Rounds the given number up to the supplied number of decimal places.

Syntax

ROUNDUP ( number, numberOfDigits )

Parameter	Description
number	Any value you want to round up.
numberOfDigits	The number of decimal places to which number is rounded.  When a negative precision is used, the digits to the right of the decimal point are dropped and the absolute number of significant digits specified by precision are replaced with zeros. If precision is 0, number is rounded up to the nearest integer.
Example

This function returns 77:

ROUNDUP(76.9,0)

This function returns 31500:

ROUNDUP(31415.92654, -2)


ROW

Description

Returns the row number of the supplied reference.

Syntax

ROW ( reference )

Parameter	Description
reference	A cell or range reference. Omitting this argument returns the row number of the cell in which ROW is entered.
Examples

This function returns 3:

ROW(B3)


ROWS

Description

Returns the number of rows in a range reference.

Syntax

ROWS ( range )

Parameter	Description
range	A reference to a range of cells.
Examples

This function returns 5:

ROWS(A1:D5)

This function returns 6:

ROWS(C30:F35)


SEARCH

Description

Locates the position of the first character of a specified text string within another text string.

Syntax

SEARCH ( search_text, text [, start_position] )

Parameter	Description
search_text	The text to find.  To search for an asterisk or question mark, include a tilde (~) before the character. The search string can contain wildcard characters. The available wildcard characters are * (asterisk), which matches any sequence of characters, and ? (question mark), which matches any single character.
text	The text to be searched.
start_position	The character position where the search begins. If the number you specify is less than 0 or greater than the number of characters in text, #VALUE! is returned. Omitting this argument assumes a starting position of 1.
Remarks

Text is searched from left to right, starting at the position specified. The search is not case-sensitive. If text does not contain the search string, #VALUE! is returned.

Examples

This function returns 6:

SEARCH("?5", "Bin b45")

This function returns 5:

SEARCH("b", "Bin b45", 4)


SEARCHB

Description

Locates the position of the first byte of a specified text string within another text string.

Syntax

SEARCHB ( search_text, text [, start_position] )

Parameter	Description
search_text	The text to find.  To search for an asterisk or question mark, include a tilde (~) before the character. The search string can contain wildcards. The available wildcard characters are * (asterisk), which matches any sequence of characters, and ? (question mark), which matches any single character.
Text	The text to be searched.
start_position	The byte position where the search begins. If the number you specify is less than 0 or greater than the number of bytes in text, #VALUE! is returned. Omitting this argument assumes a starting position of 1.
Remarks

Text is searched from left to right, starting at the position specified. The search is not case-sensitive. If text does not contain the search string, #VALUE! is returned.

Examples

This function returns 6:

SEARCHB("?5", "Bin b45")

This function returns 5:

SEARCHB("b", "Bin b45", 4)


SECOND

Description

Returns the second that corresponds to the supplied date.

Syntax

SECOND ( serial_number )

Parameter	Description
serial_number	The time as a serial number. The decimal portion of the number represents time as a fraction of the day.
Examples

This function returns 58:

SECOND(.259)

This function returns 46:

SECOND(34657.904)


SIGN

Description

Determines the sign of the specified number.

Syntax

SIGN ( number )

Parameter	Description
number	Any number.
Remarks

SIGN returns 1 if the specified number is positive, -1 if it is negative, and 0 if it is 0.

Examples

This function returns -1:

SIGN(123)

This function returns 1:

SIGN(123)


SIN

Description

Returns the sine of the supplied angle.

Syntax

SIN ( number )

Parameter	Description
number	The angle in radians. If the angle is in degrees, convert the angle to radians by multiplying the angle by PI( )/180.
Examples

This function returns .85:

SIN(45)

This function returns .89:

SIN(90)


SINH

Description

Returns the hyperbolic sine of the specified number.

Syntax

SINH ( number )

Parameter	Description
number	Any number.
Examples

This function returns 1.18:

SINH(1)

This function returns 10.02:

SINH(3)


SLN

Description

Returns the depreciation of an asset for a specific period of time using the straight-line balance method.

Syntax

SLN ( cost, salvage, life )

Parameter	Description
cost	The initial cost of the asset.
salvage	The salvage value of the asset.
life	The number of periods of the useful life of the asset.
Example

This function returns 1285.71:

SLN(10000, 1000, 7)


SQRT

Description

Returns the square root of the specified number.

Syntax

SQRT ( number )

Parameter	Description
number	Any positive number. If you specify a negative number, the error #NUM! is returned.
Examples

This function returns 3:

SQRT(9)

This function returns 1.58:

SQRT(2.5)


STDEV

Description

Returns the standard deviation of a population based on a sample of supplied values. The standard deviation of a population represents an average of deviations from the population mean within a list of values.

Syntax

STDEV ( number_list )

Parameter	Description
number_list	A list of as many as 30 numbers, separated by commas. The list can contain numbers or a reference to a range that contains numbers.
Example

This function returns .56:

STDEV(4.0, 3.0, 3.0, 3.5, 2.5, 4.0, 3.5)


STDEVP

Description

Returns the standard deviation of a population based on an entire population of values. The standard deviation of a population represents an average of deviations from the population mean within a list of values.

Syntax

STDEVP ( number_list )

Parameter	Description
number_list	A list of as many as 30 numbers, separated by commas. The list can contain numbers or a reference to a range that contains numbers.
Example

This function returns .52:

STDEVP(4.0, 3.0, 3.0, 3.5, 2.5, 4.0, 3.5)


SUBSTITUTE

Description

Replaces a specified part of a text string with another text string.

Syntax

SUBSTITUTE ( text, old_text, new_text [, instance] )

Parameter	Description
text	A text string that contains the text to replace. You can also specify a reference to a cell that contains text.
old_text	The text string to be replaced.
new_text	The replacement text.
instance	Specifies the occurrence of old_text to replace. If this argument is omitted, every instance of old_text is replaced.
Examples

This function returns "Second Quarter Results":

SUBSTITUTE("First Quarter Results", "First", "Second")

This function returns "Shipment 45, Bin 52":

SUBSTITUTE("Shipment 45, Bin 45", "45", "52", 2)


SUM

Description

Returns the sum of the supplied numbers.

Syntax

SUM ( number_list )

Parameter	Description
number_list	A list of as many as 30 numbers, separated by commas.  The list can contain numbers, logical values, text representations of numbers, or a reference to a range containing those values.  Error values or text that cannot be translated into numbers return errors. If a range reference is included in the list, text, logical expressions, and empty cells in the range are ignored.
Examples

This function returns 6000:

SUM(1000, 2000, 3000)

This function returns 4000 when each cell in the range contains 1000:

SUM(A10:D10)


SUMIF

Description

Returns the sum of the specified cells based on the given criteria.

Syntax

SUMIF ( range, criteria, sum_range )

Parameter	Description
range	The range of cells you want evaluated.
criteria	A number, expression, or text that defines which cells are added. For example, criteria can be expressed as 15, "15", ">15", "cars".
sum_range	The actual cells to sum. These cells are only summed if their corresponding cells in range match the criteria. If this argument is omitted, the cells in range are summed.


SUMSQ

Description

Squares each of the supplied numbers and returns the sum of the squares.

Syntax

SUMSQ ( number_list )

Parameter	Description
number_list	A list of as many as 30 numbers, separated by commas.  The list can contain numbers, logical values, text representations of numbers, or a reference to a range containing those values.  Error values or text that cannot be translated into numbers return errors. If a range reference is included in the list, text, logical expressions, and empty cells in the range are ignored.
Example

This function returns 302:

SUMSQ(9, 10, 11)


SYD

Description

Returns the depreciation of an asset for a specified period using the sum-of-years method. This depreciation method uses an accelerated rate, where the greatest depreciation occurs early in the useful life of the asset.

Syntax

SYD ( cost, salvage, life, period )

Parameter	Description
cost	The initial cost of the asset.
salvage	The salvage value of the asset.
life	The number of periods in the useful life of the asset.
period	The period for which to calculate the depreciation. The time units used to determine per and life must match.
Example

This function returns 1607.14:

SYD(10000, 1000, 7, 3)


T

Description

Tests the supplied value and returns the value if it is text.

Syntax

T ( value )

Parameter	Description
value	The value to test.
Remarks

Empty text (" ") is returned for any value that is not text.

Examples

This function returns Report:

T("Report")

This function returns empty text (" ") if A4 contains a number:

T(A4)


TAN

Description

Returns the tangent of the specified angle.

Syntax

TAN ( number )

Parameter	Description
number	The angle in radians. To convert a number expressed as degrees to radians, multiply the degrees by PI( )/180.
Example

This function returns 0.752:

TAN(0.645)

This function returns 1:

TAN(45*PI()/180)


TANH

Description

Returns the hyperbolic tangent of a number.

Syntax

TANH ( number )

Parameter	Description
number	Any number.
Examples

This function returns -.96:

TANH(-2)

This function returns .83:

TANH(1.2)


TEXT

Description

Returns the given number as text, using the specified formatting.

Syntax

TEXT ( number, format )

Parameter	Description
number	Any value, a formula that evaluates to a number, or a reference to a cell that contains a value.
format	A string representing a number format. The string can be any valid format string including General, M/DD/YY, or H:MM AM/PM. The format must be surrounded by a set of double quotation marks. Asterisks cannot be included in format.
Examples

This function returns 123.620:

TEXT(123.62, "0.000")

This function returns 10/19/94:

TEXT(34626.2, "MM/DD/YY")


TIME

Description

Returns a serial number for the supplied time.

Syntax

TIME ( hour, minute, second )

Parameter	Description
hour	A number from 0 to 23.
minute	A number from 0 to 59.
second	A number from 0 to 59.
Examples

This function returns .52:

TIME(12, 26, 24)

This function returns .07:

TIME(1, 43, 34)


TIMEVALUE

Description

Returns a serial number for the supplied text representation of time.

Syntax

TIMEVALUE ( text )

Parameter	Description
text	A time in text format.
Examples

This function returns .07:

TIMEVALUE("1:43:43 am")

This function returns .59:

TIMEVALUE("14:10:07")


TODAY

Description

Returns the current date as a serial number.

Syntax

TODAY ( )

Remarks

This function is updated only when the worksheet is recalculated.


TRIM

Description

Removes all spaces from text except single spaces between words.

Syntax

TRIM ( text )

Parameter	Description
text	Any text string or a reference to a cell that contains a text string.
Remarks

Text that is imported from another environment may require this function.

Example

This function returns Level 3, Gate 45:

TRIM(" Level 3, Gate 45 ")


TRUE

Description

Returns the logical value True. This function always requires the trailing parentheses.

Syntax

TRUE ( )


TRUNC

Description

Truncates the given number to an integer.

Syntax

TRUNC ( number [, precision] )

Parameter	Description
number	Any value.
precision	The number of decimal places allowed in the truncated number. Omitting this argument assumes a precision of 0.
Remarks

TRUNC removes the fractional part of a number to the specified precision without rounding the number.

Example

This function returns 123.45:

TRUNC(123.456, 2)

This function returns 9800:

TRUNC(9899.435, 2)


TYPE

Description

Returns the argument type of the given expression.

Syntax

TYPE ( expression )

Parameter	Description
expression	Any expression.
Remarks

The valid values returned by this argument are:

Number	Description
1	Number
2	Text string
4	Logical value
16	Error value
Examples

This function returns 1 if cell A1 contains a number:

TYPE(A1)

This function returns 2:

TYPE("Customer")


UPPER

Description

Changes the characters in the specified string to uppercase characters.

Syntax

UPPER ( text )

Parameter	Description
text	Any string.
Remarks

Numeric characters in the string are not changed.

Examples

This function returns 3RD QUARTER:

UPPER("3rd Quarter")

This function returns JOHN DOE:

UPPER("JOHN DOE")


USDOLLAR

Description

Returns the specified number as text using the US Dollar format and the supplied precision. Omitting the precision argument assumes two decimal places.

Syntax

USDOLLAR (number [,precision])

Parameter	Description
number	A number, a formula that evaluates to a number, or a reference to a cell that contains a number.
precision	A value representing the number of decimal places to the right of the decimal point. Omitting this argument assumes two decimal places.


VALUE

Description

Returns the specified text as a number.

Syntax

VALUE ( text )

Parameter	Description
text	Any text string, a formula that evaluates to a text string, or a cell reference that contains a text string. You can also specify a date or time in a recognizable format (for example, M/DD/YY for dates or H:MM AM/PM for time). If the format is not recognized, #VALUE! is returned.
Examples

This function returns 9800:

VALUE(9800)

This function returns 123:

VALUE("123")


VAR

Description

Returns the variance of a population based on a sample of values.

Syntax

VAR ( number_list )

Parameter	Description
number_list	A list of as many as 30 numbers, separated by commas. The list can contain numbers or a reference to a range that contains numbers.
Example

This function returns .31:

VAR(4.0, 3.0, 3.0, 3.5, 2.5, 4.0, 3.5)


VARP

Description

Returns the variance of a population based on an entire population of values.

Syntax

VARP ( number_list )

Parameter	Description
number_list	A list of as many as 30 numbers, separated by commas. The list can contain numbers or a reference to a range that contains numbers.
Example

This function returns .27:

VARP(4.0, 3.0, 3.0, 3.5, 2.5, 4.0, 3.5)


VDB

Description

Returns the depreciation of an asset for a specified period using a variable method of depreciation.

Syntax

VDB ( cost, salvage, life, start_period, end_period [, factor] [, method] )

Parameter	Description
cost	The initial cost of the asset.
salvage	The salvage value of the asset.
life	The number of periods in the useful life of the asset.
start_period	The beginning period for which to calculate the depreciation. The time units used to determine start_period and life must match.
end_period	The ending period for which to calculate the depreciation. The time units used to determine end_period and life must match.
factor	The rate at which the balance declines. Omitting this argument assumes a default of 2, which is the double-declining balance factor.
method	A logical value that determines if you want to switch to straight-line depreciation when depreciation is greater than the declining balance calculation. Use True to maintain declining balance calculation; use False or omit the argument to switch to straight-line depreciation calculation.
Example

This function returns 1041.23:

VDB(10000, 1000, 7, 3, 4)


VLOOKUP

Description

Searches the first column of a table for a value and returns the contents of a cell in that table that corresponds to the location of the search value.

Syntax

VLOOKUP ( search_item, search_range, column_index )

Parameter	Description
search_item	A value, text string, or reference to a cell containing a value that is matched against data in the top row of search_range.
search_range	The reference of the range (table) to be searched. The cells in the first column of search_range can contain numbers, text, or logical values. The contents of the first column must be in ascending order (for example, -2, -1, 0, 2...A through Z, False, True). Text searches are not case-sensitive.
column_index	The column in the search range from which the matching value is returned.  column_index can be a number from 1 to the number of rows in the search range. If column_index is less than 1, #VALUE! is returned. When column_index is greater than the number of rows in the table, #REF! is returned.
Remarks

VLOOKUP compares the information in the first column of search_range to the supplied search_item. When a match is found, information located in the same row and supplied column (column _index) is returned.

If search_item cannot be found in the first column of search_range, the largest value that is less than search_item is used. When search_item is less than the smallest value in the first column of the search_range, #REF! is returned.

Examples

The following examples use this worksheet.

This function returns $28,700:

VLOOKUP("Clark", A2:E9, 4)

This function returns 3961:

VLOOKUP("Lee", A2:E9, 3)


WEEKDAY

Description

Returns the day of the week that corresponds to the supplied date.

Syntax

WEEKDAY ( serial_number )

Parameter	Description
serial_number	The date as a serial number or as text (for example, 06-21-94 or 21-Jun-94).
Remarks

WEEKDAY returns a number ranging from 1 (Sunday) to 7 (Saturday).

Examples

This function returns 1, indicating Sunday:

WEEKDAY(34399.92)

This function returns 3, indicating Tuesday:

WEEKDAY("06/21/94")


YEAR

Description

Returns the year that corresponds to the supplied date.

Syntax

YEAR ( serial_number )

Parameter	Description
serial_number	The date as a serial number or as text (for example, 06-21-94 or 21-Jun-94).
Examples

This function returns 1993:

YEAR(34328)

This function returns 1994:

YEAR("06/21/94")