I spent most of today working with an Excel spreadsheet. I don't know whether I have been spending too much time with Numbers, but looking at all those white rectangles with grey lines was just too much for me today. I wanted something that looked a little better than the usual spreadsheet.

It's amazing what a difference just turning off the gridlines can make; the spreadsheets just look so much more elegant and professional. The only problem is that standard formatting for excel has the text essentially equally spaced between the cells and within the cells. My spreadsheet had word wrapping switched on so some of the cells had one row of text, some had two, and some had more. Without the gridlines it was really difficult to tell where one cell ended and another began. I needed a little padding between the text in different cells to complete my little makeover.

It's not perfect but I wrote a little excel macro to help me with this. It looks down the first column in the spreadsheet and checks if each cell contains a value. If it does, it adds a little padding to the cell, and then carries on to the next until it reaches a cell which doesn't contain anything. If it is useful to you, here is my script, hopefully someone might find it useful.

Sub AddRowPadding()
Dim i As Integer
i = 1
Do While i < 32000
If IsEmpty(Cells(i, 3).Value) Then
Exit Do
End If
startingHeight = Rows(i).RowHeight
Rows(i).RowHeight = startingHeight + 10
i = i + 1
Loop
End Sub

Tags: Quick tips, Excel, Visual basic

Monte-Carlo analysis is one of those tools that I have always found useful to keep in my pocket as an engineer. I like to find an elegant solution to problems limiting the number of variables and amount of analysis that has to be done. Sometimes a brute force approach can not however be avoided and Monte Carlo analysis is a very effective way of solving a complex risk or probability problem.

Because I don't use it much specialist tools such as @risk are not really an option for me. The basic version of Excel is however more than capable of performing Monte-Carlo analysis with relative simplicity.

The first stage is to build your model with the usual input and output parameters using the same format as you would normally do. Once the is done create a new worksheet for the input parameters that you want to use in he Monte-Carlo analysis. To generate the random numbers to put into the Monte-Carlo analysis you will need to use a Rand() function for each input parameter you want to consider. You can then use Excel's built in distribution functions to generate your output value or you can create your own - I will consider some of the different distribution functions in later posts.

If you have done this correctly, every time you amend your spreadsheet now the values should change on this worksheet. Now link the input values on your model worksheet to the values on the inputs worksheet. The entire model should now change each time values are adjusted on the spreadsheet. At this point it is worth taking time to check how your model is performing. Just pressing the delete key on an empty cell should run one Monte-Carlo step. It is worth doing this a few times because Monte-Carlo analyses often test the limit of a spreadsheet with combinations of very high and very low values.

It is possible to manually run a Monte-Carlo analysis manually with the spreadsheet in this form. The power of this solution is however when it is automated, and to do that we can make use of a Macro.

First create a new worksheet to hold the outputs from the model. Now create a new Macro. The typical code that should be used for the Macro is something like:

Sub MonteCarlo()
With Application
.Calculation = xlManual
End With
For Counter = 1 To 500
Sheets("Model").Select
Calculate
Output1 = Range("L3").Value
Output2 = Range("H17").Value
Set curCell = Worksheets("Results").Cells(Counter, 2)
curCell.Value = Counter
Set curCell = Worksheets("Results").Cells(Counter, 3)
curCell.Value = Output1
Set curCell = Worksheets("Results").Cells(Counter, 4)
curCell.Value = Output2
Next Counter
With Application
.Calculation = xlAutomatic
End With
End Sub

This Macro runs the Monte-Carlo analysis 500 times. The results from the analysis are taken from cells L3 and H17 from the worksheet Model. They are then put into columns 3 and 4 of the worksheet Results.

Post processing of the output data should be done in a separate workbook. If this is not done then re-running the Monte-Carlo analysis will take much longer as the post processing will be done for each step of the Monte-Carlo analysis.

Tags: Analysis, Excel, Macro, Monte Carlo, Risk, Spreadsheets, Statistics