All Rights Reserved
2021@CRYSTALPRISM
+, -, *, /, ^, %
=, <>, <, <=, >, >=
IF(), IFERROR(), OR(), AND(), NOT(), IFS(), SWITCH()
VLOOKUP(), HLOOKUP(), MATCH(), INDEX(), XMATCH(), XLOOKUP()
MAX(), MAXIFS(), MIN(), MINIFS(), AVERAGE(), AVERAGEIFS(), MEDIAN(), MODE.SNGL(), RANK.EQ(), RANK.AVG(), SUM(), SUMIF(), SUMIFS(), COUNT(), COUNTA(), COUNTBLANK(), COUNTIF(), COUNTIFS()
ROUND(), ROUNDUP(), ROUNDDOWN()
CONCATENATE(), CONCAT(), &, EXACT(), FIND(), SEARCH(), RIGHT(), LEFT(), TRIM(), MID(), LEN(), TEXTJOIN(), SUBSTITUTE()
ABS(), POWER(), SQRT()
Spreadsheets make organizations vulnerable to security risks through unregulated circulation of data, such as emailing attachments without encryption or password protection in addition to risk of manual error.
Excel files can be compromised due to accidental end-user deletion, file corruption, or malware infection. Recovering from such an event could take time and potentially result in data loss. Even the latest Files Restore feature available in Office 365 has serious limitations.
Excel-based applications use password protection to enable read-only or editing access for different users on a file-level, work-book level, or work-sheet level of a document. The appropriate passwords are shared by the document owner with team members depending on the access they should be given. This runs the risk of accidentally sharing a password with unintended users.
Change tracking for a workbook can be enabled as part of the Excel Shared Workbook feature. This feature has many limitations and has been replaced by co-authoring. Co-authoring, however, cannot track changes.
Spreadsheets are often created by team members with very little IT experience. Eventually, spreadsheet files grow into highly personalised user developed applications that do not integrate well with other IT system components.
Web applications customize users’ views of specific data according to their roles, which are clearly defined and stored in the Database. This provides total security and data protection. Streamlined workflow is another notable example from a long list of web application advantages over Excel components.
"Great Java Tool! I really like that with this tool we are efficiently transitioning from Excel to the Web. This tool has a large selection of the Excel functions including the most recent one"
Anna G. USA New York
"Powerful tool with great potential!This is the only platform on the market that allows calculation of complex formulas without Excel spreadsheets or heavily loaded external files. It has a simple syntax similar to Excel. The set of available functions is sufficient to build a serious formula based application"
Verified Reviewer, USA New York
The Adaptive Calculation Engine (ACE4J) is a Java API used to evaluate the formulas written in Microsoft Excel style. This engine provides the most popular Microsoft Excel functions which are frequently encountered in applications that require heavy calculations. The number of available formulas and features increases with every Adaptive Calculation Engine release based on feedback from our customers.
Adaptive Calculation Engine operates based on the concept of a ComputeField. The ComputeField Java interface is a high level representation of input and formula fields analogous to the Microsoft Excel input and formula cells. The InputField class implements the ComputeField interface. It is used to create input fields based on their type and value. The FormulaField class is a subclass of the InputField class and is used to create formula fields based on their type and formula text. The formulas referencing the Adaptive Calculation Engine lookup functions must use the TableArray class, analogous to the Microsoft Excel range of cells which form a table.
Adaptive Calculation Engine is completely INDEPENDENT from the Microsoft Excel Workbook paradigm. The engine uses a simple Java HashMap as a ComputeFields container. This HashMap consists of key-value pairs formed by a ComputeField name key and a ComputeField value entered by the user. The ComputeField names are arbitrary and do not follow Microsoft Excel conventions as long as they do not contain special characters (underscore and $ sign excluded).
The ComputeFields and TableArray elements can be numeric, boolean, and string-based. The ComputeFieldType Java enum constants NUMERIC, BOOLEAN and ALPHANUMERIC specify this for ComputeFields. ComputeFieldType.
ALPHANUMERIC allows for the use of special characters while ComputeFieldType.NUMERIC allows the primitive numeric data types as well as the Double, BigDecimal and BigInteger types. In the current Adaptive Calculation Engine release numeric ComputeFields and TableArray elements are internally converted into doubles.
Adaptive Calculation Engine API is available via the static methods of the abstract Java class HybridFormulaEvaluator. These methods are used to compile and evaluate the formulas. To ensure the formula text syntax is correct, each formula must be compiled without errors prior to its evaluation. This will allow for optimal evaluation performance.
The main compilation Java method, compileAllFormulas(), compiles all formulas of the FormulaFields residing in a ComputeFields HashMap. It creates and returns a FormulaFieldsDescriptor compilation summary consisting of the ordered evaluation sequences of the formulas, formula dependencies and encountered errors. The primary methods of formula evaluation, evaluateDependentFormulas() and evaluateAllFormulas(), obtain a FormulaFieldsDescriptor object from the CalculationContext parameter and use it as a data structure, which directs their execution.
Adaptive Calculation Engine provides all basic arithmetical and logical operations while being completely independent from the Microsoft Excel spreadsheet paradigm. Using the concept of an abstract Compute Field instead of an Excel cell, it supports a number of the most popular Microsoft Excel functions, such as VLOOKUP(), SUMIF(), CONCATENATE(), IFERROR(), and optimized IF().
Adaptive Calculation Engine is capable of evaluating thousands of formulas within seconds while maintaining precision comparable to Microsoft Excel. At the same time nothing limits its ability to add new functionality without compromising its great performance.
Adaptive Calculation Engine is a tool of choice that allows for an easy and smooth transition from Microsoft Excel based applications, which rely on heavy calculations, to Java based Web applications. In fact it is easily integrated with any Java application because it is no more than a Java library, offering simple and comprehensive API and efficient error processing.