SoftArtisans ExcelWriter Guide

All Functions Math Functions String Functions Logical Functions
Time Functions Statistical Functions Information Functions
Lookup & Reference Functions Database Functions Financial Functions

Formula Functions

The only functions available in
ExcelWriterSE and ExcelWriterLE
are sum and average.

ExcelWriter supports almost all major math, string, boolean, time, statistical, database, lookup and reference formulas or functions that are part of Excel.

With ExcelWriter, formulas cannot be executed server-side to retrieve values for use in the script. All formulas are executed at the time that the file is downloaded and opened in Excel. (Please note: If the downloaded files are opened client side with the free Microsoft Excel Viewer, all formulas will be rendered with "0" as the resultant value.)

Formulas can be inserted into any cell in a worksheet by using the Cell.Formula property. They can be inserted either by hard coding the cell address or by dynamic coding using a variable, such as NumRows (the return value of the CopyFromRecordset method which indicates where the last row of the pasted recordset will be positioned in the worksheet) to provide a sum at the bottom of a column.

When applying a formula to a cell, always include the entire string in double quotation marks ("") and begin the string with an equal sign (=), as you would when creating a Microsoft Excel spreadsheet.

Any formula that is not mentioned in this list can be inserted into an existing spreadsheet or template and used in ExcelWriter. This list is comprised of formulas that can be added into your ExcelWriter script to be included at run-time.

The following example demonstrates using a formula.

ws.Cells("b6").Formula = "=sum(b2:b3)"

ExcelWriter 2 allows you to use values from a different sheet when assigning a formula to a cell, as in the following examples.

ws3.Cells(1,1).Formula = "=sum(sheet1!A1,Sheet2!A2)"
ws3.Cells(2,1).Formula = "=sum(sheet1!A1:A3)"

Note that ExcelWriter does not support the following formulations:

Sheet1:Sheet2!A1:C3
Sheet1!A1:C3;Sheet4!A1:C3

ExcelWriter Supported Functions

Functions by Category

Functions Alphabetically

Math Functions
- abs*
- acos*
- asin*
- atan*
- atan2*
- ceiling*
- cos*
- degrees*
- even*
- exp*
- fact*
- floor*
- int*
- ln*
- log*
- log10*
- mod*
- odd*
- pi*
- product*
- radians*
- round*
- rounddown*
- roundup*
- sin*
- sqrt*
- subtotal*
- sum
- sumif*
- tan*
String Functions
- concatenate*
- len*
- mid*
- replace*
- value*
Logical Functions
- and*
- false*
- if*
- not*
- or*
- true*
Time Functions
- date*
- datevalue*
- day*
- days360*
- hour*
- minute*
- month*
- now*
- second*
- time*
- timevalue*
- today*
- weekday*
- year*
Statistical Functions
- average
- count*
- counta*
- countif*
- max*
- median*
- min*
- mode*
- percentile*
- stdev*
- stdevp*
Information Functions
- iserr*
- iserror*
- isnumber*
- type*
Lookup and Reference Functions
- Column*
- Columns*
- Hlookup*
- Row*
- Rows*
- Vlookup*
Database Functions
- Daverage*
- Dcount*
- Dcounta*
- Dget*
- Dmax*
- Dmin*
- Dproduct*
- Dstdev*
- Dstdevp*
- Dsum*
- Dvar*
- Dvarp*
Financial Functions
- fv*
- npv*
- pmt*
- pv*

*This function is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriterFree. The only functions available in ExcelWriterSE, ExcelWriterLE, and ExcelWriterFree are sum and average.

Note: Microsoft Excel 2000 Help was consulted in preparing "Formula Functions" and its subsections.