Forcing evaluation of Excel formulas

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 the 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 on Windows (and Composer), see this article

For a list of formulas supported by Player on all other supported platforms, see this article

Important note: To properly use excel formulas, make sure that the first data row entry of each column in excel contains a numeral value. If there are no values on the first data row, the cells will be treated as text when updated, thus excel formula will not be effective.

Formula limitations on Windows PCs

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 =SUM(Answers!A:A1000)
• 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. =A will 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
• Computation of Excel formulas for date/time cells. Such as but not limited to:  =IF(B2="","",SUM(C2-B2)) where B2 and C2 contain time "10:53:10 AM" or date inputs.

If you encounter issues using any of the supported Excel formulas, review the Excel interface asset limitations, if you still have issues please open a ticket with Technical Support.

Usage scenarios:

Calculations

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 

Another calculation example is to compute the area of an asset. For example, you can use a rectangle's width and height values to compute its pixel size on the scene through an excel formula.

In Intuiface, using a trigger, you can send the width and height values to excel, then get the formula resulted value and display it on a text asset. 

The following image illustrates how to set triggers that will write width and height asset values to excel

To see the theory in action, download this sample experience 

Creating conditions for asset visibility

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.

Creating a 

Questions or comments? Post your Excel formula usage scenarios on our User Community website.