XLSX: Work with formulas on XLSX page

In This Topic

WebSpreadsheetEditorControlJS control allows to work (view, add, edit and delete) with formulas in cells of XLSX worksheet in web browser.

Formulas can be changed manually using mouse/keyboard or programmatically.

Add a formula to the focused cell of XLSX worksheet

If you want to add a formula to the focused cell of XLSX worksheet using mouse, you should do the following steps:

Click on the cell, where formula should be added
Select "Insert" tab in WebSpreadsheetDocumentEditorControlJS control
Click "Insert function" button, "Insert function" dialog will appear, select necessary formula from formula list, click "OK" button, the dialog will be closed and the formula text (for example, "=SUM()") will be added to the focused cell.

Here is JavaScript code that demonstrates how to add a formula to the focused cell of XLSX worksheet:

JavaScript

// _spreadsheetDocumentEditorControl is an instance of WebSpreadsheetDocumentEditorControlJS class

// get spreadsheet editor
var spreadsheetEditorControl = _spreadsheetDocumentEditorControl.get_SpreadsheetEditorControl();


var formula = "ABS(A1)+SUM(B2,B3)";
// add formula to the focused cell
spreadsheetEditorControl.insertFormulaInFocusedCell(formula);
// result formula value: "=ABS(A1)+SUM(B2,B3)"

Change formula of focused cell programmatically

Here is JavaScript code that demonstrates how to change a formula of focused cell of XLSX worksheet:

JavaScript

// _spreadsheetDocumentEditorControl is an instance of WebSpreadsheetDocumentEditorControlJS class

// get the spreadsheet editor
var spreadsheetEditorControl = _spreadsheetDocumentEditorControl.get_SpreadsheetEditorControl();


// get value or formula of focused cell
var cellValue = spreadsheetEditorControl.get_FocusedCellValue();

// set value or formula of focused cell
spreadsheetEditorControl.set_FocusedCellValue("0");

Edit the formula of the focused cell using mouse and keyboard

You can edit the formula of the focused cell using textbox in formula panel (external editor) or using textbox in cell region (internal editor).

If you want to create the formula that references another cell, you should do the following steps:

Start editing the cell value
Enter '=' symbol into the cell value
Click on the cell that should be referenced and the cell address will be added to the formula

If you want to create the formula that uses values from several cells, you should do the following steps:

Start editing the cell value
Enter '=' symbol into the cell value
Enter the formula name (for example "SUM") in the cell value
Enter symbol '(' into the cell value
Press Ctrl key and click on the cells, which values should be used in the formula, cells addresses will be added to the formula

If you want to see cells, which are used in the formula, you should do the following steps:

Start editing the cell value
Used cells will be highlighted using blue regions
Used cell will be highlighted using red region if the cell address is under text caret
Used cells will be highlighted using red region if the region of cell addresses is under text caret

Delete the formula of focused cell of XLSX worksheet

If you want to delete the formula of focused cell of XLSX worksheet using mouse, you should do the following steps:

Click on the cell, which formula should be deleted.
Click the right mouse button on the focused cell and select "Clear Contents" menu in context menu.

Here is JavaScript code that demonstrates how to delete the formula of the focused cell of XLSX worksheet:

JavaScript

// _spreadsheetDocumentEditorControl is an instance of WebSpreadsheetDocumentEditorControlJS class

// get spreadsheet editor
var spreadsheetEditorControl = _spreadsheetDocumentEditorControl.get_SpreadsheetEditorControl();

// clear contents (value and formula) of selected cells
spreadsheetEditorControl.clearCellsContent();

List of supported formulas

Here is the list of supported "Date and Time" formulas:

