- 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
Lesson Two: SUM & SKIP Formulas
Those with experience building traditional spreadsheets write formulas that reference cells, e.g. =D3:D43. Quantrix Modeler requires a shift in thinking. Your new formulas will reference categories and items instead of specific cells. Look at the following sample matrices (illustrations 2.1 - 2.3) with example formulas below. You'll write four formulas referencing categories and items instead of cells.
SUM and SKIP will be used in these exercises.
SUM is used to add up the data of a series of items.
SKIP is used when you want a formula to calculate for only a specific portion of a model matrix. SKIP allows you to exclude particular structures or cells in a formula that would otherwise calculate a value for every cell in a matrix.
The Illustration below has an example formula.
The outline below illustrates how the goal of the formula can be described in real language, which translates into a logical formula.
FORMULA 1
Goal of Formula - Sum the sales of Sedans, SUVs, Wagons and Sports Cars.
Describe in Real Language - The total equals the sum of Sedan, SUV, Wagon and Sport
Formula - Total=sum(Sedan..Sport)
FORMULA 2
Goal of Formula - Add all of the months to get the Year-to-Date total. Do not include the Total line.
Describe in Real Language - Total for the year equals the sum of all four quarters. Skip the Total line.
Formula - YTD=sum(Quarter1..Quarter4) skip Total
Before going back to your model, take a minute to describe the goal and write out the formulas you need on a piece of paper, as per the above examples. For your reference, your screen should currently look like the following Illustration.
Now return to the Sales Projections matrix in your Widgets file. Enter the formulas you have written down. To write the formulas, note the location of your Formula Editor in next Illustration and follow the steps.
- Step 1. Activate the first field in the Formula Editor
- Double-click next to the "1." in the Formula Editor.
- Your cursor should be blinking in the field. Type in the formula and hit ENTER.
- Hit ENTER again (or click the "Insert Formula" button in the tool bar), add your second formula and hit ENTER. First enter the formula without the "skip" and see what happens. Click on the formula to reactivate the field and add the "skip" statement.
- Click File > Save.
