element14 Community
element14 Community
    Register Log In
  • Site
  • Search
  • Log In Register
  • Community Hub
    Community Hub
    • What's New on element14
    • Feedback and Support
    • Benefits of Membership
    • Personal Blogs
    • Members Area
    • Achievement Levels
  • Learn
    Learn
    • Ask an Expert
    • eBooks
    • element14 presents
    • Learning Center
    • Tech Spotlight
    • STEM Academy
    • Webinars, Training and Events
    • Learning Groups
  • Technologies
    Technologies
    • 3D Printing
    • FPGA
    • Industrial Automation
    • Internet of Things
    • Power & Energy
    • Sensors
    • Technology Groups
  • Challenges & Projects
    Challenges & Projects
    • Design Challenges
    • element14 presents Projects
    • Project14
    • Arduino Projects
    • Raspberry Pi Projects
    • Project Groups
  • Products
    Products
    • Arduino
    • Avnet Boards Community
    • Dev Tools
    • Manufacturers
    • Multicomp Pro
    • Product Groups
    • Raspberry Pi
    • RoadTests & Reviews
  • Store
    Store
    • Visit Your Store
    • Choose another store...
      • Europe
      •  Austria (German)
      •  Belgium (Dutch, French)
      •  Bulgaria (Bulgarian)
      •  Czech Republic (Czech)
      •  Denmark (Danish)
      •  Estonia (Estonian)
      •  Finland (Finnish)
      •  France (French)
      •  Germany (German)
      •  Hungary (Hungarian)
      •  Ireland
      •  Israel
      •  Italy (Italian)
      •  Latvia (Latvian)
      •  
      •  Lithuania (Lithuanian)
      •  Netherlands (Dutch)
      •  Norway (Norwegian)
      •  Poland (Polish)
      •  Portugal (Portuguese)
      •  Romania (Romanian)
      •  Russia (Russian)
      •  Slovakia (Slovak)
      •  Slovenia (Slovenian)
      •  Spain (Spanish)
      •  Sweden (Swedish)
      •  Switzerland(German, French)
      •  Turkey (Turkish)
      •  United Kingdom
      • Asia Pacific
      •  Australia
      •  China
      •  Hong Kong
      •  India
      •  Korea (Korean)
      •  Malaysia
      •  New Zealand
      •  Philippines
      •  Singapore
      •  Taiwan
      •  Thailand (Thai)
      • Americas
      •  Brazil (Portuguese)
      •  Canada
      •  Mexico (Spanish)
      •  United States
      Can't find the country/region you're looking for? Visit our export site or find a local distributor.
  • Translate
  • Profile
  • Settings
Personal Blogs
  • Community Hub
  • More
Personal Blogs
Legacy Personal Blogs Using spreadsheets (Excel) for simple computations and approximations
  • Blog
  • Documents
  • Mentions
  • Sub-Groups
  • Tags
  • More
  • Cancel
  • New
  • Share
  • More
  • Cancel
Group Actions
  • Group RSS
  • More
  • Cancel
Engagement
  • Author Author: koudelad
  • Date Created: 19 Aug 2017 7:42 AM Date Created
  • Views 1013 views
  • Likes 5 likes
  • Comments 4 comments
  • relative humidity sensors
  • excel
  • temperature & humidity sensor
Related
Recommended

Using spreadsheets (Excel) for simple computations and approximations

koudelad
koudelad
19 Aug 2017

I created this blog post from the information I originally posted in this thread: If I Had a Hammer  made by jack.chaney56 .

 

1) Using Excel (or other spreadsheets) to compute/visualize the data.

I will consider the STMicroelectronics  HTS221HTS221 as an example. The sensor outputs temperature and humidity in 16-bit registers in 2nd complement format I will now focus only on relative humidity measurement (the temperature is the same. The datasheet states that the output should be linear (related to the humidity) and two reference values are provided (stored in the sensor by STM) - two output registers values at obviously two relative humidity values at which the registers values have been recorded.

In order to make sense of the values, you need to open another application note.

 

The following pictures are taken from the application note:

image

image

So I have read the calibration values over I2C and entered them into an Excel spreadsheet:

image

Here comes the first surprise, but let's visualize it. I selected the data and notes (B6:D7) and inserted a scatter plot:

image

Then I made a few minor changes to correspond the plot with possible output values. Double clicking on the X and Y axis show a dialogue where it's possible to set axis bounds (among other thing). Minimum for a 16-bit signed number is −32 768 and maximum is 32 767. I also adjusted the X axis to see -20 to + 120 % values.

You might say we should only adjust the X axis for 0 to 100 value, but this gives us a bit better idea of what values could appear in the output register. Later I found out that one of the sensor outputs about 117 % rH, which is nonsense; while another sensor, SHT21 said it's just about 91 % rH.

image

Then I added a trend line (right click on one of the values - the blue dot in my case - and selecting Add Trendline...).

image

Additional dialog is shown with various options. Since we know that out sensor output has linear characteristic, let's stick with linear. We know our values at 32 % and 73 % rH and we would like to see the values through the whole possible spectrum (plus the 20 % surroundings). I edited the forecast values, forward to 47 ( 73 % + 47 % = 120 %) and backward to 52 (32 % - 52 % = -20 %). I also checked Display Equation on chart and display R-squared value on chart.

 

The result is shown immediately:

image

As you can see, the output value has different slope than in the datasheet and the application note. However, the math remains the same.

 

More interesting is the equation and the R-sqared value:

image

This is another way to compute the sensor output value than using the computation from the application note. (Try it yourself with an calculator.) This makes no sense in this case, of course, since the equation suggested by STM satisfies with integer values. The power of computing and showing the trend line and the R-squared value is in all unknown characteristics. (For example if more values are measured and you are not sure, whether the sensor provides a linear / logarithmic output.) The R-squared value here is 1, which would normally mean a perfect match of the suggested function to the values provided, but we got that because we provided only 2 values. The more values, the better idea of the relation you get.

 

More about trend lines can be found in this Microsoft Office support article: https://support.office.com/en-us/article/Add-change-or-remove-a-trendline-in-a-chart-fa59f86c-5852-4b68-a6d4-901a745842a…

More about R-squared value can be found here: https://people.duke.edu/~rnau/rsquared.htm

 

2) Using Excel (or other spreadsheets) to find the missing data.

