The Lightbulb Audit

For the past few months, I’ve been devoting a fair chunk of my “free time” (which is to say, very little time at all… I have a baby and a toddler, neither of whom sleep) to learning a bit of Java. Specifically, Java for Android. This has been… less fulfilling than expected. I’ve learnt a fair bit since the middle of the year, but not as much as I’d hoped. I do a little recreational programming in a few different languages, mostly Visual Basic and C++ derivatives, and while all languages and coding environments have their challenges, none of them compare the the peculiarities of android app development. I won’t wax poetic here, because this post isn’t actually about Java; this post is about results.

Finally, I have got my first Android app ready for public consumption (which is to say, I lowered my standards until they matched where my app was at). I think it’s pretty reasonable, in terms of presentation, but the important thing is that it’s useful. And that pretty much sums up my standards for anything.

The app is a light bulb tracking program, which I affectionately call The Lightbulb Audit. This app is designed to be a list of every light fitting in your house, and every bulb installed in those fittings, which can be carried in your pocket. That way, the next time you see LED lightbulbs on sale, instead of thinking to yourself: “Gee, that seems cheap. I wonder if I actually need more lightbulbs?”, you will check your phone and say “Wow, I actually have three blown lightbulbs that I haven’t replaced yet, no spares, that one dim bulb in the bathroom that really needs to be replaced with something more powerful, and most of my bulbs are ye-olde incandescent lightbulbs. What are we savages? time to upgrade!”… or something very much along those lines.

So, here is a link to my app on the Google Play store (sorry Apple users, after the experience I just had learning to program for Android, it’s unlikely I’ll get to you any time soon. Also, I don’t have an iPhone to test on. Also: *sound of me blowing a raspberry at you*):

https://play.google.com/store/apps/details?id=teslaandi.wordpress.com.lightbulbaudit

(Please see “important” note below!)

I appreciate feedback, comments, suggestions, and bug reports, so please feel free to leave comment on this blog post to let me know what’s on your mind (regarding the app).

But! Because I’m such a nice guy, I’ve also put up a copy of my Lightbulb Audit spreadsheet (which is how this thing originally started life). Not only can non-android users access this, but you can also export a CSV file from the app and paste it into this spreadsheet:

the-lightbulb-audit

Please, feel free to modify the spreadsheet, add colourful graphs, add features, etc. And please share your updated version if you do! Sharing is caring.

IMPORTANT!!!! – Because I’m such a nice guy and put my app up for free, I don’t make money off it just from you downloading and using it. Where I can make a few cents is from you clicking on the ad banner at the bottom of the app (which I’ve tried to make as unobtrusive as possible, because the afore mentioned niceness). So, if you see any ads that are even vaguely interesting: please, click on it. At the time of writing, my revenue is up to about $2.06… sigh… Oh well, I do it because I enjoy it, not to make money. Also because I’m just such a nice guy and want to help you and your lightbulb situation… Which is a mess, right? Be honest.

ALSO IMPORTANT!!!! – Please don’t leave poor reviews. Any comments, questions, queries, or suggestions: Please leave a comment here, and I’ll do my darndest to look after you…

…On account of being such a nice guy.

Advertisements

Excel 2010 – Autofill Series not working with filtered columns

Here’s a fiddly little feature of Excel 2010 which really gives me the irrits. If you have applied a Filter to columns in your spreadsheet, and you have then filtered by one or more of the columns, you can no longer use Autofill. I don’t know why this is a thing that must happen, but it just seems to be the way that the Microsoft decided we must want it to work.

And frankly, it’s our own fault for not wanting to do things the way Microsoft think we should want to do things, right?

here’s an example:

started list

Let’s say I’ve started a simple list, and I want to Autofill one of the columns…

autofill works

Autofill works just fine. And isn’t it a useful feature?

filter working

Now let’s say I’ve applied a filter first… still works okay.

filter not working 1

But imagine I’ve had the audacity to filter out one or more of the rows first… see that pop-up text… impending stupidity.

filter not working 2

Argh! Why do you have to be so stupid?!

I just thought I’d share this, because when I first decided to look into weird issues with Auto Fill not working, I couldn’t actually find ANY pages saying “by the way, if you filter your columns, Auto Fill won’t work”. Typical of the internet: a hundred pages explaining how to use the most basic features (i.e. “if you drag the little dot at the corner, you can autofill data in a series”), but not one easily accessible hint regarding unusual issues.

