EasyXLS

Formulas

Concept

EasyXLS™ library allows you to read Excel formulas, export Excel files with formulas and calculate Excel formulas.

Formulas are mathematical expressions that allow calculations for sheets values.

EasyXLS™ component provides all the functions supported by Excel as far as formulas are concerned. It provides arithmetic, logical and unary operators, formulas with numbers, dates and strings, formulas with cell references, formulas with cell ranges, formulas with names and with arrays.

Formulas elements:

Arithmetic operators Arithmetic operators
+   Addition
-   Subtraction
*   Multiplication
/   Division
%   Percent
^   Exponentiation
Logical operators Logical operators
=   Equal to
>   Greater than
<   Less than
>=   Greater than or equal to
<=   Less than or equal to
<>   Not equal to
Text operator Text operator
&   Concatenates two strings
Reference operators Reference operators
~   Range operator, which produces one reference to all the cells between two references, including the two references. Note: This is the: operator from Excel
,   Union operator, which combines multiple references into one reference
|   Intersection operator, which produces one reference to cells common to the two references. Note: This is the space operator from Excel
Unary operator Unary operator
+   Positive sign
-   Negative sign
Parenthesis Parenthesis
(   Left parenthesis
)   Right parenthesis
Functions Functions
Financial functions Financial functions
ACCRINT Returns the accrued interest for a security that pays periodic interest
ACCRINTM Returns the accrued interest for a security that pays interest at maturity
AMORDEGRC Returns the depreciation for each accounting period
AMORLINC Returns the depreciation for each accounting period
COUPDAYBS Returns the number of days from the beginning of the coupon period to the settlement date
COUPDAYS Returns the number of days in the coupon period that contains the settlement date
COUPDAYSNC Returns the number of days from the beginning of the coupon period to the settlement date
COUPNCD Returns a number that represents the next coupon date after the settlement date
COUPNUM Returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon
COUPPCD Returns a number that represents the previous coupon date before the settlement date
CUMIPMT Returns the cumulative interest paid on a loan between start_period and end_period
CUMPRINC Returns the cumulative principal paid on a loan between start_period and end_period
DB Returns the depreciation of an asset for a specified period using the fixed-declining balance method
DDB Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify
DISC Returns the discount rate for a security
DOLLARDE Converts a dollar price expressed as a fraction into a dollar price expressed as a decimal number
DOLLARFR Converts a dollar price expressed as a decimal number into a dollar price expressed as a fraction
DURATION Returns the Macauley duration for an assumed par value of $100
EFFECT Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year
FV Returns the future value of an investment
FVSCHEDULE Returns the future value of an initial principal after applying a series of compound interest rates
INTRATE Returns the interest rate for a fully invested security
IPMT Returns the interest payment for an investment for a given period
IRR Returns the internal rate of return for a series of cash flows
ISPMT Calculates the interest paid during a specific period of an investment
MDURATION Returns the modified duration for a security with an assumed par value of $100
MIRR Returns the internal rate of return where positive and negative cash flows are financed at different rates
NOMINAL Returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year
NPER Returns the number of periods for an investment
NPV Returns the net present value of an investment based on a series of periodic cash flows and a discount rate
ODDFPRICE Returns the price per $100 face value of a security having an odd (short or long) first period
ODDFYIELD Returns the yield of a security that has an odd (short or long) first period
ODDLPRICE Returns the price per $100 face value of a security having an odd (short or long) last coupon period
ODDLYIELD Returns the yield of a security that has an odd (short or long) last period
PMT Returns the periodic payment for an annuity
PPMT Returns the payment on the principal for an investment for a given period
PRICE Returns the price per $100 face value of a security that pays periodic interest
PRICEDISC Returns the price per $100 face value of a discounted security
PRICEMAT Returns the price per $100 face value of a security that pays interest at maturity
PV Returns the present value of an investment
RATE Returns the interest rate per period of an annuity
RECEIVED Returns the amount received at maturity for a fully invested security
SLN Returns the straight-line depreciation of an asset for one period
SYD Returns the sum-of-years' digits depreciation of an asset for a specified period
TBILLEQ Returns the bond-equivalent yield for a treasury bill
TBILLPRICE Returns the price per $100 face value for a treasury bill
TBILLYIELD Returns the yield for a treasury bill
VDB Returns the depreciation of an asset for a specified or partial period using a declining balance method
XIRR Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic
XNPV Returns the net present value for a schedule of cash flows that is not necessarily periodic
YIELD Returns the yield on a security that pays periodic interest
YIELDDISC Returns the annual yield for a discounted security
YIELDMAT Returns the annual yield of a security that pays interest at maturity
Database and List Management functions Database and List Management functions
DAVERAGE Returns the average of selected database entries
DCOUNT Counts the cells that contain numbers in a database
DCOUNTA Counts nonblank cells in a database
DGET Extracts from a database a single record that matches the specified criteria
DMAX Returns the maximum value from selected database entries
DMIN Returns the minimum value from selected database entries
DPRODUCT Multiplies the values in a particular field of records that match the criteria in a database
DSTDEV Estimates the standard deviation based on a sample of selected database entries
DSTDEVP Calculates the standard deviation based on the entire population of selected database entries
DSUM Adds the numbers in the field column of records in the database that match the criteria
DVAR Estimates variance based on a sample from selected database entries
DVARP Calculates variance based on the entire population of selected database entries
Information functions Information functions
CELL Returns information about the formatting
ERROR.TYPE Returns a number corresponding to an error type
INFO Returns information about the current operating environment
ISBLANK Returns TRUE if the value is blank
ISERR Returns TRUE if the value is any error value except #N/A
ISERROR Returns TRUE if the value is any error value
ISEVEN Returns TRUE if number is even, or FALSE if number is odd
ISLOGICAL Returns TRUE if the value is a logical value
ISNA Returns TRUE if the value is the #N/A error value
ISNONTEXT Returns TRUE if the value is not text
ISNUMBER Returns TRUE if the value is a number
ISODD Returns TRUE if number is odd, or FALSE if number is even
ISREF Returns TRUE if the value is a reference
ISTEXT Returns TRUE if the value is text
N Returns a value converted to a number
NA Returns the error value #N/A
TYPE Returns a number indicating the data type of a value
Logical functions Logical functions
AND Returns TRUE if all its arguments are TRUE
FALSE Returns the logical value FALSE
IF Specifies a logical test to perform
IFERROR Returns the future value of an initial principal after applying a series of compound interest rates
NOT Reverses the logic of its argument
OR Returns TRUE if any argument is TRUE
TRUE Returns the logical value TRUE
Lookup and Reference functions Lookup and Reference functions
ADDRESS Returns a reference as text to a single cell in a worksheet
AREAS Returns the number of areas in a reference
CHOOSE Chooses a value from a list of values
COLUMN Returns the column number of a reference
COLUMNS Returns the number of columns in a reference
HLOOKUP Looks in the top row of an array and returns the value of the indicated cell
HYPERLINK Creates a shortcut or jump that opens a document stored on a network server
INDEX Uses an index to choose a value from a reference or array
INDIRECT Returns a reference indicated by a text value
LOOKUP Looks up values in a vector or array
MATCH Looks up values in a reference or array
OFFSET Returns a reference offset from a given reference
ROW Returns the row number of a reference
ROWS Returns the number of rows in a reference
RTD Retrieves real-time data from a program that supports COM automation
TRANSPOSE Returns the transpose of an array
VLOOKUP Looks in the first column of an array and moves across the row to return the value of a cell
Math and Trigonometry functions Math and Trigonometry functions
ABS Returns the absolute value of a number
ACOS Returns the arccosine of a number
ACOSH Returns the inverse hyperbolic cosine of a number
ASIN Returns the arcsine of a number
ASINH Returns the inverse hyperbolic sine of a number
ATAN Returns the arctangent of a number
ATAN2 Returns the arctangent from x- and y- coordinates
ATANH Returns the inverse hyperbolic tangent of a number
CEILING Rounds a number to the nearest integer or to the nearest multiple of significance
COMBIN Returns the number of combinations for a given number of objects
COS Returns the cosine of a number
COSH Returns the hyperbolic cosine of a number
DEGREES Converts radians to degrees
EVEN Rounds a number up to the nearest even integer
EXP Returns e raised to the power of a given number
FACT Returns the factorial of a number
FACTDOUBLE Returns the double factorial of a number
FLOOR Rounds a number down
GCD Returns the greatest common divisor of two or more integers
INT Rounds a number down to the nearest integer
LCM Returns the least common multiple of integers
LN Returns the natural logarithm of a number
LOG Returns the logarithm of a number to a specified base
LOG10 Returns the base-10 logarithm of a number
MDETERM Returns the matrix determinant of an array
MINVERSE Returns the matrix inverse of an array
MMULT Returns the matrix product of two arrays
MOD Returns the remainder from division
MROUND Returns a number rounded to the desired multiple
MULTINOMIAL Returns the ratio of the factorial of a sum of values to the product of factorials
ODD Rounds a number up to the nearest odd integer
PI Returns the value of Pi
POWER Returns the result of a number raised to a power
PRODUCT Multiplies its arguments
QUOTIENT Returns the integer portion of a division
RADIANS Converts degrees to radians
RAND Returns a random number between 0 and 1
RANDBETWEEN Returns a random number between the numbers you specify
ROMAN Converts an arabic numeral to roman
ROUND Rounds a number to a specified number of digits
ROUNDDOWN Rounds a number down
ROUNDUP Rounds a number up
SERIESSUM Returns the sum of a power series based on a formula
SIGN Returns the sign of a number
SIN Returns the sine of the given angle
SINH Returns the hyperbolic sine of a number
SQRT Returns a positive square root
SQRTPI Returns the square root of (number * p)
SUBTOTAL Returns a subtotal in a list or database
SUM Adds its arguments
SUMIF Adds the cells specified by a given criteria
SUMIFS Adds the cells in a range that meet multiple criteria
SUMPRODUCT Returns the sum of the products of corresponding array components
SUMSQ Returns the sum of the squares of the arguments
SUMX2MY2 Returns the sum of the difference of squares of corresponding values in two arrays
SUMX2PY2 Returns the sum of the sum of squares of corresponding values in two arrays
SUMXMY2 Returns the sum of squares of differences of corresponding values in two arrays
TAN Returns the tangent of a number
TANH Returns the hyperbolic tangent of a number
TRUNC Truncates a number to an integer
Statistical functions Statistical functions
AVEDEV Returns the average of the absolute deviations of data points from their mean
AVERAGE Returns the average of its arguments
AVERAGEA Returns the average (arithmetic mean) of the arguments
AVERAGEIF Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria
AVERAGEIFS Returns the average (arithmetic mean) of all cells that meet multiple criteria
BETADIST Returns the cumulative beta probability density function
BETAINV Returns the inverse of the cumulative beta probability density function
BINOMDIST Returns the individual term binomial distribution probability
CHIDIST Returns the one-tailed probability of the chi-squared distribution
CHIINV Returns the inverse of the one-tailed probability of the chi-squared distribution
CHITEST Returns the test for independence
CONFIDENCE Returns the confidence interval for a population mean
CORREL Returns the correlation coefficient between two data sets
COUNT Counts how many numbers are in the list of arguments
COUNTA Counts how many values are in the list of arguments
COUNTBLANK Counts the number of blank cells within a range
COUNTIF Counts the number of nonblank cells within a range that meet the given criteria
COUNTIFS Counts the number of cells within a range that meet multiple criteria
COVAR Returns covariance
CRITBINOM Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
DEVSQ Returns the sum of squares of deviations
EXPONDIST Returns the exponential distribution
FDIST Returns the F probability distribution
FINV Returns the inverse of the F probability distribution
FISHER Returns the Fisher transformation
FISHERINV Returns the inverse of the Fisher transformation
FORECAST Returns a value along a linear trend
FREQUENCY Returns a frequency distribution as a vertical array
FTEST Returns the result of an F-test
GAMMADIST Returns the gamma distribution
GAMMAINV Returns the inverse of the gamma cumulative distribution
GAMMALN Returns the natural logarithm of the gamma function
GEOMEAN Returns the geometric mean
GROWTH Returns values along an exponential trend
HARMEAN Returns the harmonic mean
HYPGEOMDIST Returns the hypergeometric distribution
INTERCEPT Returns the intercept of the linear regression line
KURT Returns the kurtosis of a data set
LARGE Returns the k-th largest value in a data set
LINEST Returns the parameters of a linear trend
LOGEST Returns the parameters of an exponential trend
LOGINV Returns the inverse of the lognormal distribution
LOGNORMDIST Returns the cumulative lognormal distribution
MAX Returns the maximum value in a list of arguments
MAXA Returns the largest value in a list of arguments
MEDIAN Returns the median of the given numbers
MIN Returns the minimum value in a list of arguments
MINA Returns the smallest value in the list of arguments
MODE Returns the most common value in a data set
NEGBINOMDIST Returns the negative binomial distribution
NORMDIST Returns the normal cumulative distribution
NORMINV Returns the inverse of the normal cumulative distribution
NORMSDIST Returns the standard normal cumulative distribution
NORMSINV Returns the inverse of the standard normal cumulative distribution
PEARSON Returns the Pearson product moment correlation coefficient
PERCENTILE Returns the k-th percentile of values in a range
PERCENTRANK Returns the percentage rank of a value in a data set
PERMUT Returns the number of permutations for a given number of objects
POISSON Returns the Poisson distribution
PROB Returns the probability that values in a range are between two limits
QUARTILE Returns the quartile of a data set
RANK Returns the rank of a number in a list of numbers
RSQ Returns the square of the Pearson product moment correlation coefficient
SKEW Returns the skewness of a distribution
SLOPE Returns the slope of the linear regression line
SMALL Returns the k-th smallest value in a data set
STANDARDIZE Returns a normalized value
STDEV Estimates standard deviation based on a sample
STDEVA Estimates standard deviation based on a sample
STDEVP Calculates standard deviation based on the entire population given as arguments
STDEVPA Calculates standard deviation based on the entire population given as arguments, including text and logical values
STEYX Returns the standard error of the predicted y-value for each x in the regression
TDIST Returns the Student's t-distribution
TINV Returns the inverse of the Student's t-distribution
TREND Returns values along a linear trend
TRIMMEAN Returns the mean of the interior of a data set
TTEST Returns the probability associated with a Student's t-test
VAR Estimates variance based on a sample
VARA Estimates variance based on a sample
VARP Calculates variance based on the entire population
VARPA Calculates variance based on the entire population
WEIBULL Returns the Weibull distribution
ZTEST Returns the two-tailed P-value of a z-test
Text functions Text functions
BAHTTEXT Converts a number to Thai text and adds a suffix of "Baht"
CHAR Returns the character specified by the code number
CLEAN Removes all nonprintable characters from text
CODE Returns a numeric code for the first character in a text string
CONCATENATE Joins several text items into one text item
DOLLAR Converts a number to text
EXACT Checks to see if two text values are identical
FIND Finds one text value within another (case-sensitive)
FIXED Formats a number as text with a fixed number of decimals
LEFT Returns the leftmost characters from a text value
LEN Returns the number of characters in a text string
LOWER Converts text to lowercase
MID Returns a specific number of characters from a text string starting at the position you specify
PROPER Capitalizes the first letter in each word of a text value
REPLACE Replaces characters within text
REPT Repeats text a given number of times
RIGHT Returns the rightmost characters from a text value
SEARCH Finds one text value within another (not case-sensitive)
SUBSTITUTE Substitutes new text for old text in a text string
T Converts its arguments to text
TEXT Formats a number and converts it to text
TRIM Removes spaces from text
UPPER Converts text to uppercase
VALUE Converts a text argument to a number
Date and Time functions Date and Time functions
DATE Returns the serial number of a particular date
DATEDIF Returns the number of days, months, or years between two dates
DATEVALUE Converts a date in the form of text to a serial number
DAY Converts a serial number to a day of the month
DAYS360 Calculates the number of days between two dates based on a 360-day year
EDATE Returns the serial number that represents the date that is the indicated number of months before or after a specified date
EOMONTH Returns the serial number date for the last day of the month that is the indicated number of months before or after start_date
HOUR Converts a serial number to an hour
MINUTE Converts a serial number to a minute
MONTH Converts a serial number to a month
NETWORKDAYS Returns the number of whole working days between start_date and end_date
NOW Returns the serial number of the current date and time
SECOND Converts a serial number to a second
TIME Returns the serial number of a particular time
TIMEVALUE Converts a time in the form of text to a serial number
TODAY Returns the serial number of today's date
WEEKDAY Converts a serial number to a day of the week
WEEKNUM Returns a number that indicates where the week falls numerically within a year
WORKDAY Returns a number that represents a date that is the indicated number of working days before or after start_date
YEAR Converts a serial number to a year
YEARFRAC Calculates the fraction of the year represented by the number of whole days between two dates
Engineering functions Engineering functions
BESSELI Returns the serial number of a particular date
BESSELJ Returns the serial number of a particular date
BESSELK Returns the serial number of a particular date
BESSELY Returns the serial number of a particular date
BIN2DEC Returns the serial number of a particular date
BIN2HEX Returns the serial number of a particular date
BIN2OCT Returns the serial number of a particular date
COMPLEX Returns the serial number of a particular date
CONVERT Returns the serial number of a particular date
DEC2BIN Returns the serial number of a particular date
DEC2HEX Returns the serial number of a particular date
DEC2OCT Returns the serial number of a particular date
DELTA Returns the serial number of a particular date
ERF Returns the serial number of a particular date
ERFC Returns the serial number of a particular date
GESTEP Returns the serial number of a particular date
HEX2BIN Returns the serial number of a particular date
HEX2DEC Returns the serial number of a particular date
HEX2OCT Returns the serial number of a particular date
IMABS Returns the serial number of a particular date
IMAGINARY Returns the serial number of a particular date
IMARGUMENT Returns the serial number of a particular date
IMCONJUGATE Returns the serial number of a particular date
IMCOS Returns the serial number of a particular date
IMDIV Returns the serial number of a particular date
IMEXP Returns the serial number of a particular date
IMLN Returns the serial number of a particular date
IMLOG10 Returns the serial number of a particular date
IMLOG2 Returns the serial number of a particular date
IMPOWER Returns the serial number of a particular date
IMPRODUCT Returns the serial number of a particular date
IMREAL Returns the serial number of a particular date
IMSIN Returns the serial number of a particular date
IMSQRT Returns the serial number of a particular date
IMSUB Returns the serial number of a particular date
IMSUM Returns the serial number of a particular date
OCT2BIN Returns the serial number of a particular date
OCT2DEC Returns the serial number of a particular date
OCT2HEX Returns the serial number of a particular date
References
Absolute and relative cell references       i.e. $A$1, A1
Absolute and relative 3D cell references       i.e. Sheet1!$A$1, Sheet1!A1
Absolute and relative range references       i.e. $A$1:$A$5, A1:A5
Absolute and relative 3D range references       i.e. Sheet1!$A$1:$A$5, Sheet1!A1:A5
Names       Note: To add names to a worksheet use ExcelWorksheet.easy_addName methods (See Names)
References in A1 or R1C1 style       Exponentiationi.e. $A$1, R[1]C[1]Note: To specify the R1C1 reference style use ExcelOptions.setR1C1ReferenceStyle method
Arrays and array formulas Arrays and array formulas
Numbers, booleans, strings and errors Numbers, booleans, strings and errors