Now let's say you can read the humidity register values and compute the relative humidity. What if you wanted to answer the opposite question, what register value will be valid for a specific relative humidity (for example to set some threshold or interrupt directly in the sensor) ?

 

I created one more column to the table and typed the equation for relative humidity computation:

image

image

If I enter my output registers value, I get the relative humidity value.

 

To do the opposite, we will use a Goal Seek function (Data > What-If Analysis > Goal Seek...)

(In previous Office editions, there was an extension called a Solver (it might not be present in some editions or you might need to enable it. More information on solver can be found in this support article: https://support.office.com/en-us/article/Define-and-solve-a-problem-by-using-Solver-9ed03c9f-7caf-4d99-bb6d-078f96d1652c .)

 

image

 

For example, I would like to know what value will be present in the registers if the humidity is 62,5 %.

 

So I will enter the following values to the dialog:

image

We want to set the cell with the humidity value to 62,5 by changing cell with the output register.

 

After clicking OK, the computation takes a while and shows us the result.

image

And we have a solution! Well, sort of image The output register value is not round, which means we can adjust it to -8206 and/or -8207 and see whether this humidity value difference is OK for our application. (In this case is the sensor noise bigger than the difference between these two values, so I would say it fits.)

 

Final thoughts

I hope this helps someone image

 

There are also more tools for advanced analysis etc. but I don't feel the confidence in talking about them.

 

Please note that I am aware that real-life applications are not always that simple as in the examples, but spreadsheets are a huge help for almost everything I do.

 

TIP: I used Microsoft Office 2016 Professional Plus edition (the full-featured edition, not available for resale). Microsoft has a Home Use Program (HUP) that entitles employees to buy software for which your company has a subscription contract with Microsoft (typically Microsoft Office, Project, Visio), for about 25 $.

  • Sign in to reply

Top Comments

  • DAB
    DAB over 7 years ago +1
    Nice post, Somewhere in my stash is a little book that provides tips for doing science and engineering using Excel. I may have to dig it out and see what things I might be able to upgrade and use. Thanks…
  • jack.chaney56
    jack.chaney56 over 7 years ago +1
    I like the detail. I used a spreadsheet for testing some spark map calculations when I was doing engine management stuff. Jack
  • jw0752
    jw0752 over 7 years ago +1
    Hi David, Thank you for a relevant description of a powerful tool that is available to us. I need to take the time to explore and practice with Xcel. Thanks for the inspiration. John
  • mcb1
    mcb1 over 7 years ago

    Nice work.

    Us humans seem happier with graphical representation of things.

     

    I used to do Pivot Tables of data we received for alarms from sites.

    The results showed the ones that needed further investigation or explanation.

     

    VBA (Visual Basic for Applications) is able to be used to manipulate or even provide the data, but you can use Visual Basic with all the other aspects (forms, etc) to interact with it as well.

     

     

     

    Mark

    • Cancel
    • Vote Up +1 Vote Down
    • Sign in to reply
    • More
    • Cancel
  • jw0752
    jw0752 over 7 years ago

    Hi David,

    Thank you for a relevant description of a powerful tool that is available to us. I need to take the time to explore and practice with Xcel. Thanks for the inspiration.

    John

    • Cancel
    • Vote Up +1 Vote Down
    • Sign in to reply
    • More
    • Cancel
  • jack.chaney56
    jack.chaney56 over 7 years ago

    I like the detail.  I used a spreadsheet for testing some spark map calculations when I was doing engine management stuff.

     

    Jack

    • Cancel
    • Vote Up +1 Vote Down
    • Sign in to reply
    • More
    • Cancel
  • DAB
    DAB over 7 years ago

    Nice post,

     

    Somewhere in my stash is a little book that provides tips for doing science and engineering using Excel.

     

    I may have to dig it out and see what things I might be able to upgrade and use.

     

    Thanks

    DAB

    • Cancel
    • Vote Up +1 Vote Down
    • Sign in to reply
    • More
    • Cancel
element14 Community

element14 is the first online community specifically for engineers. Connect with your peers and get expert answers to your questions.

  • Members
  • Learn
  • Technologies
  • Challenges & Projects
  • Products
  • Store
  • About Us
  • Feedback & Support
  • FAQs
  • Terms of Use
  • Privacy Policy
  • Legal and Copyright Notices
  • Sitemap
  • Cookies

An Avnet Company © 2025 Premier Farnell Limited. All Rights Reserved.

Premier Farnell Ltd, registered in England and Wales (no 00876412), registered office: Farnell House, Forge Lane, Leeds LS12 2NE.

ICP 备案号 10220084.

Follow element14

  • X
  • Facebook
  • linkedin
  • YouTube