Stupid Excel.

No I didn’t mean that, baby, you know I love you!

Digitising Graphs – Turning Paper Graphs into Functions

I thought I’d write a short guide to anyone who is, like I once was, looking to turn a graphed curve into a mathematical function. There are many reasons why one might do this, but they boil down into one of two fundamental motivators:

  1. Insufficient data on how the curve was produced; or
  2. Too lazy/not enough time to sit down and figure out the maths behind the curve from first principles or known mathematical models

One could argue that Reason 1 actually a subset of Reason 2, because if you really made the effort, you could figure it out… But being lazy is a perfectly good reason, and exactly why people have made the software used in this guide.

In this example, we’ll look at one of the graphs from AS1403 – Australian Standard for Design of Rotating Steel Shafts. We shan’t look at all of them, because I can’t be bothered explaining the process of interpolating between multiple curves on a single chart. If anyone’s interested in that, leave a comment. Here’s the Size Factor chart:

AS1403 - Ks Size Factor

First, use your favourite screen capture software (or take a screen shot and trim in paint) to save the graph as a .bmp file. I suggest

The next step for turning this chart into a mathematical function is to convert it into a series of datapoints. I use “Engauge”, which is a free software package. Unfortunately there are some bugs with Engauge. In particular, to use this software you have to locate and install “Qt3Support4.dll”. Fortunately the folks at bConverged have done the hard work for us, and compiled a build which takes care of these issues, here: http://www.bconverged.com/download.php

Once you’ve installed the software, click File >> Import File >> then select the graph image that you want to import.

The graph should come in, with a heap of green lines all over it. These green lines are the curve/line sections that Engauge has automatically detected.

step 1 - import

On the toolbar, there are a number of tools for selecting points on your curve.

Engauge buttons

First, click the “Axis Point” tool. This will allow you to define the three points which establish the coordinate system for your chart. In the example below, I’ve set the Origin, and extreme points on the horizontal and vertical axes as my points. Always pick the extreme points on your axes, for greater accuracy. I also recommend zooming in (select zoom percent in the tool bar) when placing the points, to ensure accurate positioning. Remember to always check the actual coordinates of these points. You’ll notice that the vertical axis starts at a value of 1.0; this means that the Origin is a (0,1.0) and the furthest horizontal axis point is as (250,1.0). The red crosses in the photo below are the Axis Points.

step 2 - axis points

Next use the “Select Section” tool to select segments of the curve (as identified by Engauge), for quick filling of points. You can then use the Curve Point tool (button with the little blue crosses) to add extra points, and use the Arrow tool to relocate points (or select them for deletion). The blue crosses are selected data points:

step 3 - select segments and adjust points

Now at this point, it’s worth going to Settings >> Curves. This will allow you to Rename the curve which contains the datapoints that you’ve selected. It also lets you define new curves. You can later use the drop-down box in the toolbar to select different curve names, so that you can add points to those other curves. Doing so means that when you export your curve, the set of data points will be appropriately labelled, and you can export multiple curves from a single file.

Now click File >> Export File As >> then name your file and save it. I like to save as a .CSV file, for use in excel.