DATE - Computes the serial date-time for the given date.
DATEDIF - Calculates the number of days, months, or years between two dates.
DATEVALUE - Computes the serial date-time of the date represented by the string date-time-string, taking into account the current date system.
DAY - Computes the numeric day of the month in the Gregorian calendar for the date and/or time having the given date-value, taking into account the current date system.
DAYS360 - Computes the signed number of days between two dates based on a 360-day year (twelve 30-day months).
EDATE - Computes the serial date-time of the date that is month-offset months from the date specified by the date date-string, taking into account the current date system.
EOMONTH - Computes the serial date-time of the last day of the month for the date that is month-offset months from the date specified by the date start-date, taking into account the current date system.
HOUR - Computes the hour for the date and/or time having the given time-value.
MINUTE - Computes the minute for the date and/or time having the given time-value.
MONTH - Computes the numeric month in the Gregorian calendar for the date and/or time having the given date-value, taking into account the current date system.
NETWORKDAYS - Computes the number of whole working days between start-date and end-date.
NETWORKDAYS.INTL - Computes the numeric month in the Gregorian calendar for the date and/or time having the given date-value, taking into account the current date system.
NOW - Computes the serial date-time of the current date and time, taking into account the current date system.
SECOND - Computes the second for the date and/or time having the given time-value.
TIME - Computes the serial date-time for the given time.
TIMEVALUE - Computes the serial date-time of the time represented by the string date-time-string.
TODAY - Computes the serial date-time of the current date, taking into account the current date base system
WEEKDAY - Computes the weekday number for the date having the given serial-value, taking into account the current date system and weekday-start-flag, if present.
WEEKNUM - Computes the week number of the date corresponding to serial-value.
WORKDAY - Computes the serial date-time of the date that is day-offset working days offset from start-date.
WORKDAY.INTL - Computes the serial date-time of the date that is day-offset working days offset from start-date.
YEAR - Computes the numeric year in the Gregorian calendar for the date and/or time having the given date-value, taking into account the current date system.
YEARFRAC - Computes the fractional number of years represented by the number of whole days between two dates, start-date and end-date; according to basis.

Here is the list of supported "Engineering" formulas:

BIN2DEC - Makes the decimal equivalent of number.
BIN2HEX - Makes the uppercase hexadecimal equivalent of number, with the result having num-hex-digits digits.
BIN2OCT - Makes the octal equivalent of number, with the result having num-oct-digits digits.
DEC2BIN - Makes the binary equivalent of number, with the result having num-bin-digits digits.
DEC2HEX - Makes the hexadecimal equivalent of number, with the result having num-hex-digits digits.
DEC2OCT - Makes the octal equivalent of number, with the result having num-oct-digits digits.
DELTA - Compares two numbers for equality.
GESTEP - Tests if the value of number is greater than or equal to that of step.
HEX2BIN - Makes the binary equivalent of number, with the result having num-bin-digits digits.
HEX2DEC - Makes the decimal equivalent of number.
HEX2OCT - Makes the octal equivalent of number, with the result having num-oct-digits digits.
OCT2BIN - Makes the binary equivalent of number, with the result having num-bin-digits digits.
OCT2DEC - Makes the decimal equivalent of number
OCT2HEX - Makes the hexadecimal equivalent of number, with the result having num-hex-digits digits.

Here is the list of supported "Financial" formulas:

CUMIPMT - Computes the cumulative interest paid on a loan between start-period and end-period.
FV - Computes the future value of an investment based on periodic, constant payments and a constant interest rate.
IPMT - Computes the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
PMT - Computes the payment for a loan based on constant payments and a constant interest rate
PPMT - Computes the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.

Here is the list of supported "Information" formulas:

CELL - Retrieves information about the formatting, location, or contents of the upper-left cell indicated by reference.
ISBLANK - Determines if value refers to an empty cell.
ISERR - Determines if value is any of the error values other than #N/A.
ISERROR - Determines if value is any of the error values
ISEVEN - Determines if value is an even number or refers to a cell containing an even number.
ISLOGICAL - Determines if value contains a logical value or refers to a cell containing a logical value.
ISNA - Determines if value is the error value #N/A.
ISNUMBER - Determines if value contains a number or refers to a cell that contains a number.
ISODD - Determines if value is an odd number or refers to a cell containing an odd number.
NA - Gets the error value #N/A.

Here is the list of supported "Logical" formulas:

AND - Tests if all arguments in argument-list are TRUE. The function evaluates all arguments prior to returning a value.
FALSE - Computes the value FALSE.
IF - Tests logical-value, and if it is TRUE, value-if-true is evaluated and returned; otherwise, value-if-false is evaluated and returned.
IFERROR - Provides a simpler and more efficient way of trapping and handling errors.
NOT - Computes the logical negation of logical-value.
OR - Tests if any one or more arguments in argument-list are TRUE.
TRUE - Computes the value TRUE.

Here is the list of supported "Lookup and Reference" formulas:

