Analysis Mode
Once the author has configured an excel file for Spark, they can perform different kinds of review actions to make sure their file is ready for upload.
Check for Warnings and Errors
Before uploading a configured file to Spark, users can run a quick check to make sure all rules have been properly implemented. If there are any errors found, uploading to Spark will not be allowed. However, if there are only warnings and no errors, users can upload anyway
Login to the Spark Assistant and enter the Analysis Mode
Click on the option to Check for warnings and errors
Any identified misspelling, overlapping ranges, etc. will be found here as warnings. More severe issues such as a solver missing any solve inputs will be considered an error.
Compare service
If using this functionality, users are advised to run the comparison using a copy of their Excel file as this functionality may make edits to the open Excel file.
With this feature, users can compare valid named ranges, formulas and values on any open Excel file with a service uploaded to Spark. Upon completion, the Spark Assistant will produce a summary of the number of differences and give you an opportunity to download a file with additional details. This summary file is a copy of the open Excel with the differences highlighted, any cells that match are grey-scaled for analysis.
Login to the Spark Assistant and enter the Analysis Mode
Click on Compare Service
Select the scope, folder, service, and version to compare with
Selecting the "Reset default values" checkbox will set the input values on the open Excel file to the same as those on the uploaded service. This helps to ensure that different input values do not affect the results of the comparison.
Click on Confirm
A summary screen gives an overview of the differences between the two files. Click on Download Details to see a copy of the file with the differences highlighted using a color scheme
For a more detailed understanding, test with the below files. First, upload the below file to Spark (try using One-step upload from the Spark Assistant!):
Once the file is uploaded, open the below file which has changes made to "Compare this file.xlsx":
Now open the Spark Assistant and go to the Analysis Mode. Click on Compare Service. Select the service you just uploaded (make sure it is the same version) and select the scope to the entire workbook. For the sake of this example, do not click on Reset input values. You will see the below screen:
Click on Download Details to download a detailed comparison file. See the file below:
Gradient Analysis
If using this functionality, users are advised to run the comparison using a copy of their Excel file as this functionality may make edits to the open Excel file.
Excel users who need to manage large tables of data can use this tool to identify trends and outliers in their data. By iteratively checking three consecutive cells within a larger table of data we can identify:
Outliers within 3 numbers: For example in the sequence {1, 3, 2}, 3 does not fall in between 1 and 2 and would be identified as an outlier.
Values that do not fall within a linearly interpolated sequence:
In the sequence of {10, 20, 30} 20 would be considered to be a perfect linear interpolation between {10, 30}.
In the sequence of {10, 29, 30}, because 29 is much farther from 10 compared to 30, it will be given a different shading.
Repeated values which may be unintentional
Text values within a range of numbers
To use this feature:
Login to the Spark Assistant and select the Analysis Mode
Click on Gradient Analysis
Input the location of analysis as well as the type and hit Confirm
There are two types of analysis - Horizontal and Vertical depending on the direction of analyzing three consecutive cells. The image below is an example of vertical gradient analysis.
The result is a colored table range created on a new tab that displays a copy of the select range color-coded as per set rules:
Black - Any cells that are non-numeric.
Light grey - First repeated value from the three consecutive cells analyzed
Dark grey - Subsequently repeated value
Bright yellow - Local minimum/Local maximum
Blue - Cell value closer to the preceding cell value
Green - Perfectly linear between the preceding and succeeding cell values
Red - Cell value closer to the succeeding cell value
Click on Generate Report to create a new tab with the metadata and color legend that can easily be shared.
Example file
Try it yourself! Download the below attached file and run a gradient analysis on the tab called "Sample Analysis Sheet".
For your reference, the horizontal and vertical analysis is already performed on the second and third tab!