step 5 - CSV in excelNext create an X-Y scatter of the exported points, and add a trend-line (selecting the curve type appropriate, and adjusting to get a reasonable R² value. Right Click on trendline >> Format Trendline >> select the option to “display equation on chart” and “Display R-Squared value on chart”. Now you may notice that the equation is displayed in scientific notation with very few significant digits. This caught me out the first time, and gave me a final function that was WAY off the chart…

Low Resolution Equation

Next, right click on the trendline label text (equation and R²) >> Format Trendline Label. Under the “Number” tab, set the Category to “Number”, and adjust the number to show sufficient significant digits for each coefficient in the equation (for this equation I went 10, because there are some very small coefficients). If you don’t do this, you won’t have enough significant digits for your equation to work, and the results will be off by a significant margin.

Now you’ll have a formula in this general format: “y = 0.0000000000×5 – 0.0000000035×4 + 0.0000012276×3 – 0.0002226645×2 + 0.0219923943x + 0.7781145214”

Copy and paste that into a cell, delete the “y” and replace each “x” with a reference to the cell next to it and raise the exponent. i.e. “= 0*D3^5 – 0.0000000035*D3^4 + 0.0000012276*D3^3 – 0.0002226645*D3^2 + 0.0219923943*D3 + 0.7781145214”

This formula is your digitised graph. Congratulations! Type you should be able to change the value in your referenced cell, and the result will be pretty close to what you’d measure from the chart (even more accurate, if you’ve done it right).

Step 6 - forula in action

If people don’t trust your digitisation skills, you can set the background of the chart to No Fill, and lay it over the top of the original chart, and even plot on any key points of interest (i.e. a know X value and calculated Y value) to prove the fidelity of your equation.

You can also turn these formulas into User Defined Functions, for easy use within calculation sheets. For info on User Defined Functions in Excel, see my previous post here, or google the topic.

Excel Functions That Should Exist, and Making Your Own

Maybe other engineers have had different experiences, but I know that during my degree, Microsoft Excel became a very close and special friend to me. Sure, it’s not the most powerful software, but it can do any calculation that you could do on paper, and it will do it over and over and over again. This makes it great for iterative calculations.

God I love a good spreadsheet.

The problem with Excel is that, like most good pieces of software, it was written by a person. This means that it was written by exactly the kind of jerk who would deliberately and maliciously write the code in a way that slightly inconveniences me. Those guys are the worst.

Okay, maybe I’m exagerating. But there are certain functions in excel that aren’t quite right, or haven’t been followed through to their logical conclusion (although I’ll touch on the likely reasons for this in a moment). This post is about addressing these shortcomings, and writing functions that will work the way you want them to.

Writing User Defined Functions (UDF):

To write your UDF, hit   Alt + F11   to bring up the VBA (Visual Basic for Applications) window. Select a location to store the function. Your options are as follows:

  1. Worksheet
  2. Workbook
  3. A Module

locations for VBA code

Simply double click on the worksheet, workbook, or Module in which you want to insert the code. If you want to use a module, simple click INSERT – MODULE, as shown below. You can rename these modules whatever you want, and export them for use in other spreadsheets. I like modules, as they are easy ways of tidying up your code library within a spreadsheet

insert module

Note: Ensure that you are writing your code inside the right VBA Project. You’ll see in the panel on the left of the VBA window  that there is a VBA project for each spreadsheet open, as well as any Add In spreadsheets running in the background. So just select the worksheet, workbook or module within the appropriate project.

Now, the fun bit: Coding.

Simply start writing your function using the following:

Function Functionname(value1, value2, value3)

You can specify the variable type for the values given in brackets, e.g. value1 As Range, or you can leave it implicit.

Excel should completed the End Function statement for you, and you can start writing your code in between these statements:

functionResult=value1+value2+value3

Within your function, simply set the output value by using a line similar to:

Functionname = functionResult

Putting it all together, in this example, you are left with your User Defined Function, which should look something like this:

Function Functionname(value1, value2, value3)
functionResult = value1 + value2 + value3
Functionname = functionResult
End Function

To use this function just type in “=Functionname(1,2,5)” in a cell, to return the value “8”. You can refer to a cell for any (or all) of the inputs, and it will use the value inside that cell.

Once you’ve written your function, if it’s one that you’ll use again and again, you can copy and paste it into the VBA project for any new spreadsheet that you need to use it in. Alternatively, if you only need to use these spreadsheets on YOUR computer (or if you want the code stored somewhere handy for easy pasting into new spreadsheets without having to find the original), you can post the code into a module in a blank spreadsheet, and save that spreadsheet as an Add In (.XLAM format) (see image below), then activate the add-in. Once you’ve done this, any time you open up Excel your computer will open up the XLAM file and make any SUBs or FUNCTIONs in it available to you. (Note: if you send your spreadsheet to someone who doesn’t have your add-in, it won’t operate correctly as the code isn’t availble, so in that case it’s good to save the function in the WORKBOOK of that specific spreadsheet). I’ll do another post shortly about how to add it to the list of functions displayed when you pres the “fx” button, as well as how to show descriptions for the inputs of the function.

saving Excel Addin

As an example of where these custom Add Ins are useful, I have add-ins that I use at work for quick “back of the envelope” calculations (e.g. “how much power would the conveyor need to lift 20m?” or “what would the full load torque of that motor be?”), and since I generally have Excel open, I can simply use my LiftPower() or FLT() functions in excel to get a quick answer.

Example: Lookup

I’m sure many of you are familiar with the shortcomings of the excel Lookup functions (LOOKUP, VLOOKUP, HLOOKUP). Primarily these shortcomings are:

  • Formatting limitations (the lookup column must be sorted in ascending order)
  • Result limitations (minimal control over what to do if no exact match is found, or if multiple matches are found)

After looking at how these functions work, it’s obvious that the code was written so as to give a reasonably workable function, while minimising processing requirements. Since excel spreadsheets can get quite large, an older computer could get quite bogged down in running the multitude of comparisons required by the Lookup function, so it seems like they let it shortcut out of the code by exiting the loop and returning the result value as soon as the value in the next lookup cell is greater than the value being searched for. Presumably the decision was made that it was less likely that people would be bothered by having to sort their data in ascending order (for the lookup column) than they would be by the slowness caused by the spreadsheet running through excess code.

As for shortcomings in the outputs, this is a result of an assumed philosophy of keeping things simple. An increased number of options just increases the difficulty of using the function.

At the end of the day, anyone who is so into Excel that they aren’t entirely happy with how these functions operate, is probably into it enough to write their own User Defined Functions.

Thank the heavens for VBA. Can I get an “Amen!”?

In a fit of pique, I wrote my own lookup function, which I call Ulookup (Ultra Lookup). Depending on the option selected, the function will search the Lookup array and return one of the following types of result from the Results array:

  1. Get first exact value
  2. Get last exact value
  3. Get nearest value
  4. Get nearest value below
  5. Get nearest value over
  6. Return “” if not found
  7. Interpolate between two nearest results
  8. Sum of exact results

Here’s the code for my Ulookup (I’ll upload a link to the actual XLAM format add-in as an example, just as soon as I find somewhere to store it):

Function Ulookup(value, lookupArray As Range, resultsArray As Range, lookupType As Integer)

temp = “error”

Dim temp1, temp2, a, b, Ra, Rb As Long
Dim check As Boolean
check = False

Dim Rl, Cl, Rr, Cr, i As Integer

Rl = lookupArray.Rows.Count
Cl = lookupArray.Columns.Count
Rr = resultsArray.Rows.Count
Cr = resultsArray.Columns.Count

‘ lookupType is type of lookup. 0 = get first exact value, 1 = get last exact value, 2 = get nearest value, 3 = get nearest value below, 4 = get nearest value over, 5 = return “” if not found, 6 = interpolate

Select Case lookupType
Case 0 ‘ get first exact value
For i = 1 To Rl
If check = False Then
If lookupArray.Cells(i, 1).value = value Then
temp = resultsArray.Cells(i, 1)
check = True
End If
End If
Next
If temp = “error” Then
temp = “value not found”
End If

Case 1 ‘ get last exact value
For i = 1 To Rl
If lookupArray.Cells(i, 1).value = value Then
temp = resultsArray.Cells(i, 1)
End If
Next
If temp = “error” Then
temp = “value not found”
End If

Case 2 ‘ get nearest value
For i = 1 To Rl
If i = 1 Then
temp1 = Val(lookupArray.Cells(i, 1))
temp = resultsArray.Cells(i, 1)
Else
temp2 = Val(lookupArray.Cells(i, 1))
If Abs(temp2 – value) < Abs(temp1 – value) Then
temp1 = temp2
temp = resultsArray.Cells(i, 1)
End If
End If
Next

Case 3 ‘ get nearest value below
For i = 1 To Rl
If Val(lookupArray.Cells(i, 1)) <= value Then
If check Then
temp1 = Val(lookupArray.Cells(i, 1))
temp = resultsArray.Cells(i, 1)
Else
temp2 = Val(lookupArray.Cells(i, 1))
If Abs(temp2 – value) < Abs(temp1 – value) Then
temp1 = temp2
temp = resultsArray.Cells(i, 1)
End If
End If
End If
Next

Case 4 ‘get nearest value over
For i = 1 To Rl
If Val(lookupArray.Cells(i, 1)) >= value Then
If check Then
temp1 = Val(lookupArray.Cells(i, 1))
temp = resultsArray.Cells(i, 1)
Else
temp2 = Val(lookupArray.Cells(i, 1))
If Abs(temp2 – value) < Abs(temp1 – value) Then
temp1 = temp2
temp = resultsArray.Cells(i, 1)
End If
End If
End If
Next

Case 5 ‘linear interpolation
check = False
temp1 = 0
temp2 = 0
For i = 1 To Rl
If Val(lookupArray.Cells(i, 1)) <= value Then
If check Then
temp1 = Val(lookupArray.Cells(i, 1))
Ra = Val(resultsArray.Cells(i, 1))
Else
temp2 = Val(lookupArray.Cells(i, 1))
If Abs(temp2 – value) < Abs(temp1 – value) Then
temp1 = temp2
Ra = Val(resultsArray.Cells(i, 1))
End If
End If
End If
Next
a = temp1
check = False
temp1 = 0
temp2 = 0
For i = 1 To Rl
If Val(lookupArray.Cells(i, 1)) >= value Then
If check Then
temp1 = Val(lookupArray.Cells(i, 1))
Rb = Val(resultsArray.Cells(i, 1))
Else
temp2 = Val(lookupArray.Cells(i, 1))
If Abs(temp2 – value) < Abs(temp1 – value) Then
temp1 = temp2
Rb = Val(resultsArray.Cells(i, 1))
End If
End If
End If
Next
b = temp1
If a = b Then
temp = Ra
Else
temp = ((value – a) / (b – a)) * (Rb – Ra) + Ra
End If

Case 6 ‘return “” if not found
temp = “”
For i = 1 To Rl
If lookupArray.Cells(i, 1).value = value Then
temp = resultsArray.Cells(i, 1)
End If
Next

End Select

Ulookup = temp

End Function

555 Timer Calculator (Astable timer)

For a recent (incomplete, ongoing) project, I wanted to create a circuit to trigger my ultrasonic rangefinder, so I bought some cheap 555 IC chips. Now there’s a lot of info out there about basic 555 timing circuits, and how to select your resistors and capacitors to generate square wave outputs of various frequencies and duties… However some of the calculators out there don’t tell you how they generate the numbers, and some of the sets of equations are simplified, or not quite correct.

The theory behind a 555 chip’s use is pretty simple. Here are the typical voltage levels for the chip:

  • Vcc is normally 5V, this is both the chip supply voltage and output voltage. This can vary, but that’s fairly standard.
  • threshold voltage is normally 2/3 of Vcc (e.g. 3.33V)
  • Trigger voltage is normally 1/3 of Vcc (e.g. 1.67V)

Here’s a summary of how the chip functions (see the referenced data sheet for a good explanation and tables showing input states versus output states):

  • Reset pin must be HIGH
  • When threshold pin voltage exceeds threshold voltage, the Discharge Switch goes open (connecting the Discharge Pin to ground), the output goes LOW
  • When Trigger pin voltage drops below the Trigger voltage, the Discharge Switch goes closed (thus isolating the Discharge Pin), the output goes HIGH

What this means is that through the use of a capacitor and a couple of resistors you can create a timer circuit to for a number of functions:

  • delay OFF button press (quick charge the capacitor with positive terminal connected to VCC through resistor, and using the button to connect the capacitor to ground) – also useful for debouncing a button press
  • Generate a set frequency  square wave signal (Astable operation), determined by the size of capacitor and the values of two resistors. This is what my calculator is for.
  • to generate an adjustable duty cycle square wave signal (PWM) using a potentiometer in an Astable circuit with the centre pin connected to the discharge, one outer pin connected to Vcc and the other connected to the trigger and threshold pins.

There’s plenty of information on these and other circuits, so I won’t go into it here, just have a look at the Reference pages below, or do a web search.

So, as I said, the calculators available on a lot of websites are a bit… not good. Some are “black box” calculations, and some are just over simplified. I put together some of the standard calcs available online for Astable circuits, and added my own calculations to solve for Diode Adjusted circuits (which allows you to achieve a full duty range from 0% to 100%. Here’s the excel calculation, which includes circuit diagrams, graphs, equations and instructions:

555 Calculator

Note: I pinched the pictures from a website, and I can’t remember the address, so… Yeah. As one of the older guys I work with would say “use your eyes and plagiarise”

References:

example data sheet for 555 chip   –   http://www.ti.com/lit/ds/symlink/na555.pdf

example 555 circuits   –   http://electronicsclub.info/555timer.htm

example 555 circuits   –   http://www.555-timer-circuits.com/