Spry Spreadsheets - Informal Guide

Spreadsheets are a powerful and flexible option when reviewing your data and are one of the most
heavily relied upon reporting tools in Spry. However, the setup can be difficult, particularly if you don't
have experience in Excel.

This guide will provide a basic demonstration of how to use the unique features of Spry-based
spreadsheets and feature tips on how to quickly build a report using cell references.

The Formulas tab at the top of the spreadsheet page is where you will find the prebuilt commands that
allow you to access data from your scenario.

The first step in setting up a new spreadsheet is to create our reporting periods.
To start, click on the Settings button, Dates, SCHEDULESTARTDATE.

This will populate the selected cell with whatever date we have as the start of our schedule and will
update if we make changes to this date.

Above your row of Start Dates, type the name of the Reporting Period that you are reporting by.

The next step is to increment your Schedule Start Date by your reporting period. In our example, we
are incrementing by Month.

Drag out your formula for as many reporting periods as desired. This example uses Monthly reporting
periods for the year of 2019.

Copy the row so that you have two identical rows of reporting periods. This is an optional step, and
just provides ease of interpretation by having a Month/Year row and a DD/MM/YYYY row.

You can format your rows of dates to whatever suits you best. In this example the top row includes the
long name for the month and bottom row provides a simple check that our period starts on the first day
of each month.

As in the example above, each column is reserved for a certain element.

Column A is for our Equipment, Column B is the Process and Column C is the value we are
aggregating.

Not only does this method provide ease of visual interrogation, it also lays the groundwork for building
dynamic formulas.

ScheduleSum( )

To build our first formula, select a cell, click the Results button, then SCHEDULESUM.

As suggested by the name, this function sums a series of values from the Output Schedule based on
filters input by the user.

The value we using a ScheduleSum on is Source Quantity.

To apply filters click the Generate Filter Expression button as highlighted.

Select the filters you want to apply to the value that is being summed. In this example our filters ensure
that we are only summing the SourceQuantity of the Waste material worked by the equipment
EX1.

Fill in the rest of the options. This part doesn't overly matter as we will be altering our formula to refer
to our Reporting Period cells that we set up earlier.

You should now having something like the image below.

The next step is to alter the final two portions of our expression so that they reference cells.

Next we turn our value into a cell reference.

Check that your formula still returns a value and that you have locked your cells (using the $ symbol)
correctly.

You should now be able to drag and autofill your formula for all reporting periods in your Waste
SourceQuantity and OperatingHours rows and see values in your cells.

The next step is to alter the first portion of our expression; the part which determines the value we are
summing.

The next step is to alter the portion of our expression which defines our Process filter.

You can now drag your formula down the next two rows, where our Process is Coal.

ScheduleWeightAverage( )

The second most commonly used formula in Spry Spreadsheets is ScheduleWeightAverage. The
major difference between the two formulas is that ScheduleWeightAverage requires a secondary
field to weigh the chosen value against.

Access the formula through the Results tab.

In this example the value used is from the Custom Field "Imported Coal Ash Single" and the weighting
value is another Custom Field "Imported Coal Tonnes".

Note: Although our custom field for Coal Ash is already weighted on our Coal Tonnes we still need to
set a weighting field in our Schedule Weighted Average formula.

Under the Filter options, there are two new lines: Safe and Safe Value. Ticking on Safe and entering
a Safe Value will prevent errors when dividing by zero. Instead of a "DIV/0" message, whatever you
have entered as your Safe Value will be populated in the cell.

Select a Reporting Level and Period and click Accept.

As before, we can alter our formula so that we reference cells. You can also create a new column to
house the Weighted Value if you wish.

In the above example an extra piece of equipment has been added to the schedule. Since we have
already set up our references for the EX1, the process for replicating our reports for EX2 is simple.

Firstly, copy all the EX1 rows and paste them below your exti renaming the "EX1" to "EX2". If you get
errors or values that mirror those for your EX1, check your cell references and make sure they are not
locked to the wrong axis, e.g. $A6 versus A$6.

Changing Reporting Period

To change a reporting period you must first create the period in your Sections > Reporting Periods
within your scenario.

This method for changing your Reporting Period in your spreadsheet assumes you have followed the
steps above and that you have altered your formulas so that your Reporting Level and Period are
input from cell references.

The first step is to change the name of your Reporting Level the level you wish to report by. In the
previous examples we were using the Reporting Level "Month". In the example below, that reference
has been replaced by "Year".

The next step is to alter your formula so that each column contains the start date of each new period.
In the example below, the formula used adds 12 months to each previous start date, giving us yearly
reporting periods. Finally, delete any rows that aren't needed.

Troubleshooting

I'm getting VALUE errors in my cells

Use a fresh cell and the ScheduleSum tool to create the formula. If this provides a sensible value, start
replacing portions of the formula with cell references as detailed by this guide.

Make sure to check that your formula still outputs the same value after each new cell reference. If it
does not, there are a few things to check.

1. Check the spelling in the referenced cell EXACTLY matches your Equipment / Process /
Custom Field / Reporting Period

2. Ensure that you have input the cell reference into your formula appropriately. You may need to
use "& or &" or both depending on the location of the segment you are changing to a cell reference.

I'm getting values that don't seem right

There are a few reasons you might get a value that does not exactly match what you expected. Here
are a few things you can do to check that the number you are reporting is correct.

1. Build your result in a fresh cell using the ScheduleSum or ScheduleWeightAverage wizard.
If it is still not what you expected, you can use your Output Schedule to investigate.

2. Check the cells being referenced. Click on the formula and ensure that each highlighted
cell reference is one that you expect. Incorrect locking on columns and/or rows before
dragging an expression over multiple cells can cause incorrect cell referencing.

3. Check that the dates in your reporting periods in your spreadsheet match the dates in
your reporting periods for your schedule. This most commonly affects the first reporting period.
Go to Sections > Reporting Periods to check the Start and End Dates of your reporting
periods.

4. Use a Pivot Table or export your Output Schedule to excel to double check the result.
Make sure you use exactly the same filters that you applied to your spreadsheet formula.

Want to learn more?

Online Help Manuals - Click here for the latest version

Learning Management System - Click here to login or here to request access