Histogram with normal distribution overlay in Excel

This tutorial will walk you through plotting a histogram with Excel and then overlaying normal distribution bell-curve and showing average and standard-deviation lines.

To produce my random normal samples I used VBA function RandNormalDist by Mike Alexander. I created samples with a mean of 100 and standard deviation of 25, function RandNormalDist(100, 0.25).

The actual mean and standard deviation was 100.84 and 27.49 respectively.

Normally distribution

The samples can be checked to confirm normally distributed by comparing the mean, median and mode which should all be equal.

Mean 100.84
Median 99.5
Mode 89

Histogram

The first thing to do is produce the histogram. This is done by creating bins of a certain width and counting the frequency of the samples that fall in each bin.

Start by calculating the minimum (28) and maximum (184) and then the range (156). Using Sturges’ formula the number of bins is 9, using the square root method the number of bins is 15.

To get a bin width, divide the range (156) by the number of bins (9) which results in 17.33, round this up to an even 20 to produce nice round bin widths.

Set up the bins starting at the minimum and ending at the maximum, using the Excel FREQUENCY function to determine frequency in each bin. The FREQUENCY Function must be entered as an array (ctrl-enter).

Bin Frequency
0 0
20 0
40 1
60 12
80 32
100 56
120 51
140 28
160 19
180 0
200 1

Using a column chart a histogram can be produced.

Histogram using standard column chart

Histogram using Scatter Chart

Overlaying a normal curve is a little trickier, firstly, the above column chart can’t be used and the histogram must be produced using a scatter chart.

Select the data and produce a scatter chart with smooth lines.

Scatter chart as histogram

Select the chart and click on the ribbon menu, Layout, then Error Bars and then More Error Bars Options. Select Display Direction Minus, End Style No Cap and Error Amount Percentage 100%.

Scatter chart as histogram format error bars

This will produce a scatter chart with the following error bars.

Scatter chart as histogram with error bars

Increase the Line Style Width so that it starts looking like a histogram with no gaps.

Scatter chart as histogram using error bars

Finally, remove the scatter chart line.

Scatter chart as histogram using error bars finished

Normal Curve

For the normal curve the points need to be created first. The bell curve looks nice when it covers the full 6 standard deviations. 100 points will be created for a nice smooth curve.

Multiply the standard deviation (27.49) by 6 to get 164.96, divide by 100 to get an increment of 1.6496. Starting at minus 3 standard deviations (equal to the mean minus 3 standard deviations (18.36)) increment the value by 1.6496 all the way up to positive 3 standard deviations(183.32). There will actually be 101 total points.

Now for each of those points the normal distribution shall be calculated using Excel’s NORMDIST function. The first parameter is the values we calculated, the second the mean, the third the standard deviation and the last should be FALSE as we don’t want cumulative (NORMDIST(Q1,100.84,27.49,FALSE)).

If you plot the data you will notice a very short normal distribution curve, barely visible as a bell curve due to differences in scale. For our sample of 200 points with bin width of 20, each sample represents a square of 20 by 20. So the total area of our histogram is 200 by 20 which is 4000. The normal distribution has a total area of 1, so the normal curve must be scaled by 4000. And this produces a nice bell-shaped normal curve over the histogram.

Scatter chart as histogram with normal curve

Histogram correction

You may notice that the histogram and bell curve is a little out of sync, this is due to the way the bins widths and frequencies are plotted. If you have a bin width of 20, and the bin value is 40, the corresponding frequency is all values between 20 and 40. When you plot this value on a scatter chart, the centre of the bar is at 40 and the bar width being plus and minus half the bin width (10), which is 30 to 50 respectively. That’s why the histogram looks shifted to the right.

To fix this, create a temporary fixed bin that has half the bin width (10) subtracted from it and use this when plotting the histogram.

Bin Frequency Corrected Bin Frequency
0 0 -10 0
20 0 10 0
40 1 30 1
60 12 50 12
80 32 70 32
100 56 90 56
120 51 110 51
140 28 130 28
160 19 150 19
180 0 170 0
200 1 190 1

Scatter chart as histogram with normal curve corrected

Mean and Standard Deviation

Since it is a scatter chart, it is possible to add additional indicators including mean and standard deviation lines.

Simply produce a single line segment from 0 to the height of the bell curve using the previous NORMDIST function.

