This post explains how to use excel templates for use in NXT2excel.
When using NXT2excel for monitoring the NXT in real-time one can use an empty excel file to write the observations to. You can also use a template, this allows you to add some extra analyzing power. For example you can compute values from other values using excel formulas. You can add graphs that are updated real-time. Or you can format your results to make it look better. I’ll explain how to do this, but before I can do so you’ll need to understand how NXT2excel writes observations to your excel sheet.
The picture above shows the first five rows of an excel template for NXT2excel. Only these rows matter when making a template.
Row 2 is the most important row as this is the row NXT2excel writes its observations to. In this example it is assumed that 6 values are sent from the NXT to excel. Values will be written starting at column A and so on. The 6 green cells will be filled with data. Row 2 will always contain the most recent data, so you don’t have to scroll to see it.
Row 1, marked blue, is not used by NXT2excel. You can use it to add labels so you know what you are looking at.
Row 3 is also not used and serves the same purpose.
Row 4 and later, marked purple, are used to store the complete history of all values received. Newer data is added below older data. These data rows are filled by copying row 2 and inserting its contents into the first empty row.
To get a nice lay out for your data you can add formatting to row 2. There is no need to format the entire column as the formatting of row 2 will be copied with the data itself to row 4 and on once the data starts coming in.
Adding calculated values
If you want to add calculations to your template you can do so by adding formulas to row 2, marked in red. These formulas will be copied with the data to row 4 and on. This means that you don’t have to fill an entire column with your formula before hand. In this example the speed of motor A is calculated. When you use this example you’ll never see a valid result in row 2 because to calculate the speed you need two observations, the most recent one and the one before that. But, once the formula is copied together with the data to row 5 and later it will show the speed of the motor.
You can add graphs that are updated real time. For this to work one has to put the graph on another sheet. You’ll have to let your graphs point to row 4 to row 5 for data. This way they’ll grow when new data arrives. This is also why there are two rows that are marked purple. (NXT2excel will insert an empty line just before row 5 before copying data thus ensuring the data range in your graph grows every time new data is recieved.)
- I used background colors in the example. These are only used to point out the different regions. You do not have to use background colors in your template.
- One can add as many formulas as one likes. But remember all data columns are always to the left of formula columns.
- When developing graphs it is advised to manually add data to rows 4 and 5. This will help you seeing what your graph is going to look like.
- Calculations and graphs will slow down excel and NXT2excel. As a result you can lose data. If you do not want to lose data there are some options. Send data from the NXT less often, add graphs and formulas after you received the data, minimise excel when receiving data or limit the maximum number of observations in NXT2excel.
- Make your templates read only so you can reuse them over and over again without having to clean them.