Excel Power Users Training Courses

 The Random Number Generation tool creates sets of random numbers that are not uniformly distributed.  

Calculating Moving Averages 

A moving average is a forecasting technique that simplifies trend analysis by smoothing fluctuations that occur in measurements taken over time.  

These fluctuations can be caused by random noise that is often a by-product of the measurement technique. For example, measurements of the height of a growing child will vary with the accuracy of the ruler and whether the child is standing straight or slouching.  

You can take a series of measurements, however, and smooth them over time, resulting in a curve that reflects the child's actual growth rate.  

Fluctuations in measurements can result from other temporary conditions that introduce bias. Monthly sales, for example, might vary with the number of working days in the month or the absence of a star salesperson who takes a holiday. 

Correlations 

Overview 

How are monthly stock returns for Microsoft, GE, Intel, GM, and Cisco related? 

Trend curves are a great help in understanding how two variables are related. Often, however, we need to understand how more than two variables are related.  

Looking at the correlation between any pair of variables can provide insights into how multiple variables move up and down in value together. 

The correlation (usually denoted by r) between two variables (call them x and y) is a unit-free measure of the strength of the linear relationship between x and y.  

The correlation between any two variables is always between –1 and +1. Although the exact formula used to compute the correlation between two variables isn’tvery important, being able to interpret the correlation between the variables is. 

A correlation near +1 means that xand yhave a strong positive linear relationship. That is, when x is larger than average, y tends to be larger than average, and when x is smaller than average, y also tends to be smaller than average.  

When a straight line is applied to the data, there will be a straight line with a positive slope that does a good job of fitting the points. As an example, for the data shown below (x=units produced and y=monthly productioncost), xand yhave a correlation of +0.90. 

Summary of Correlation 

Determining the Correlation Between Data 

Adding Controls 

To add a control to your worksheet, simply click the relevant icon on the form’sdrop-down, and by clicking and dragging, draw the control onto the sheet. 

Once placed on the screen, you will see that it has handles at each edge and corner, allowing you to resize it as required. 

Understanding Controls and How They Work 

All controls must have a “cell link”.  This can be any cell on the worksheet (that would generally be hidden away) that is used to store the value returned by the control.  For example, if cell B2 is used as the cell link for a tick box, the value TRUE or FALSE would be returned to cell B2 depending on whether or not the box is ticked. 

Once there is a value in B2 (in this example, either True or False), you can then use that result for a calculation, function, etc. 

e.g. 

=IF(B2=True,”Yes I will attend”,”No I won’t be attending”) 

Alternatively, if you are using a Spinner or Scrollbar to change values from 10 to 100, and using C3 as the cell link, you could use the value returned in a calculation. 

e.g. 

=C3*60 

=C3*10 

=C3*100 

All controls have their own properties, which will be covered in this guide.  All controls have the ‘Cell Link’ property, which needs to be set if the result is to be used further within the spreadsheet. 

Group Box and Option Buttons 

From the Insert > Form Controls, click . 

Draw a Group Box onto the spreadsheet.  Highlight the heading and rename it to “April Fixtures”. 

Simply highlight the heading and overtype.  Alternatively, just delete to have no heading and just a box. 

From the Insert > Form Controls, click . 

Draw the Option Button inside the Group Box.  Highlight the text and type “Tottenham Hotspurs”. 

Add 5 more Option Buttons, and rename them “Arsenal”, “Man Utd”, “Liverpool”, “Chelsea”, and “Aston Villa”. 

Resize the Group Box if necessary (by clicking one of the borders and using the handles), to fit in the other Option Buttons. 

Click on the CONTROL tab.  What you can see here is the properties of the Option Button control. 

Click in the CELL LINK box, then click an empty cell behind in the spreadsheet.  In the example below, cell F1 has been selected.