-3sigma 18.35943 0
18.35943 0.644784
-2sigma 45.85295 0
45.85295 7.855081
-1sigma 73.34648 0
73.34648 35.20403
mean 100.84 0
100.84 58.04164
1sigma 128.3335 0
128.3335 35.20403
-2sigma 155.827 0
155.827 7.855081
3sigma 183.3206 0
183.3206 0.644784

Scatter chart as histogram with normal curve, mean and standard deviation

Tidying up the colours results in the following final histogram with overlaid normal curve and mean and standard deviation indications.

Scatter chart as histogram with normal curve, mean and standard deviation final

Download the Excel workbook here.

Free copy of The Building Code of Australia (BCA)

What a free copy of the building code of Australia (BCA), the Australian Government has made it public.

In order to promote public education and public safety, equal justice for all, a better informed citizenry, the rule of law, world trade and world peace, this legal document is hereby made available on a noncommercial basis, as it is the right of all humans to know and speak the laws that govern them.

National Construction Code Series 2012 VOLUME ONE HTML
National Construction Code Series 2012 VOLUME TWO HTML
National Construction Code Series 2012 VOLUME ONE PDF
National Construction Code Series 2012 VOLUME TWO PDF

Automatically start and run LabVIEW VI

Here is an AutoIT script and executable that takes a LabVIEW VI path, opens the VI and then runs the VI.

Script and download below.

#include <File.au3>

If $CmdLine[0] > 0 Then

; assume first parmaeter is the file and path of the VI
Local $sFilePath = $CmdLine[1]

Local $sDrive = "", $sDir = "", $sFilename = "", $sExtension = ""
Local $aPathSplit = _PathSplit($sFilePath, $sDrive, $sDir, $sFilename, $sExtension)

; generate the window title
Local $sTitle = $sFilename & $sExtension & " Front Panel"

; run the VI
ShellExecute($sFilePath )

; wait till the VI opens
WinWaitActive($sTitle)

; send CTRL-R to start to RUN
Send("^r")

EndIf

Open and Run LabVIEW VI AutoIt
Open and Run LabVIEW VI EXE

Use as follows;

open-run-labiew-vi.exe "C:\Users\UserName\Desktop\VIName.vi"

The benefits of builder brokers

It is known that mortgage brokers and insurance brokers are useful to get the best possible service at the best possible price. I thought I would share my knowledge of builder brokers, and whether they can benefit those building a home or development.  Note this analysis and opinion is purely based on my knowledge and experience in Perth, Australia, and I have never actually engaged the services of a builder broker.

Similar to a mortgage and insurance broker, a builder broker claims that they can build a dwelling at the best price and terms. The argument is that by quoting on the same plans to various builders, since all the builders will be quoting on the same design, the most favourable price and terms can be selected. This is different to approaching builders individually, who will create their own designs and provide a cost, since all designs are different, it is difficult to compare like for like. Though, there is an advantage that each builder may produce a design that they can optimally construct due to their past knowledge.

Design

A builder broker will create the dwelling design, usually the development approval drawings, so that it can be submitted to council for approval. The builder broker may guarantee council approval, which is a definitely a good thing. The price of these drawings may be significantly less than getting an architect to do similar, with the builder broker reasoning that their return are from the builder on signing up, so it is in their interest to take it that far.

There are a few catches, the copyright of the drawings will belong to the broker builder and will be licensed only if you use their services.  Let’s say you have received development approval, paid your application fee, a few hundred or thousand dollars depending on the size, you are now at the mercy of the builder broker, if you don’t like their choice of builders, the subsequent quotes, etc, you can’t take your plans somewhere else. This leads on to the next point.

Conflict of Interest

There is a difference between a mortgage or insurance broker and a builder broker, the former can easily be compared by yourself, when you receive a loan from a mortgage broker it can be compared with any number of banks, all on similar terms and conditions. Similarly, with an insurance broker, the insurance premiums can all be easily compared. A builder broker, you don’t have a licence to use the copyright except if you chose to progress the build through the broker, with the builder broker arguing that this is how they make their fees. Understandably, this is true, but even throwing your own preference for builder can be met with backlash.

Firstly, though, let’s talk about the builder broker’s fee, besides the initial ‘foot in the door’ payment for producing the drawings, the bulk of the builder brokers fee is the commission from the builders. That is, a percentage of the building cost is given to the builder broker, let me say that again, the more you pay to the builder, the more the builder broker receives, how is that is your best interests. Which leads to my next point.

Transparency

