In Sourcing Optimizer, a formula is an expression that calculates a value. Formulas are used to create primary and secondary cost calculation formulas, formula columns, and feedback formula columns.
In areas of the application where you can create a formula, a formula-building interface is provided. You can use this interface to build your formula or you can type the formula into the Formula field.
You can use arithmetic and comparison operators, the bid sheet columns from your bid sheet, functions (predefined formulas), and created primary and secondary cost calculation formulas to build formulas. If you are creating a feedback formula, you can also use feedback functions. Each of these components for building formulas is described in the following sections.
Arithmetic operators
Use arithmetic operators to create mathematical expressions in your formula. Table 1 describes the arithmetic operators available for building formulas.
| Operator | Description |
| + | Addition |
| - | Subtraction and negation |
| * | Multiplication |
| / | Division |
| ( | Left parenthesis |
| ) | Right parenthesis |
Comparison operators
Use comparison operators to compare values in your formula. Table 2 describes the comparison operators available for building formulas.
| Operator | Description |
| = | Checks if two values are equal |
| > | Checks if a value is greater than another value |
| < | Checks if a value is less than another value |
| >= | Checks if a value is greater than or equal to another value |
| <= | Checks if a value is less than or equal to another value |
| <> | Checks if two values are not equal to each other |
Bid sheet columns
The Bid sheet columns area lists the columns from your bid sheet that you can use to build your formula.
Functions
A function is a predefined formula that can be used when creating a formula. A function executes calculations using specific inputs known as arguments. Each function uses a particular order of arguments to perform the calculation.
Table 3 describes the functions available, lists the arguments for each, and provides examples using the functions and example bid sheet columns.
| Function | Description | Arguments | Example |
| IF() | Tests for a condition. | ( CONDITION, OUTCOME IF TRUE, OUTCOME IF FALSE ) |
IF( Incumbent = TRUE, Freight * 0.95, Freight ) If the bidder is an incumbent, apply a 5% discount to the freight cost. |
| AND() | Check if all conditions in a test are true. | ( CONDITION 1, CONDITION 2, CONDITION 3, ... ) |
IF( AND( Incumbent = TRUE, Transit Time < 7), Freight * 0.9, Freight ) If the bidder is an incumbent and the transit time to ship the goods is less than 7 days, apply a 10% discount to the freight cost. |
| OR() | Check if any conditions in a test are true. | ( CONDITION 1, CONDITION 2, CONDITION 3, ... ) |
IF( OR( Origin Charges > 5000 , Destination Charges > 5000 ), Freight * 1.1, Freight ) If the origin charges are more than 5000 or the destination charges are more than 5000, apply a 10% penalty to the freight cost. |
| MIN() | Returns the smallest number in a set of values. | ( VALUE 1, VALUE 2, VALUE 3, ... ) |
MIN( Transit Time ) Return the shortest transit time. |
| MAX() | Returns the largest number in a set of values | ( VALUE 1, VALUE 2, VALUE 3, ... ) |
MAX( Volume ) Return the highest volume. |
| SUM() | Adds all of the values in the argument. | ( VALUE 1, VALUE 2, VALUE 3, ... ) |
SUM( Origin Charges, Destination Charges, Freight ) Add the origin charges, destination charges, and freight charges. |
| AVERAGE() | Returns the average of the values in the argument. | ( VALUE 1, VALUE 2, VALUE 3, ... ) |
AVG( Available Capacity ) Return the average available capacity. |
| MEDIAN() | Returns the median of the values in the argument. |
( VALUE 1, VALUE 2, VALUE 3, ... ) |
MEDIAN( Transit Time ) Return the median transit time. |
| ROUND() | Rounds a number to a specified number of decimal places. | ( VALUE , NUMBER OF DECIMAL PLACES ) |
ROUND( Freight, 0 ) Round the freight costs to the nearest dollar. |
| ROUNDUP() | Rounds a number up, always. | ( VALUE , NUMBER OF DECIMAL PLACES ) |
ROUND( Origin Charges, 0 ) Round the origin charges up to the nearest dollar. |
| ROUNDDOWN() | Rounds a number down, always. | ( VALUE , NUMBER OF DECIMAL PLACES ) |
ROUND( Destination Charges, 0 ) Round the destination charges down to the nearest dollar. |
| CONCATENATE() | Joins text strings together into one string. | ( VALUE 1, VALUE 2, VALUE 3, ... ) |
CONCATENATE( "Note that the cut-off day is", " " , Cut Off Day, "and the sailing day is", " ", Sailing Day ) Note that the cut-off day is Tuesday and the sailing day is Friday. |
|
BIDDER() |
Returns the bidder organization name. |
|
IF( BIDDER() = "OrganizationX", Freight * 0.95, Freight ) If the bidder is OrganisationX, apply a 5% discount to the freight cost. |
| ISBLANK() | Checks if a cell is blank and returns either TRUE or FALSE. | ( VALUE ) |
IF( ISBLANK( Origin Region ), "N/A", Origin Region) If a cell is blank in the Origin Region column, return N/A, else return the region entered in the cell. |
| NA() |
Returns blank | NA() |
IF(Price=0,NA(),Price) The formula replaces 0 with blank, and for inputs that are NOT zero, our formula will show the value (ie Price) |
| POWER() |
Returns a number raised to the given power | ( VALUE, POWER ) |
|
| SQRT() |
Returns the square root of a positive number | ( VALUE ) |
|
| LEN() |
Returns a count of characters of a text string | ( VALUE ) |
|
| LEFT() |
Extracts a given number of characters from the left of a text string | ( VALUE, NUMBER OF CHARACTERS ) |
|
| RIGHT() |
Extracts a given number of characters from the right of a text string | ( VALUE, NUMBER OF CHARACTERS ) |
|
| MID() |
Extracts a given number of characters from a given starting position in a text string | ( VALUE, STARTING POSITION, NUMBER OF CHARACTERS ) |
|
Feedback functions
A feedback function is a predefined formula that can be used when creating a feedback formula. Feedback functions differ from regular functions because they are applied across all bidders rather than per bidder.
Table 4 describes the feedback functions available, lists the arguments for each, and provides examples using the feedback functions and example bid sheet columns.
| Function | Description | Arguments | Example |
| xRANK() | Returns the statistical rank of a value in a group of values in either ascending or descending order. | ( VALUE, ORDER ) |
xRANK( Freight, ASC ) Returns the rank value of the freight costs in ascending order. |
| xRANKVALUE() | Returns the value based on its ranked position in a group of values in either ascending or descending order. | ( VALUE, ORDER, NUMBER ) |
xRANKVALUE( Freight, ASC, 2 ) Returns the value of the second-lowest ranked freight cost. |
| xRANKBIDDER() | Returns the bidder name based on their ranked position in a group of values in either ascending or descending order. | ( VALUE, ORDER, NUMBER ) |
xRANKBIDDER( Freight, DESC, 3 ) Returns the bidder name of the bidder with the third-highest ranked freight cost. |
| xCOUNT() | Returns the count of bidders for which a value could be evaluated. | ( VALUE ) |
xCOUNT( Freight ) Returns the number of bidders for which there are freight values on lanes or lots. |
| xAVERAGE() | Returns the average of the values across bidders for a value. | ( VALUE ) |
xAVERAGE ( Transit Time ) Returns the average value of the transit times on a lane or lot for which there are transit time values available. |
| xMEDIAN() | Returns the median of the values across bidders for a value. | ( VALUE ) |
xMEDIAN ( Transit Time ) Returns the median value of the transit time on a lane or lot for which there are transit time values available. |
Other Formulas
The Other Formulas area lists the primary and secondary cost calculation formulas you created that you can use to build your formula.