STEP 3: Formulas Supported in the Excel Model

Overview

Here is a list of the Excel formulas that are accepted within ValueCore.

Arithmetic Operations

Supports +,-,*,/,^ in any combination or order

IF Statement

IF condition logical operators: <,>,<>,>=,<=, AND, OR

Only one AND/OR is supported. No support for multiple AND/OR or a combination of both.

Examples

=IF(VROI_INPUTS!$C$4=3,0,1)

=IF(AND(VROI_INPUTS!$C$4=3,VROI_INPUTS!$C$4=3),0,1)

=IF(OR(VROI_INPUTS!$C$4=3,VROI_INPUTS!$C$4=3),0,1)

=IF(VROI_INPUTS!$C$4=3,0,IF(VROI_INPUTS!$C$4=3,0,1))

Other supported functions (examples)

=SUM(B4:B7)

=SUM(C3:C5,B2)

=IF(VROI_INPUTS!C4=3,0,1)

=IF(VROI_INPUTS!$C$4=3,0,1) 

=IF(VROI_INPUTS!C$4=3,0,1)

VLOOKUP

NPV

IRR

XIRR

Support operations with percentages (X*2%). For ex =VROI_INPUTS!C2*2%

Supports '=+'. For ex: =+VROI_INPUTS!C2+VROI_INPUTS!C3

Supports X*-1. For ex: =VROI_INPUTS!C3*-8

Calculated Input/Override Variables

admin user/model developer can create calculated input/override variables. You can import this variable type with a "flat" file. Under the "VROI_OUTPUTS" tab, you need to add an extra column called "Override" and add "1" next to the variable that you want to become calculated input/override.

MIN/MAX

Limitations

Due to Blockly limitations, the following is not supported =MAX(B2:B5, 100)

Workaround

Use nested MIN/MAX, i.e: =MAX(10,MAX(20,MAX(30,MAX(40,MAX(50,100)))))

ROUND, ROUNDUP, ROUNDDOWN

Limitations

Due to Blockly limitations, round to 1, 2, etc. decimals are not supported =ROUND(B2,2)

Workaround

round to 1 decimal =(ROUND(B2*10,0))/10

round to 2 decimals =(ROUND(B2*100,0))/100

ROUND

ROUNDUP

ROUNDDOWN

Limitation Examples

Example 1

In General - keep calculations/formulas OUTSIDE of IF statements. Create interim output calculations ABOVE IF on the Excel

E.g.: The following will not work:

=IF(VROI_INPUTS!B1=1,0,1)*(B2+B3)  

Fix 1

need to split into 3 variables 

var1 =IF(VROI_INPUTS!B1=1,0,1)

var2 =B2+B3

var3 (resolved) =var1+ var2

Example 2

=MAX(12-VROI_INPUTS!B24,0)*(VROI_OUTPUTS!B18/12)

Fix 2

Need to be split into 3 variables 

var1 =MAX(12-VROI_INPUTS!B24,0)

var2 =(VROI_OUTPUTS!B18/12)

var3 (resolved) =var1*var2

Parentheses

1. Support "(" and ")" as the first and last chars of an Excel function

   Extra Parentheses

   1. Examples

      =(IF(VROI_INPUTS!B1=1,1,IF(VROI_INPUTS!B153=2,2,2)))

      Due to Blockly limitation, we can have only two numbers or variables, or use a nested function, like MIN(1,MIN(2,3))

      There are other specifics (minor)

Blockly Functionality

All functionality above is supported by an Excel upload document.

MIN, and MAX, are not currently supported.

All functionality above is supported by an Excel upload document.