The builder broker will cloud however they can the comparison between builders, in order to favour the quote that gives them the largest cut. Firstly, each builder may give different referral fees, depending on past relationship, etc, you will never know, and that is why they are hesitant to introduce builders that are not on their shortlist, they may need to build new relationships, or know that there is not much commission through them. Secondly, they summarise the quotes into a simple table, so you can easily compare, you may never see the quotes, and their summaries are arranged to lead you choosing a builder that the builder broker prefers. Thirdly, brokers argue that they are simply taking the commission that the sales person of the builders would normally take, this may be true if the broker has a good relationship with a builder, but again, there is no transparency, and unlike a sales person what incentive does the broker have to negotiate a lower price.

Alternatives

A better alternative to a builder broker is getting a draft person or architect to design the dwelling for you. Once council approved, then approach builders individually, yes, you will be dealing with the builder’s salesperson, but if you don’t like what one builder is offering, simply move to the next.

In fairness, don’t show one builder’s quote to other builders, instead give them a rough indication of how they are placed and allow them to adjust.

Log solar generated to PVOutput with LabVIEW

Here is the full LabVIEW code to read a CMS2000 inverter solar generation, including power, voltage and temperature, and then logging it to PVOutput.

The data is read from the CMS2000 every 1 second and averaged over 5 minutes, as PVOutput only supports 5 minute intervals. The averaged value gives a better representation of the parameter compared to a single read every 5 minutes.

Logged is power generated, inverter temperature, and DC voltage. PV Bean Counter only supports AC voltage which won’t vary too much, DC voltage is a lot more interesting, DC current can be calculated from the power and voltage values.

Don’t forget to change the API key and system ID strings.

Use the linked images below when dragging into LabVIEW as a snipped, not the thumbs.

Read CMS2000 inverter to PVOutput VI

Read CMS2000 inverter to PVOutput

Read CMS2000 inverter to PVOutput

Connect to CMS2000 solar inverter with LabVIEW and read power

After a few weeks of ‘chasing the sun’, finally got something I am happy with, LabVIEW connects to the CMS2000 via serial interface and reads the parameters.

Programmed as a state-machine, basic error checking and fail safes.

Note, I am using a RS232 to TCP/IP adapter, I am guessing a lot of the peculiarities are due to that.

Don’t use the thumb snippet, but the linked image.

Read CMS 2000 Inverter with LabVIEW

Read CMS2000 invert in LabVIEW VI

Phoenixtec (CMS2000) inverter protocols spreadsheet

DIN rail single phase two wire energy meter Lanx Australis LXEM180 manual

Here is the closest replication manual for the Lanx Australis LXEM180, which is available from Schnap

The manual is another rebadged version, operation is exactly the same.

DIN rail single phase two wire energy meter LXEM180-PRO2DM.pdf

LXEM180

AFS Walling Solutions – permanent formwork concrete walls review

Recently I did some research into the AFS Logic Wall product as an alternative to brick for a development I am working on.

AFS is a permanent formwork concrete wall solution, what this means is instead of creating the timber frame on site for the concrete pour, a factory creates a shell which is then assembled on site and filled with concrete.

The benefits are quicker wall construction times, days not weeks, as there is minimum site works. This initially seams like a huge benefit, but only after speaking to a number of industry people are the true costs discovered.

As a start, read through this AFS Assessment by the Ceramic Advisory Services, though the information may be outdated and biased.

I received the following information from an AFS distributor, April 2014;

Typically AFS150 would be supplied and installed and filled with concrete for approx $ 215/m2, ready for applied finishes.

Compared to a brick, the costs is fairly similar, especially if comparing to double brick. But there are some additional costs that are not accounted for.

From the distance, there looks like there is a huge advantage to reduced wall thicknesses, I was informed 150mm AFS could be used in place of my 230mm double brick. There are a couple of catches though, only electrical conduit can be set in the concrete, water pipes can’t and so a separate stud wall is needed, adding about 75mm and additional costs.

AFS wall have a specified insulation R value, though the wall is concrete only, if you can’t meet the required insulation then additional will be required. With double brick construction, the insulation can be installed in the cavity, there is no cavity with the AFS so again a separate stud wall is needed.

When you need a thin wall, brick is a minimum 90mm thick, whilst AFS is 110mm, possibly due to the cement formwork being 10mm in thickness on each side and unable to support any load.

The slab needs starter rebar for the formwork, whilst brick does not, again, adding additional costs.

Finally, there are only a handful of builders that work with AFS, in Perth I was given the names of four builders, severely restricting the choice.