How-To Use Excel Formulas (with Example Script)

When working with Excel you can send Excel formulas directly from Foxtrot. Just target a cell, choose Send Value action and type formula you want to use. Essentially, you can make Foxtrot imitate any Excel formula that you are using during process, whether it is simple sum formulas, lookup formulas, or advanced cross-document formulas. In this article, we will take a look at how you properly utilize this option and set up your Send Value actions correctly.

Please note that formulas need to be sent in American format and in American language. Foxtrot will make sure to automatically convert the formulas to the format (and the language) of your Excel. American format means:

• Using a dot as a decimal separator when sending decimals
• Using a comma as arguments separator

=IF(D2>1.545,TRUE,FALSE) – correct

=IF(D2>1,545;TRUE;FALSE) – incorrect

Make sure to keep this in mind during this article and going forward. So, if you have a process today where you use formulas in Excel that are not American formatted, you can still set up Foxtrot to utilize these formulas, you simply have to find the correct American way of writing the formula. In other words, you have to make sure to translate the formulas.

Example Script

In the attached file (find at the end of the article), you can find an example on how to build a script using Excel formulas. You can download the script and try to run it. Remember to save the Foxtrot Project File to the same location as your other projects, typically in this destination: C:\ProgramData\Foxtrot Suite

The output of the attached project is something similar to:

So, in this example, we use four different Excel formulas:

• Column C: =$A2*$B2
• Column D: =SUM($C$2:$C$11)
• Column E: =SUMIF(A:A,">2000",C:C)
• Column F: =AVERAGE(A:A)

Notice how we simply have to make sure that the formulas are with American format, then they work just like just they were put in manually. And Foxtrot does automatically translate the formulas to the correct local format, for example, if your machine is Danish, the SUMIF formula in Excel document will be:

The whole script is: