Often times when gathering pricing information from Suppliers, you will also want to use formulas to calculate different outputs based on their provided data. With Scout's Formula capability, you will be able to create both simple and complex formulas within Scout. 

In this article, we will cover:

1. Basic formula functionality in a Worksheet
2. How to access Advanced Formulas in a Worksheet
3. The different capabilities of Advanced Formulas
4. How to test your 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 (Quantity * Price) 

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, click the arrow by the "Edit Column" button and select "Remove all Columns" from the drop down.

To create a calculated column, open the Edit Column side panel and set the Type to “Formula”.  This will bring up the “Edit Formula” button. 

Clicking “Edit Formula” will open Scout’s formula builder. Click on a column header name from the options on the left to choose which variables will be pulled in to the formula. Type in numbers and functions, or choose from the function options in the bottom bar. Click “Save Formula” when finished.

These basic formulas are 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.  

(*Note: For faster navigation in the formula tool, you can use your keyboard's arrow keys to move through the Add Function drop-down options and the tab key to move the curser out of a function.) 

2. How To Access Advanced Formulas

To access Advanced Formulas on Scout, open the Formula Builder and click the "Add Function" button expand a dropdown list of advanced formula options. 

3. Advanced Formula Capabilities

The Advances Formula options are: IF, ROUNDUP, ROUNDDOWN, FLOOR, ABS, POWER, SQRT, MIN, MAX, SUM, and AVG. 

IF Statements - IF(Something is True, then do something, otherwise do something else)

ROUNDUP - round data up to a given number of digits - ROUNDUP(Value, number of digits)

ROUNDDOWN - round data down to a given number of digits - ROUNDDOWN(Value, number of digits)

FLOOR - rounds a number (towards zero) to the nearest specified multiple of significance - Floor(Value, significance) 

ABS - returns the absolute value of the input - ABS(value)

POWER - Power(Value, to the power of)

SQRT - returns the square root of the entered value - SQRT(Value)

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], ... )

SUM - returns the sum of a series of values. SUM(value1, value2, ...)

AVG - returns the average of a set of values. AVG(value1, value2, ...)

4. How to Test your Formulas

In order to ensure that your formulas are working, you can use the Preview mode to enter in Supplier  data and test the functionality of your formulas.  Locate the "Preview" button from the options at the top of the page within the Worksheet Builder:

In this example, we are using an IF statement to choose which price point will be used in the calculation. If Quantity is equal to or greater than 50, the column will output Quantity times the Bulk Discount Price. Otherwise, the column will output Quantity times Price. 

To verify the formula is working as expected, open up the preview view and input test data in to the Supplier Fill columns:  

By entering this dummy data we can verify that the Worksheet is handling the calculation correctly, which means our formula is working properly. 

This example was just one of the many ways that you can leverage the Advanced formulas functionality in Scout. 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 on Worksheets