How to use Excel Formulas
Table of Contents
The value of an Asset property bound via an Excel interface asset to a spreadsheet cell will NOT be refreshed automatically if that value is derived from a formula. An action must be used to force evaluation of a formula in a spreadsheet cell.
Most commonly, use the Set text or Set value actions with the action parameter bound to a spreadsheet cell containing a formula. Use of these actions forces re-evaluation of the formula's result.
For a list of formulas supported by Player for Windows (and Composer), see this article
For a list of formulas supported by Player for Kiosks and Player for Tablets, see this article
The following limitations were identified when using the Excel interface asset on Windows PCs.
- Memory leaks were identified when referencing a full spreadsheet column in a formula rather than a predefined list of rows. For example, formulas such as
=SUM(Answers!A:A)may result in a memory leak.
To avoid memory leaks, use formulas such as
COUNTIF(A1:A8,"=3")will work only if the range contains numbers and only if the test (e.g.
=3) from the given example is a number (i.e.
=Awill not work)
- Computation of Excel formulas referencing a cell range on another sheet (such as
=SUM(OtherSheet!A1:A20)) will not work if any of the referenced cell range values are modified by the experience.
If you encounter issues using any of the supported Excel formulas, please open a ticket with Technical Support.
If you need to perform complex calculations in your IntuiFace Experience, you can accomplish this by using an Excel Interface Asset to return the desired result.
For example you can build an end user interface by linking a spreadsheet's cells with Text Input assets. Using the Update cell action you will be able to bring the values written in the Text Input assets into your spreadsheet.
The result of the formula you set in the spreadsheet can then be displayed in a Text asset using triggers and actions.
The following image illustrates how to set a text value derived from an Excel formula
To see this principle in action, download this sample experience [
By using functions such as OR; IF; AND, you can evaluate multiple variables using Excel. Remember, the result of an Excel formula will not be refreshed automatically, you will have to set a trigger/action to get the end result.
To see this principle in action, download this sample experience.
Questions or comments? Post your Excel formula usage scenarios on our User Community website.