Concept in action

The below example shows how to export an Excel file with formulas.

The source code samples can be integrated in:
- ASP.NET web pages
- Windows applications
- Windows Forms (WinForms)
- Console applications
- Windows service applications
- ASP.NET MVC web applications
- PHP and ASP web pages
- Java applications

Source code sample

C#.NET
VB.NET
C++.NET
J#.NET
Java
PHP
ASP
C++
VB6
VBS
Coldfusion

The screen shot below represents the exported Excel file with formulas generated by the code sample above. The formula represents the sum of the cell values from A1 to A4. The result of the formula is displayed in cell A6.

Exported Excel file with formulas

Export formulas to Excel file

EasyXLS allows you to export formulas to Excel file. The above code sample shows how to achieve this goal.

The formula is a cell value that must start with the "=" sign (i.e. =A1+C2).

Import formula from Excel file

EasyXLS allows you to import formula from Excel file. The library permits reading Excel formula expression or the formula value.

The formula expression can be read after the Excel file is imported using ExcelCell.getValue method.

The formula value can be read using various methods that allows importing the Excel file to DataSet, ResultSet or List.

Calculate formula and read formula result

EasyXLS library allows Excel calculation for sheets and reading the formula result after computation.

Hidden formulas

EasyXLS allows you to hide the Excel formulas expressions using ExcelStyle.setHiddenFormula, ExcelCell.setHiddenFormula, ExcelColumn.setHiddenFormula and ExcelRow.setHiddenFormula methods. The sheet that contains the formula must be protected.

Array formulas

EasyXLS supports also array formulas that can be added to Excel file using ExcelWorksheet.easy_addArrayFormula method.

Getting started with EasyXLS Excel library

To download the trial version of EasyXLS Excel Library, press the below button:

Download EasyXLS™ Excel Library for .NET and Java

If you already own a license key, you may login and download EasyXLS from your account.



Available for: Professional, Excel Writer, Excel Reader, Excel Express Writer (partial)
Go to top

EasyXLS Excel libraries:

.NET
.NET Excel Library
full .NET version to import, export or convert Excel files
.NET Excel Writer
.NET version to create and export Excel files
.NET Excel Reader
.NET version to read and import Excel files
-
Java
Java Excel Library
full Java version to import, export or convert Excel files
Java Excel Writer
Java version to create and export Excel files
Java Excel Reader
Java version to read and import Excel files
Download EasyXLS™ Excel Library for .NET and Java

File formats:

MS Excel 97 - 2003
MS Excel 2007 - 2010
MS Excel 2013
MS Excel 2016
MS Excel 2019
XLSX XLSM XLSB XLS
XML HTML CSV TXT