ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / Developing an application or website / Controls, windows and pages / Controls: Available types / Spreadsheet control
  • Overview
  • Formulas available in standard
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Overview
At runtime, in the Spreadsheet control, the end user directly enters the calculation formulas. The cells used can be selected with the mouse.
The Spreadsheet control proposes the main calculation functions and operators: sum, subtraction, multiplication, division, percentages, mean, min-max, condition (IF), calculation and comparison operators, ...
Several WLanguage functions can be used in the formulas such as the mathematical functions, string functions, date functions, financial functions, ...
In the formulas, you have the ability to use the WLanguage procedures that have been created for this purpose in the application (SpreadsheetAllowProcedure). For example, the application can propose the "Shipping cost" procedure that calculates the amount according to advanced parameters: weight, volume, speed, destination country, ...
This procedure can even be used in the spreadsheet!
Formulas available in standard
The available functions are as follows:
Compatibility functions
COVARReturns the covariance, mean deviation of products for each series of observations.
MODEReturns the most common value of a data series.
STDEVEvaluates the standard deviation of a population according to a sample of this population.
VARCalculates the variance according to a sample.
Date and time functions
DATEReturns the series number for a specific date.
DATEVALUEConverts a date represented in text format into serial number.
DAYConverts a series number into a day of the month.
DAYS360Returns the number of days between two dates according to a 360-day year.
EDATEReturns the series number of the date that represents a specific date (start_date argument), corrected plus or minus the specified number of months.
EOMONTHReturns the series number of the last day of the month before or after a specified number of months from start_date.
HOURConverts a series number into hour (included between 1 and 12).
MINUTEConverts a series number into minute (included between 0 and 59).
MONTHConverts a series number into month (included between 1 and 12).
NETWORKDAYSReturns the number of whole workdays included between two dates.
NETWORKDAYS.INTLReturns the number of whole workdays between two dates via parameters identifying the weekend days and their number.
NOWReturns the series number of today's date and time.
SECONDCoverts a series number into second (between 0 and 59).
TIMEReturns the series number of a specific time.
TIMEVALUEConverts a date represented in text format into serial number.
TODAYReturns the series number of today's date.
WEEKDAYConverts a series number into a day of the week (included between 0 and 7).
WEEK.NUMConverts a series number into a number representing the week number in the year.
WORKDAYReturns the series number of the date before or after the specified number of days.
WORKDAY.INTLReturns the series number of the date before or after the specified number of days by specifying the parameters that identify and count the days included in the weekend.
YEARConverts a series number into year.
YEARFRACReturns the fraction of the year representing the number of whole days separating the start date from the end date.
Engineering functions
CONVERTConverts a number from a measurement unit to another one.
DEC2BINConverts a decimal number into binary number.
DEC2HEXConverts a decimal number into hexadecimal number.
GESTEPChecks whether a number is greater than a threshold value.
HEX2BINConverts an hexadecimal number into binary number.
HEX2DECConverts an hexadecimal number into decimal number.
HEX2OCTConverts an hexadecimal number into octal number.
OCT2BINConverts an octal number into binary number.
OCT2DECConverts an octal number into decimal number.
OCT2HEXConverts an octal number into hexadecimal number.
Information functions
CELLReturns information about the layout, the location and the content of a cell. NOTE This function is not available in Excel Web App.
ISBLANKReturns TRUE if the value argument is empty.
ISERRReturns TRUE if the value argument refers to an error value, except #N/A.
ISERRORReturns TRUE if the value argument refers to an error value.
ISEVENReturns TRUE if the digit is even.
ISLOGICALReturns TRUE if the value argument refers to a logical value.
ISNAReturns TRUE if the value argument refers to the #N/A error value.
ISNONTEXTReturns TRUE if the value argument is not presented in text format.
ISNUMBERReturns TRUE if the value argument represents a number.
ISODDReturns TRUE if the digit is odd.
ISREFReturns TRUE if the value argument is a reference.
ISTEXTReturns TRUE if the value argument is presented in text format.
NReturns a value converted into number.
NAReturns the #N/A error value.
TYPEReturns a number indicating the data type of a value.
Logical functions
ANDReturns TRUE if all its arguments are TRUE.
FALSEReturns the logical value FALSE.
IFSpecifies a logical test to run.
IFERRORReturns a specific value if a formula generates an error ; otherwise, returns the result of the formula.
NOT
ORReturns TRUE if one of the arguments if TRUE.
TRUEReturns the logical value TRUE.
Search and reference functions
ADDRESSReturns a reference in text format to a single cell of a worksheet.
AREASReturns the number of areas in a reference.
CHOOSEChooses a value in a list.
COLUMNReturns the column number of a reference.
COLUMNSReturns the number of columns in a reference.
HLOOKUPPerforms a search in the first row of a matrix and returns the value of the specified cell.
INDIRECTReturns a reference indicated by a text value.
LOOKUPSearches for values in a vector or in a matrix.
MATCHSearches for values in a reference or in a matrix.
OFFSETReturns a reference shifted in relation to a given reference.
ROWReturns the row number of a reference.
ROWSReturns the number of rows in a reference.
TRANSPOSEReturns the transposition of a matrix.
VLOOKUPPerforms a search in the first column of a matrix and moves on the row to return the value of a cell.
Mathematical and trigonometric functions
ABSReturns the absolute value of a number.
ACOSReturns the arc cosine of a number.
ACOSHReturns the reverse hyperbolic cosine of a number.
AGGREGATEReturns an aggregate in a list or database.
ASINReturns the arc sine of a number.
ASINHReturns the reverse hyperbolic sine of a number.
ATANReturns the arc tangent of a number.
ATAN2Returns the arc tangent of the x and y coordinates.
ATANHReturns the reverse hyperbolic tangent of a number.
CEILINGRounds a number to the nearest integer number or to the nearest multiple of the precision argument by going away from zero.
CEILING.PRECISERounds a number to the nearest integer number or to the nearest multiple of the precision argument by going away from zero. Regardless of its sign, this number is rounded to the greater integer.
COMBINReturns the number of combinations that can be obtained from a given number of objects.
COSReturns the cosine of a number.
COSHReturns the hyperbolic cosine of a number.
DEGREESConverts radians into degrees.
EVENRounds a number to the nearest even integer number by going away from zero.
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 by tending towards 0 (zero).
FLOOR.PRECISERounds a number to the nearest integer number or to the nearest multiple of the precision argument by tending towards zero. Regardless of its sign, this number is rounded to the lower integer.
GCDReturns the greatest common divisor.
INTRounds a number of the immediately lower integer.
ISO.CEILINGReturns a number rounded to the nearest integer or to the nearest multiple of the precision argument by going away from zero.
LCMReturns the lowest common multiple.
LNReturns the Napierian logarithm of a number.
LOGReturns the logarithm of a number in the specified base.
LOG10Calculates the logarithm of a number in base 10.
MDETERMReturns the determinant of a matrix.
MINVERSEReturns the reverse matrix of a matrix.
MMULTReturns the product of two matrices.
MODReturns the remainder of a division.
MROUNDGives the rounding of a number to the specified multiple.
ODDReturns the number, rounded to the value of the nearest odd integer number by going away from zero.
PIReturns the value of Pi.
POWERReturns the value of the number raised to a power.
PRODUCTMultiplies its arguments.
QUOTIENTReturns the integer part of the division result.
RADIANSConverts degrees into radians.
RANDReturns a random number included between 0 and 1.
RANDBETWEENReturns a random number between the specified numbers.
ROUNDRounds a number to the specified number of digits.
ROUNDDOWNRounds a number by tending towards 0 (zero).
ROUNDUPRounds a number to the greater integer, by going away from zero.
SIGNReturns the sign of a number.
SINReturns the sine of a given angle.
SINHReturns hyperbolic sine of a number.
SQRTReturns the square root of a number.
SQRTPIReturns the square root of (number * pi).
SUMCalculates the sum of its arguments.
SUMIFAdds the specified cells if they match a given criterion.
SUMIFSAdds the cells of a range that match several criteria.
SUMPRODUCTMultiplies the corresponding values of specified matrices and calculate the sum of these products.
SUMSQReturns the sum of squared arguments.
TANReturns the tangent of a number.
TANHReturns the hyperbolic tangent of a number.
TRUNCReturns the integer part of a number.
Statistical functions
AVEDEVReturns the average deviation observed in the mean of data points.
AVERAGEReturns the mean of its arguments.
AVERAGEAReturns the mean of its arguments, including numbers, text and logical values.
AVERAGEIFReturns the arithmetic mean of all cells found in a range that mach the given criteria.
COUNTDefines the numbers included in the list of arguments.
COUNTADefines the number of values included in the list of arguments.
COUNTBLANKCounts the number of empty cells in a range.
COUNTIFCounts the number of cells that match a given criterion in a range.
COUNTIFSCounts the number of cells inside a range that match several criteria.
MAXReturns the maximum value found in a list of arguments.
MAXAReturns the maximum value found in a list of arguments, including numbers, text and logical values.
MEDIANReturns the median value of given numbers.
MINReturns the minimum value found in a list of arguments.
MINAReturns the minimum value found in a list of arguments, including numbers, text and logical values.
Text functions
ASCChanges the English characters coded on two bytes found inside a character string into characters coded on one byte.
CHARReturns the character specified by the numeric code.
CLEANDeletes all the control characters from the text.
CODEReturns the code number of the first character found in the text.
CONCATENATEConcatenates several textual elements in order to get a single one.
DOLLARConverts a number into text by using the currency format $ (Dollar).
EXACTChecks whether two text values are identical.
FIXEDConverts a number to text format with the specified number of decimals.
LEFT, LEFTBReturns the characters found at the left extremity of a character string.
LEN, LENBReturns the number of characters found in a text string.
LOWERConverts the text into lowercase characters.
PROPERWrites the first letter of each word in uppercase in a textual string.
REPLACE, REPLACEBReplaces characters in a text.
REPTRepeats a text a given number of times.
RIGHT, RIGHTBReturns the characters found at the right extremity of a character string.
SEARCH, SEARCHBFinds a text in another text (while ignoring the case).
SUBSTITUTEReplaces the former text of a character string by a new one.
TConverts its arguments into text.
TEXTConverts a number to text format.
TRIMDeletes the space characters from the text.
UPPERConverts the text into uppercase characters.
VALUEConverts a textual argument into number.
Minimum version required
  • Version 20
Comments
Click [Add] to post a comment

Last update: 05/26/2022

Send a report | Local help