IDSB Banner

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.
    Step 2. Enter the formula
      Your cursor should be blinking in the field. Type in the formula and hit ENTER.
    Step 3. Enter the second formula.
      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.
    Step 4. Save your model.
      Click File > Save.
Your matrix should now look like the Illustration below. The checkmarks to the left of the formulas indicate that there are no syntax errors.



idbs_banner_960x41
About Quantrix - Contact - Site Map - Refer A Friend - Support
Solutions - Products - Services - Community - Partners - Webinars - Learning - Store
© Copyright 2014 Subx Inc.-All rights reserved -+1.207.775.0808 -