STEP 3: Formulas Supported in the Excel Model

Overview

This article provides a detailed list of the Excel formulas that are compatible with ValueCore, a platform or system that integrates with Microsoft Excel. These formulas are the ones that can be used within ValueCore to perform various functions, calculations, and data analysis tasks. The article likely outlines which specific Excel functions are recognized and can be executed properly within the ValueCore environment, ensuring users can leverage them effectively. It may also highlight any limitations or restrictions on certain Excel formulas, along with any special instructions for using them within the platform. By providing this list, the article helps users understand which formulas will work seamlessly in ValueCore, allowing them to efficiently manage and analyze their data.

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

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.