Often times when gathering pricing information back from Suppliers, you will quickly outgrow the standard math operators and need to rely on more complex functions. With Scout's Advanced Formula capability you will now be able to create these complex formulas all within Scout. 

In this article, we will cover:

1. Basic formula functionality in a Worksheet
2. The different capabilities of Advanced Formulas
3. How to test your Advanced Formulas

1. Basic Formula Functionality

Our regular formula building supports four basic operations (add, subtract, multiply and divide). You are able to build out formulas using these operations by referencing any column that is set as a numeric or currency field column. 

When you begin building a new Worksheet, Scout provides you with a simple Worksheet with 5 different columns:

1. Title - Text
2. Unit of Measure - Text
3. Quantity - Numeric
4. Price - Currency
5. Extended Price - Formula

Note: These are the five columns that Scout initially provides you with but you are able to edit and delete them to however you would like your Worksheet to appear. If you would like to start from scratch, you can click the delete all in the upper right hand corner of the Worksheet builder to completely start from scratch. 

For this example, lets assume we are going to keep the 5 columns provided for us within Scout. Notice the last column provided to us in Scout is the Extended Price which is the product of "Quantity" multiplied by "Price":

This basic formula is often sufficient. However, there are times when you'll need to rely on more complex calculations. When this occurs, you'll need to use our "Advanced Formulas" feature.  

2. Advanced Formula Capabilities

Advanced Formulas are located within the Formula Editor in the "Functions" tab:

Additionally, the Formula Editor allows 

Math Operators: 

+, -, *, /, %, ^, |, &

Comparisons:

<, >, <=, >=, <>, !=, =,

Functions:

IF Statements - IF(Something is True, then do something, otherwise do something else). In this case, if the Suppliers are offering more than or equal to 100 units, they will get a 20% bonus. If the Suppliers offer less than 100 units, the Buyers will pay less.

MIN - returns the smallest value from a supplied set of numeric values. MIN( number1, [number2], ... )

MAX - returns the largest value from a supplied set of numeric values. MAX( number1, [number2], ... )

ROUNDUP - Round a number to a given number of digits - ROUNDUP(number, num_digits). In this example, we are rounding the price column up to the nearest dollar. The "0" in the below image is how many decimal places you want to round to.

ROUNDDOWN- Round a number down to a given number of digits- ROUNDDOWN(number, num_digits). In this example, we are rounding the price down to the nearest dollar. 

3. How to Test your Advanced Formulas

In order to ensure that you're formulas are working, you'll often need to verify that they are correct by entering in Supplier information to test the functionality of your formula.  In order to do so, you'll need to use the Supplier Preview button in Scout. 

The Preview button is located at the top of your Worksheet:

You can enter "dummy" data into your Previewed Spreadsheet to then confirm if your formulas are functioning properly. With the Advanced Formula functionality you will be able to create more complex formulas within your worksheet when collecting pricing back from Suppliers!

Click here for more articles about Worksheets