ADDRESS - Creates a cell address, given the specified row and column numbers.
AREAS - Finds the number of areas designated by reference.
CHOOSE - Selects the argument in argument-list that corresponds by position to index.
COLUMN - Finds the number of the column(s) corresponding to reference.
COLUMNS - Finds the number of columns corresponding to array.
HYPERLINK - Creates a shortcut that opens a document stored on a network server, an intranet, or the Internet.
INDEX - Locates a value or the reference to a value from within a table or range.
INDIRECT - Locates the reference specified by ref-text and evaluates that reference to get to its underlying value.
LOOKUP - The vector form looks in a vector for a value, and returns a value from the same position in a second vector.
MATCH - Locates the relative position of an array item that matches a specified value in a specified order.
OFFSET - Gets a reference to a range that is a specified number of rows and columns from a cell or range of cells.
ROW - Finds the number of the row(s) corresponding to reference.
ROWS - Finds the number of rows corresponding to array.
VLOOKUP - Performs a vertical search for a value in the left-most column of a table or an array, noting the row in which the matching value is found.

Here is the list of supported "Math and Trigonometry" formulas:

ABS - Computes the absolute value of x.
ACOS - Computes the arc cosine of x.
ACOSH - Computes the inverse hyperbolic cosine of x.
ASIN - Computes the arc sine of x
ASINH - Computes the inverse hyperbolic sine of x.
ATAN - Computes the arc tangent of x.
ATAN2 - Computes the arc tangent of the coordinates x and y.
ATANH - Computes the inverse hyperbolic tangent of x
CEILING - Computes a value that is x rounded-up, away from zero, to the nearest multiple of significance.
COMBIN - Computes the possible number of groups of size number-chosen that can be formed from number objects.
COS - Computes the cosine of x.
COSH - Computes the hyperbolic cosine of x.
DEGREES - Converts angle in radians into degrees.
ECMA.CEILING - Computes a value that is x rounded-up, away from zero, to the nearest multiple of significance.
EVEN - Computes x rounded to the nearest even integer, away from zero.
EXP - Computes e^x, where the constant e is the base of the natural logarithm.
FACT - Computes the factorial of x.
FACTDOUBLE - Computes the double factorial of n.
FLOOR - Computes x rounded down, toward zero, to the nearest multiple of significance.
GCD - Computes the greatest common divisor of the one or more numbers, designated by arguments in argument-list.
INT - Computes x rounded down to an integer.
ISO.CEILING - Computes a value that is x rounded-up, to the nearest multiple of significance.
LCM - Computes a value that is x rounded-up, to the nearest multiple of significance.
LN - Computes the natural logarithm of x.
LOG - Computes the logarithm of x to the base base.
LOG10 - Computes the base-10 logarithm of x.
MDETERM - Computes the determinant of the square matrix of numbers designated by array
MINVERSE - Computes the inverse of the square matrix of numbers designated by array.
MMULT - Computes the product of the matrices of numbers designated by array-1 and array-2.
MOD - Computes the remainder when x is divided by y.
MROUND - Computes x rounded to multiple, away from zero.
MULTINOMIAL - Computes the ratio of the factorial of the sum of the values in argument-list to the product of the factorials.
ODD - Computes x rounded to the nearest odd integer, away from zero.
PI - Computes the value π.
POWER - Computes x raised to the power y.
PRODUCT - Multiplies the numeric values of arguments in argument-list.
QUOTIENT - Computes the integer portion of the division of dividend by divisor.
RADIANS - Converts angle in degrees into radians.
RAND - Computes an evenly distributed random real number greater than or equal to 0 and less than 1.
RANDBETWEEN - Computes a random integer number in the range lower-bound–upper-bound.
ROMAN - Converts the Arabic number, number, to a Roman number according to form.
ROUND - Rounds x to the number of digits specified by number-digits.
ROUNDDOWN - Computes x rounded down, toward zero, to the number of digits specified by number-digits.
ROUNDUP - Computes x rounded up, away from zero, to the number of digits specified by number-digits.
SERIESSUM - Computes the sum of a power series.
SIGN - Determines the sign of x.
SIN - Computes the sine of x.
SINH - Computes the hyperbolic sine of x.
SQRT - Computes the positive square root of x.
SQRTPI - Computes the positive square root of x × π.
SUBTOTAL - Computes a value using the function designated by function-number, using the arguments in argument-list.
SUM - Adds the numeric values of arguments in argument-list.
SUMIF - Applies selection criteria on the values in one range of cells and sums the values of the cells in a corresponding range.
SUMIFS - Adds the cells in a range that meet multiple criteria.
SUMPRODUCT - Multiplies the corresponding elements in the array arguments in argument-list, and returns the sum of those products.
SUMSQ - Adds the squares of arguments in argument-list.
SUMX2MY2 - Computes the sum of the difference of squares of the corresponding numerical elements in two arrays designated by array-1 and array-2.
SUMX2PY2 - Computes the sum of the sum of the squares of the corresponding numerical elements in two arrays designated by array-1 and array-2.
SUMXMY2 - Computes the sum of the squares of the difference between corresponding numerical elements in two arrays designated by array-1 and array-2.
TAN - Computes the tangent of x.
TANH - Computes the hyperbolic tangent of x.
TRUNC - Truncates x to the number of fractional digits by number-digits.

