- About Quantrix
- Solutions
- Products
- Services
- Sample Models
- Learning
- Learning Overview
- New in Quantrix 3.0
- Video Demos
- Webinar Series
- Quick Guide
- User Guides
- Modeler Notes
- Marketing Response Curve Model
- Pivoting Data
- Quantrix in Corporate Budgeting
- Using Expressions in Quantrix
- Performing a Sales Analysis
- Indirect() w/Capex & Depreciation
- Analyzing ARM vs. Fixed Mortgages
- Integrated Financials Models
- Leveraging the @ and # Operators
- Using Circular References
- Converting Spreadsheets to Quantrix
- Using Functions with Black-Scholes
- Using Statistical Functions
- Using Quantrix in Capital Budgeting
- Working with Scenario Categories
- Whitepapers
- FAQ
- Support
- Store
LEARNING
Leveraging the @ and # Operators in Formulas
There are times in a model when you want to utilize the names and positions of the items in your matrix formulas. Perhaps you have a category of Years and you want to pull the actual Year value into a formula. Or you have a category of Months and you want to translate the May item as the value of 5 in a formula. The use of @ and # operators in Quantrix formulas allows you to do just that.
| In this example, we use the names and positions of items to calculate how many months away the current month is from a specified date. There are three formulas in the model to illustrate how these operators work. Formula 1 Year = value(@YEARS) This formula states: Take the item names contained in the 'YEARS' category and place them into the cells associated with the ‘Year’ item. The ‘@’ operator is the key for moving the item names into the cells. The ‘@Years’ is surrounded by the ‘Value’ function so it will turn the text of the year into a number. Formula 2 Month = #MONTHS This formula calculates the position of the Month item from category 'MONTHS' into the appropriate cell. The ‘#’ operator is the trigger for the Quantrix formula to return the position of the item as a value to the cell. So 'May' returns a value of 5, 'Nov' a value of 11 and so on. |
 |
Formula 3
Number of Months from Date = (Specified Date::Year - value(@YEARS)) * 12 + (Specified Date::Month - #MONTHS)
This formula takes the concepts of formula #1 and formula #2 and puts them together to perform a calculation. The calculation determines how many months away each Month / Year combination is from a specified date. In this case, the specified date is in the Year 2000, Month 6. Lets look at the calculation for the month of January, year of 1997. The first part of the formula states: (Specified Date::Year - value(@YEARS)) * 12 Specified Date::Year returns a value of 2000; Value(@Years) returns the number 1997. Those two values are subtracted to get the result of 3 and then multiplied by 12 to derive the answer of 36 The second half of the formula states: (Specified Date::Month - #MONTHS) Specified Date::Month returns the value of 6 and #MONTHS returns the value of 1. Those two values are subtracted which produces the result of 5. Lastly, the 36 from the first half of the formula and the 5 from the second half of the formula are added together to produce the result of 41. So the formula calculates that January, 1997 is 41 months away from June, 2000. The years 1997 – 2004, and all the months contained therein, are thus calculated by one powerful Quantrix formula utilizing the @ and # operators. These techniques are often useful when applying statistical functions to solve optimization and regression problems (see Using Statistical Functions in Banking Model).
Downloads:
>> Operators Model