-------------------------Monitor------------------------ The Newsletter for PC-Based Data Acquisition and Control Issue 123 www.windmill.co.uk October 2008 --------------------ISSN 1472-0221---------------------- Welcome to Monitor. This month we're extolling the virtues of Pivot Tables in Excel, and discussing the use of LVDTs to measure displacement. I hope you enjoy the newsletter but if you do want to remove yourself from our mailing list please go to https://www.windmill.co.uk/newsletter.html CONTENTS ======== * Measuring Small Changes in Position * Analysing Data in Excel: Are Pivot Tables the Answer * DAQ News Roundup ________________________________________________________ ________________________________________________________ Measuring Small Changes in Position ________________________________________________________ When monitoring small movements a Linear Variable Differential Transformer, or an LVDT, is commonly used. For instance, you can use an LVDT in shock testing, to measure the thickness of machined parts and to measure displacement of concrete during strength testing. How an LVDT Works ================= An LVDT comprises three coils wrapped around a tube. The two outer coils are called the secondary coils, whilst the middle coil is the primary coil. Inside the tube is a moveable magnetic core. You attach the object whose position is to be measured to this core. As the object moves, the core slides within the tube. A constant alternating current driven through the primary coil of the transformer causes a voltage to be induced in each secondary coil. As the core moves, the inductances change, causing the voltages induced in each secondary coil to change. When the core is exactly between the two secondary coils, the voltage output is zero. As it is displaced in one direction, the voltage in one coil increases as the other decreases, causing an output voltage. The magnitude of the output voltage is proportional to the distance moved by the core. The sliding core does not touch the inside of the tube. Parts do not rub together or wear out, making the LVDT very reliable. This lack of friction also leads to the LVDT being able to measure infinitely small changes in core position. The only limiting factor is in the resolution of the data acquisition equipment used to monitor the LVDT. Computerised Data Logging ========================= LVDTs normally use an AC excitation, and give an AC output signal. Data acquisition equipment for AC energised LVDTs therefore require additional oscillator and demodulation circuits. Given the specification of the LVDT, these circuits will be supplied in the appropriate physical format. DC energised LVDTs are available with the oscillator- demodulator circuit built-in. These require an excitation supply and give a normal low voltage DC output signal. The Microlink 3806 module, for example, provides the excitation and signal conditioning for LVDT probes. This is part of the Microlink 3000 measurement and control range from Biodata Ltd (http://www.microlink.co.uk/). For more information on using LVDTs contact sales@biodataltd.com ________________________________________________________ Analysing Data in Excel: Are Pivot Tables the Answer? ________________________________________________________ ________________________________________________________ In issue 116 of Monitor (https://www.windmill.co.uk/monitor116.html) I explained how to use an array formula to perform calculations on values matching two or more criteria. I also told you how to return several values from one formula - the three largest readings in a list for example. This month we'll find out how to do similar things using a pivot table. You can download an example spreadsheet from https://www.windmill.co.uk/excel/pivottable.xls Pivot tables can be quite fiddly to set up. Once you have done so though, it is easy to display different pieces of information with a few mouse clicks. If you were using, say, an array formula to do the same thing you would have to keep editing the formula. Another advantage is that a pivot table eliminate errors than could otherwise creep into your formula, either through typing mistakes or a faulty understanding of how the formula works. A pivot table's data is valid and transparent: it is easy to see if you have made a mistake creating your pivot table. It is not always obvious if you have made a mistake when using a formula to analyse data. Imagine you have a table of data with temperature and humidity readings. With a pivot table you can quickly display the average temperature at each humidity level. Creating a Pivot Table ====================== To create a pivot table your data needs to be laid out in a particular way. Readings collected by the free Windmill Logger program comprises lists of timestamped data. For example Time Temp Humidity Secs oC % 16:27:42 9.23 63 16:27:43 9.23 63 16:27:44 9.22 62 To create a pivot table the first row must contain the name of the column of data below it. The next row must contain the first set of data items. As you can see, Windmill Logger also includes a row of unit names. Before creating your pivot table you need to move these to above the data names - like so Secs oC % Time Temp Humidity 16:27:42 9.23 63 16:27:43 9.23 63 16:27:44 9.22 62 Now, in Excel, click anywere in any of the data columns. From the Data menu select Pivot Table. Choose to analyse an Excel list and create a PivotTable. Step through the rest of the Wizard. A grid appears with instructions to "drop data and fields here". Above this is the PivotTable dialogue box showing your column names. For our example you need to drag "Humidity" to the Rows area and "Temperature" to the Columns area. By default the pivot table thinks you want to sum the data. However, we want the average. So double-click "Sum of Temperature" and change to "Average of Temperature". If you make a mistake right-click the pivot table, select Wizard > Layout and change your selections. To remove a data column just drag it off the pivot table. The pivot table will show the average temperature for each distinct humidity reading. With a pivot table you can easily change the data you are analysing. For instance, you could drag Humidity off the table and replace it with Windspeed (assuming you have a column of Windspeed readings of course). You can also show average Temperature against both Humidity and Windspeed. Using Wizard > Layout put Humidity in the Row area, Windspeed in the Column area and Average of Temperature in the Data area. (Just drag anything in the wrong area away in the Layout Wizard.) When I discussed Array Formulae, I showed how to find a conditional value. For example, the average temperature when humidity was 62%. You can't do this directly with a pivot table, but can achieve a similar result by hiding some results. Click the down arrow by Humidity in the pivot table, and deselect values below 63%. Only average temperatures for readings over 62% are now shown. Hiding some readings in this way can be extremely useful. For instance, using an array formula you can show the three largest readings in a column. Using a pivot table you can not only show the three largest values but also how often they occurred and at what temperature or humidity. You can sort and format the data in a pivot table exactly as you would normally. Just select the data values then use Excel's Format and Data menus. A pivot table has many advantages but it does have limitations. For instance, you can only use the functions provided: sum, count, average, max, min, product, standard deviation, etc. You cannot create your own custom formulae. To answer the question posed at the beginning of this article - are pivot tables always the answer? Of course not, but as they offer flexibility and error free analysis I've been won over to them and they are now the first analysis technique I consider. Further Reading =============== Using Excel for Data Acquisition and Control https://www.windmill.co.uk/excel/ Monitor 116: Using Array Formulae https://www.windmill.co.uk/monitor116.html ________________________________________________________ ________________________________________________________ DAQ News Roundup ________________________________________________________ Welcome to our roundup of the data acquisition and control news. If you would like to receive more timely DAQ news updates then grab our RSS newsfeed at https://www.windmillsoft.com/monitor.xml. Read https://www.windmill.co.uk/newsfeed.php for notes on how to display the news on your own web site, read it via e-mail, mobile phone or in your browser. Thermocouple accurate beyond 1300 oC One of the ways to make aircraft engines more efficient is to run them at higher temperatures. However to do so safely requires heat-treating the super-alloys of which certain engine components are made to ensure they can withstand hotter conditions. This process involves exposing these components to very specific temperatures in excess of 1300 oC. Achieving exact temperatures is critical and getting it wrong by more than two degrees in either direction can have major implications. Thermocouples are used to ensure correct temperatures are achieved in this process. Previously, thermocouples only offered an accuracy of plus or minus three degrees. Now NPL has reduced thermocouple uncertainties to less than one degree. Source: NPL http://www.npl.co.uk/ Battery-Free Chemical Sensors GE Global Research have developed a battery-free, radio-frequency identification (RFID) sensor. Billed the "first-of-its-kind", a single sensor provides a highly selective response to multiple chemicals under variable conditions. Without a battery, new sensors can be designed to be smaller than a penny and manufactured at very low cost. The power is obtained wirelessly from the sensor reader. Source: GE Global Research http://www.ge.com/ Fastest Bar Code Reader Building on a series of recent breakthroughs in ultrafast analogue-to-digital conversion, UCLA engineers have designed a bar code reader that is nearly a thousand times faster than any device currently in use. There is a need for speed as the volume of information in bar codes is increasing and they are becoming integrated into real-time sensor networks; plus there is a requirement for high-speed scanners for non-contact position and displacement sensing, as used in real-time inspection and monitoring in industrial applications. Source: UCLA http://www.newsroom.ucla.edu/portal/ucla/ucla-researchers-develop-the-world-64157.aspx Sensor Could Help Avert Pipeline Failures Researchers at the National Institute of Standards and Technology and Colorado School of Mines have developed a sensor that quickly detects very small amounts of hydrogen accumulation in pipeline steel. The new sensor could provide early warning of pipes that have accumulated excessive amounts of hydrogen - a notorious source of embrittlement - and avert potentially disastrous failures of pipelines carrying hydrogen fuel. Source: NIST https://www.nist.gov/ Fieldbus Foundation and ISA Announce Collaborative Wireless Initiative The Fieldbus Foundation and ISA have announced an agreement to implementat wireless backhaul transport networks. This technology initiative is based on shared interests in serving the needs of end users and suppliers of wireless systems in industrial automation. Source: ISA https://www.isa.org/ ________________________________________________________ ________________________________________________________ * Copyright Windmill Software Ltd * Reprinting permitted with this notice included * For more articles see https://www.windmill.co.uk We are happy for you to copy and distribute this newsletter, and use extracts from it on your own web site or publication, providing the above notice is included and a link back to our website is in place. An archive of previous issues is at https://www.windmill.co.uk/newsletter.html and an index of articles at https://www.windmill.co.uk/newsletter.html Windmill Software Ltd, PO Box 58, North District Office, Manchester, M8 8QR, UK Telephone: +44 (0)161 834 6688 Facsimile: +44 (0)161 833 2190 E-mail: monitor@windmillsoft.com https://www.windmill.co.uk/ https://www.windmillsoft.com/
Subscribing
To receive Monitor every month please fill in your e-mail address below. We will not pass your address to any third parties, nor send you any unsolicited e-mail.
Subscribe Monitor
You will receive an e-mail confirming your subscription, with details of how to download the free software. If you don't receive this then your spam filter may be blocking our message. Make sure it is set to accept messages from monitor@windmillsoft.com. If you have problems contact the Editor.