Here is the list of supported "Statistical" formulas:

AVERAGE - Computes the arithmetic mean of the numeric values of its arguments.
COUNT - Counts the number of arguments in argument-list that contain numbers, and the number of cells referred to by arguments in argument-list, which contain numbers.
COUNTA - Counts the number of arguments that are not cell references, and the number of cells, referred to by arguments, which are not empty.
COUNTBLANK - Counts the number of cells in a specified range of cells, which are empty.
COUNTIF - Counts the number of cells in a specified range of cells, whose values meet the specified criteria.
COUNTIFS - Counts the number of cells within a range that meet multiple criteria.
FREQUENCY - Calculates how often values occur within a range of values. A call to FREQUENCY shall be an array formula.
LARGE - Computes the k-th largest value in a data set.
MAX - Computes the largest of a set of numbers.
MIN - Computes the smallest of a set of numbers.
SMALL - Computes the k-th smallest value in a data set.
STDEV - Makes an estimate of the standard deviation based on a sample, using the "unbiased" or "n-1" method.
STDEVP - Computes the standard deviation of an entire population, using the "biased" or "n" method.
VAR - Makes an estimate of the variance based on a sample.
VARP - Computes the variance of an entire population.

Here is the list of supported "Text and Data" formulas:

BAHTTEXT - Produces a string containing number formatted according to the Thai convention.
CHAR - Determines the character that is represented by the value number.
CLEAN - Makes a string that is a copy of string with all so-called "non-printable" characters—those with internal values in the range U+0000–001F—removed.
CODE - Determines the numeric code of the first character in string.
CONCATENATE - Makes a string that is the concatenation of all the strings corresponding to the arguments in argument-list, taken left-to-right.
DOLLAR - Produces a string containing number rounded to num-decimal decimal places.
EXACT - Performs a case-sensitive, character-by-character, lexical comparison of string-1 and string-2
FIND - Performs a case-sensitive search using a lexical comparison for the first occurrence of string-1 in string-2, starting at character position start-pos within string-2.
FIXED - Produces a string containing number rounded to num-decimal decimal places, using the same rounding algorithm as ROUND.
LEFT - Extracts the left-most number-chars characters from string.
LEN - Determines the number of characters in string.
LOWER - Makes a lowercase version of string by doing a character-by-character conversion of string to lowercase, except as noted below.
MID - Extracts number-chars characters from string, starting at character position start-pos.
PROPER - Makes a lowercase version of string except that the first letter in string and any other letters in string that immediately follow a character that is not a letter, are converted to uppercase.
REPLACE - Produces a new string that is string-1 with number-chars characters starting at position start-pos, replaced by string-2.
REPT - Creates a string that is replication-count number of occurrences of string concatenated together.
RIGHT - Extracts the right-most number-chars characters from string.
SEARCH - Performs a case-insensitive search, using a lexical comparison, for the first occurrence of string-1 in string-2, starting at character position start-pos within string-2.
SUBSTITUTE - Produces a new string that is string with one or all occurrences of old-string replaced by new-string.
T - Retrieves the text referenced by value.
TEXT - Produces a string containing value formatted according to format.
TRIM - Makes a string that is a copy of string with the leading and trailing space characters removed, and each sequence of embedded spaces reduced to a single space.
UPPER - Makes an uppercase version of string.
VALUE - Converts string to a number.

Send Feedback