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
ACCRINTReturns the accrued interest for a security that pays periodic interest
ACCRINTMReturns the accrued interest for a security that pays interest at maturity
AMORDEGRCReturns the depreciation for each accounting period
AMORLINCReturns the depreciation for each accounting period
COUPDAYBSReturns the number of days from the beginning of the coupon period to the settlement date
COUPDAYSReturns the number of days in the coupon period that contains the settlement date
COUPDAYSNCReturns the number of days from the beginning of the coupon period to the settlement date
COUPNCDReturns a number that represents the next coupon date after the settlement date
COUPNUMReturns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon
COUPPCDReturns a number that represents the previous coupon date before the settlement date
CUMIPMTReturns the cumulative interest paid on a loan between start_period and end_period
CUMPRINCReturns the cumulative principal paid on a loan between start_period and end_period
DBReturns the depreciation of an asset for a specified period using the fixed-declining balance method
DDBReturns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify
DISCReturns the discount rate for a security
DOLLARDEConverts a dollar price expressed as a fraction into a dollar price expressed as a decimal number
DOLLARFRConverts a dollar price expressed as a decimal number into a dollar price expressed as a fraction
DURATIONReturns the Macauley duration for an assumed par value of $100
EFFECTReturns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year
FVReturns the future value of an investment
FVSCHEDULEReturns the future value of an initial principal after applying a series of compound interest rates
INTRATEReturns the interest rate for a fully invested security
IPMTReturns the interest payment for an investment for a given period
IRRReturns the internal rate of return for a series of cash flows
ISPMTCalculates the interest paid during a specific period of an investment
MDURATIONReturns the modified duration for a security with an assumed par value of $100
MIRRReturns the internal rate of return where positive and negative cash flows are financed at different rates
NOMINALReturns the nominal annual interest rate, given the effective rate and the number of compounding periods per year
NPERReturns the number of periods for an investment
NPVReturns the net present value of an investment based on a series of periodic cash flows and a discount rate
ODDFPRICEReturns the price per $100 face value of a security having an odd (short or long) first period
ODDFYIELDReturns the yield of a security that has an odd (short or long) first period
ODDLPRICEReturns the price per $100 face value of a security having an odd (short or long) last coupon period
ODDLYIELDReturns the yield of a security that has an odd (short or long) last period
PMTReturns the periodic payment for an annuity
PPMTReturns the payment on the principal for an investment for a given period
PRICEReturns the price per $100 face value of a security that pays periodic interest
PRICEDISCReturns the price per $100 face value of a discounted security
PRICEMATReturns the price per $100 face value of a security that pays interest at maturity
PVReturns the present value of an investment
RATEReturns the interest rate per period of an annuity
RECEIVEDReturns the amount received at maturity for a fully invested security
SLNReturns the straight-line depreciation of an asset for one period
SYDReturns the sum-of-years' digits depreciation of an asset for a specified period
TBILLEQReturns the bond-equivalent yield for a treasury bill
TBILLPRICEReturns the price per $100 face value for a treasury bill
TBILLYIELDReturns the yield for a treasury bill
VDBReturns the depreciation of an asset for a specified or partial period using a declining balance method
XIRRReturns the internal rate of return for a schedule of cash flows that is not necessarily periodic
XNPVReturns the net present value for a schedule of cash flows that is not necessarily periodic
YIELDReturns the yield on a security that pays periodic interest
YIELDDISCReturns the annual yield for a discounted security
YIELDMATReturns the annual yield of a security that pays interest at maturity
Database and List Management functions Database and List Management functions
DAVERAGEReturns the average of selected database entries
DCOUNTCounts the cells that contain numbers in a database
DCOUNTACounts nonblank cells in a database
DGETExtracts from a database a single record that matches the specified criteria
DMAXReturns the maximum value from selected database entries
DMINReturns the minimum value from selected database entries
DPRODUCTMultiplies the values in a particular field of records that match the criteria in a database
DSTDEVEstimates the standard deviation based on a sample of selected database entries
DSTDEVPCalculates the standard deviation based on the entire population of selected database entries
DSUMAdds the numbers in the field column of records in the database that match the criteria
DVAREstimates variance based on a sample from selected database entries
DVARPCalculates variance based on the entire population of selected database entries
Information functions Information functions
CELLReturns information about the formatting
ERROR.TYPEReturns a number corresponding to an error type
INFOReturns information about the current operating environment
ISBLANKReturns TRUE if the value is blank
ISERRReturns TRUE if the value is any error value except #N/A
ISERRORReturns TRUE if the value is any error value
ISEVENReturns TRUE if number is even, or FALSE if number is odd
ISLOGICALReturns TRUE if the value is a logical value
ISNAReturns TRUE if the value is the #N/A error value
ISNONTEXTReturns TRUE if the value is not text
ISNUMBERReturns TRUE if the value is a number
ISODDReturns TRUE if number is odd, or FALSE if number is even
ISREFReturns TRUE if the value is a reference
ISTEXTReturns TRUE if the value is text
NReturns a value converted to a number
NAReturns the error value #N/A
TYPEReturns a number indicating the data type of a value
Logical functions Logical functions
ANDReturns TRUE if all its arguments are TRUE
FALSEReturns the logical value FALSE
IFSpecifies a logical test to perform
IFERRORReturns the future value of an initial principal after applying a series of compound interest rates
NOTReverses the logic of its argument
ORReturns TRUE if any argument is TRUE
TRUEReturns the logical value TRUE
Lookup and Reference functions Lookup and Reference functions
ADDRESSReturns a reference as text to a single cell in a worksheet
AREASReturns the number of areas in a reference
CHOOSEChooses a value from a list of values
COLUMNReturns the column number of a reference
COLUMNSReturns the number of columns in a reference
HLOOKUPLooks in the top row of an array and returns the value of the indicated cell
HYPERLINKCreates a shortcut or jump that opens a document stored on a network server
INDEXUses an index to choose a value from a reference or array
INDIRECTReturns a reference indicated by a text value
LOOKUPLooks up values in a vector or array
MATCHLooks up values in a reference or array
OFFSETReturns a reference offset from a given reference
ROWReturns the row number of a reference
ROWSReturns the number of rows in a reference
RTDRetrieves real-time data from a program that supports COM automation
TRANSPOSEReturns the transpose of an array
VLOOKUPLooks 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
ABSReturns the absolute value of a number
ACOSReturns the arccosine of a number
ACOSHReturns the inverse hyperbolic cosine of a number
ASINReturns the arcsine of a number
ASINHReturns the inverse hyperbolic sine of a number
ATANReturns the arctangent of a number
ATAN2Returns the arctangent from x- and y- coordinates
ATANHReturns the inverse hyperbolic tangent of a number
CEILINGRounds a number to the nearest integer or to the nearest multiple of significance
COMBINReturns the number of combinations for a given number of objects
COSReturns the cosine of a number
COSHReturns the hyperbolic cosine of a number
DEGREESConverts radians to degrees
EVENRounds a number up to the nearest even integer
EXPReturns e raised to the power of a given number
FACTReturns the factorial of a number
FACTDOUBLEReturns the double factorial of a number
FLOORRounds a number down
GCDReturns the greatest common divisor of two or more integers
INTRounds a number down to the nearest integer
LCMReturns the least common multiple of integers
LNReturns the natural logarithm of a number
LOGReturns the logarithm of a number to a specified base
LOG10Returns the base-10 logarithm of a number
MDETERMReturns the matrix determinant of an array
MINVERSEReturns the matrix inverse of an array
MMULTReturns the matrix product of two arrays
MODReturns the remainder from division
MROUNDReturns a number rounded to the desired multiple
MULTINOMIALReturns the ratio of the factorial of a sum of values to the product of factorials
ODDRounds a number up to the nearest odd integer
PIReturns the value of Pi
POWERReturns the result of a number raised to a power
PRODUCTMultiplies its arguments
QUOTIENTReturns the integer portion of a division
RADIANSConverts degrees to radians
RANDReturns a random number between 0 and 1
RANDBETWEENReturns a random number between the numbers you specify
ROMANConverts an arabic numeral to roman
ROUNDRounds a number to a specified number of digits
ROUNDDOWNRounds a number down
ROUNDUPRounds a number up
SERIESSUMReturns the sum of a power series based on a formula
SIGNReturns the sign of a number
SINReturns the sine of the given angle
SINHReturns the hyperbolic sine of a number
SQRTReturns a positive square root
SQRTPIReturns the square root of (number * p)
SUBTOTALReturns a subtotal in a list or database
SUMAdds its arguments
SUMIFAdds the cells specified by a given criteria
SUMIFSAdds the cells in a range that meet multiple criteria
SUMPRODUCTReturns the sum of the products of corresponding array components
SUMSQReturns the sum of the squares of the arguments
SUMX2MY2Returns the sum of the difference of squares of corresponding values in two arrays
SUMX2PY2Returns the sum of the sum of squares of corresponding values in two arrays
SUMXMY2Returns the sum of squares of differences of corresponding values in two arrays
TANReturns the tangent of a number
TANHReturns the hyperbolic tangent of a number
TRUNCTruncates a number to an integer
Statistical functions Statistical functions
AVEDEVReturns the average of the absolute deviations of data points from their mean
AVERAGEReturns the average of its arguments
AVERAGEAReturns the average (arithmetic mean) of the arguments
AVERAGEIFReturns the average (arithmetic mean) of all the cells in a range that meet a given criteria
AVERAGEIFSReturns the average (arithmetic mean) of all cells that meet multiple criteria
BETADISTReturns the cumulative beta probability density function
BETAINVReturns the inverse of the cumulative beta probability density function
BINOMDISTReturns the individual term binomial distribution probability
CHIDISTReturns the one-tailed probability of the chi-squared distribution
CHIINVReturns the inverse of the one-tailed probability of the chi-squared distribution
CHITESTReturns the test for independence
CONFIDENCEReturns the confidence interval for a population mean
CORRELReturns the correlation coefficient between two data sets
COUNTCounts how many numbers are in the list of arguments
COUNTACounts how many values are in the list of arguments
COUNTBLANKCounts the number of blank cells within a range
COUNTIFCounts the number of nonblank cells within a range that meet the given criteria
COUNTIFSCounts the number of cells within a range that meet multiple criteria
COVARReturns covariance
CRITBINOMReturns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
DEVSQReturns the sum of squares of deviations
EXPONDISTReturns the exponential distribution
FDISTReturns the F probability distribution
FINVReturns the inverse of the F probability distribution
FISHERReturns the Fisher transformation
FISHERINVReturns the inverse of the Fisher transformation
FORECASTReturns a value along a linear trend
FREQUENCYReturns a frequency distribution as a vertical array
FTESTReturns the result of an F-test
GAMMADISTReturns the gamma distribution
GAMMAINVReturns the inverse of the gamma cumulative distribution
GAMMALNReturns the natural logarithm of the gamma function
GEOMEANReturns the geometric mean
GROWTHReturns values along an exponential trend
HARMEANReturns the harmonic mean
HYPGEOMDISTReturns the hypergeometric distribution
INTERCEPTReturns the intercept of the linear regression line
KURTReturns the kurtosis of a data set
LARGEReturns the k-th largest value in a data set
LINESTReturns the parameters of a linear trend
LOGESTReturns the parameters of an exponential trend
LOGINVReturns the inverse of the lognormal distribution
LOGNORMDISTReturns the cumulative lognormal distribution
MAXReturns the maximum value in a list of arguments
MAXAReturns the largest value in a list of arguments
MEDIANReturns the median of the given numbers
MINReturns the minimum value in a list of arguments
MINAReturns the smallest value in the list of arguments
MODEReturns the most common value in a data set
NEGBINOMDISTReturns the negative binomial distribution
NORMDISTReturns the normal cumulative distribution
NORMINVReturns the inverse of the normal cumulative distribution
NORMSDISTReturns the standard normal cumulative distribution
NORMSINVReturns the inverse of the standard normal cumulative distribution
PEARSONReturns the Pearson product moment correlation coefficient
PERCENTILEReturns the k-th percentile of values in a range
PERCENTRANKReturns the percentage rank of a value in a data set
PERMUTReturns the number of permutations for a given number of objects
POISSONReturns the Poisson distribution
PROBReturns the probability that values in a range are between two limits
QUARTILEReturns the quartile of a data set
RANKReturns the rank of a number in a list of numbers
RSQReturns the square of the Pearson product moment correlation coefficient
SKEWReturns the skewness of a distribution
SLOPEReturns the slope of the linear regression line
SMALLReturns the k-th smallest value in a data set
STANDARDIZEReturns a normalized value
STDEVEstimates standard deviation based on a sample
STDEVAEstimates standard deviation based on a sample
STDEVPCalculates standard deviation based on the entire population given as arguments
STDEVPACalculates standard deviation based on the entire population given as arguments, including text and logical values
STEYXReturns the standard error of the predicted y-value for each x in the regression
TDISTReturns the Student's t-distribution
TINVReturns the inverse of the Student's t-distribution
TRENDReturns values along a linear trend
TRIMMEANReturns the mean of the interior of a data set
TTESTReturns the probability associated with a Student's t-test
VAREstimates variance based on a sample
VARAEstimates variance based on a sample
VARPCalculates variance based on the entire population
VARPACalculates variance based on the entire population
WEIBULLReturns the Weibull distribution
ZTESTReturns the two-tailed P-value of a z-test
Text functions Text functions
BAHTTEXTConverts a number to Thai text and adds a suffix of "Baht"
CHARReturns the character specified by the code number
CLEANRemoves all nonprintable characters from text
CODEReturns a numeric code for the first character in a text string
CONCATENATEJoins several text items into one text item
DOLLARConverts a number to text
EXACTChecks to see if two text values are identical
FINDFinds one text value within another (case-sensitive)
FIXEDFormats a number as text with a fixed number of decimals
LEFTReturns the leftmost characters from a text value
LENReturns the number of characters in a text string
LOWERConverts text to lowercase
MIDReturns a specific number of characters from a text string starting at the position you specify
PROPERCapitalizes the first letter in each word of a text value
REPLACEReplaces characters within text
REPTRepeats text a given number of times
RIGHTReturns the rightmost characters from a text value
SEARCHFinds one text value within another (not case-sensitive)
SUBSTITUTESubstitutes new text for old text in a text string
TConverts its arguments to text
TEXTFormats a number and converts it to text
TRIMRemoves spaces from text
UPPERConverts text to uppercase
VALUEConverts a text argument to a number
Date and Time functions Date and Time functions
DATEReturns the serial number of a particular date
DATEDIFReturns the number of days, months, or years between two dates
DATEVALUEConverts a date in the form of text to a serial number
DAYConverts a serial number to a day of the month
DAYS360Calculates the number of days between two dates based on a 360-day year
EDATEReturns the serial number that represents the date that is the indicated number of months before or after a specified date
EOMONTHReturns the serial number date for the last day of the month that is the indicated number of months before or after start_date
HOURConverts a serial number to an hour
MINUTEConverts a serial number to a minute
MONTHConverts a serial number to a month
NETWORKDAYSReturns the number of whole working days between start_date and end_date
NOWReturns the serial number of the current date and time
SECONDConverts a serial number to a second
TIMEReturns the serial number of a particular time
TIMEVALUEConverts a time in the form of text to a serial number
TODAYReturns the serial number of today's date
WEEKDAYConverts a serial number to a day of the week
WEEKNUMReturns a number that indicates where the week falls numerically within a year
WORKDAYReturns a number that represents a date that is the indicated number of working days before or after start_date
YEARConverts a serial number to a year
YEARFRACCalculates the fraction of the year represented by the number of whole days between two dates
Engineering functions Engineering functions
BESSELIReturns the serial number of a particular date
BESSELJReturns the serial number of a particular date
BESSELKReturns the serial number of a particular date
BESSELYReturns the serial number of a particular date
BIN2DECReturns the serial number of a particular date
BIN2HEXReturns the serial number of a particular date
BIN2OCTReturns the serial number of a particular date
COMPLEXReturns the serial number of a particular date
CONVERTReturns the serial number of a particular date
DEC2BINReturns the serial number of a particular date
DEC2HEXReturns the serial number of a particular date
DEC2OCTReturns the serial number of a particular date
DELTAReturns the serial number of a particular date
ERFReturns the serial number of a particular date
ERFCReturns the serial number of a particular date
GESTEPReturns the serial number of a particular date
HEX2BINReturns the serial number of a particular date
HEX2DECReturns the serial number of a particular date
HEX2OCTReturns the serial number of a particular date
IMABSReturns the serial number of a particular date
IMAGINARYReturns the serial number of a particular date
IMARGUMENTReturns the serial number of a particular date
IMCONJUGATEReturns the serial number of a particular date
IMCOSReturns the serial number of a particular date
IMDIVReturns the serial number of a particular date
IMEXPReturns the serial number of a particular date
IMLNReturns the serial number of a particular date
IMLOG10Returns the serial number of a particular date
IMLOG2Returns the serial number of a particular date
IMPOWERReturns the serial number of a particular date
IMPRODUCTReturns the serial number of a particular date
IMREALReturns the serial number of a particular date
IMSINReturns the serial number of a particular date
IMSQRTReturns the serial number of a particular date
IMSUBReturns the serial number of a particular date
IMSUMReturns the serial number of a particular date
OCT2BINReturns the serial number of a particular date
OCT2DECReturns the serial number of a particular date
OCT2HEXReturns the serial number of a particular date
References 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 formulasArrays and array formulas
Numbers, booleans, strings and errorsNumbers, 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
XLSXXLSMXLSBXLS
XMLHTMLCSVTXT