Monthly Archives: May 2026

VBA

Moving average with VBA

We have sales data. For two weeks 13 and 14 sales increased clearly, but sales dropped to normal level after the peak. To see trend rather than sales for a separate week, we can use moving average. The moving average calculates the average from the last five weeks in our case.

For the week 5, we calculate the average between weeks 1 and 5. For the week 6, we calculate the average for the weeks from 2 to 6. So, we take average for the current week and four previous weeks. I have just counted with addition, but you could use also SUM or AVERAGE functions.

I created a line chart. As you see, the sales increased during the weeks 13 and 14, but we don’t have too high an increase in moving average. The average for the last week, week 18, is decreasing even though there is an increase from the previous week.

We can also create a macro to calculate the moving averages.

Sub c_mav()

ActiveCell.Offset(4, 1).Select

start:

ActiveCell.Offset(0, -1).Select

If ActiveCell.Value = “” Then

    MsgBox “Moving average calculated.”, vbOKOnly, “Moving averages”

    End

Else

    ActiveCell.Offset(0, 1).Select

End If

ActiveCell.Offset(-4, -1).Select

v1 = ActiveCell.Value

ActiveCell.Offset(1, 0).Select

v2 = ActiveCell.Value

ActiveCell.Offset(1, 0).Select

v3 = ActiveCell.Value

ActiveCell.Offset(1, 0).Select

v4 = ActiveCell.Value

ActiveCell.Offset(1, 0).Select

v5 = ActiveCell.Value

ActiveCell.Offset(0, 1).Select

mva = (v1 + v2 + v3 + v4 + v5) / 5

ActiveCell.Value = mva

ActiveCell.Offset(1, 0).Select

GoTo start

End Sub

Mav macro is first moving the cursor four rows down and one column to right. When you run the mav macro, place the cursor in the first cell of the data range, B3. Then I have IF sentence which checks if the next cell to left is empty. If we have reach end of the data range, then the macro should stop there. If that is not the case, then the macro is going one cell to right. The macro is going four rows up and one column left and capturing the value of that cell in variable v1. Then macro steps one row down and the value of that cell is v2. This continues till macro has taken five values one under the other. Then macro takes one step left. Macro calculates the sum of from v1 to v5 and divides that with five. That value is entered into the active cell. Then macro steps one row down and one column left. Macro is facing again the IF sentence.

Sub c_mav2()

ActiveCell.Offset(4, 1).Select

start:

ActiveCell.Offset(0, -1).Select

If ActiveCell.Value = “” Then

    MsgBox “Moving average calculated.”, vbOKOnly, “Moving averages”

    End

Else

    ActiveCell.Offset(0, 1).Select

End If

    ActiveCell.FormulaR1C1 = _

        “=(R[-4]C[-1]+R[-3]C[-1]+R[-2]C[-1]+R[-1]C[-1]+RC[-1])/5”

ActiveCell.Offset(1, 0).Select

GoTo start

End Sub

Mav2 macro starts from the first cell of the data range. Then macro takes four steps down and one step to right. The macro moves one column to left, if that cell is empty, then macro ends, if the cell left is not empty, then macro takes one step right. In that cell Excel macro creates a relative formula, four rows up and one column left plus three rows up and one column left plus two rows up and one column left plus one row up and one column left plus one column left divided by five. Then macro moves one row down and one column left. After that macro faces IF sentence again.  

If you calculate running average for five values repeatedly, a macro might be the fastest way. On the other hand, macro is doing just one thing. If you need to do something else, macro needs to be rewritten. The first macro enters the results of calculations in the cells. The second macro is creating the formulas in each cell. The Excel calculates the values normally based on the formulas which macro created.

Access, Graphics

Stacked area graph for cash profile

Here we have data about cash level in a bank account. Then we have defined high and low limit for good liquidity. The value of cash account should not fall below low limit and should not exceed high limit.

Just note, as we use stacked areas, the upper limit will be 100 + 500, not 500. If you would like the high limit to be 500, change the cell C2 to 400. Our low limit is 100 and high limit is 600.

We need to have a graph to show if our cash balance is in between the limits. Then we can visually follow the cash balance and limits.

Let’s start creating the graph.

Activate the area, select insert – chart, choose combo.

For the low limit, select stacked area. That is the area chart in the middle.

Select also the chart type for high limit for stacked area.

The graph looks like this. It is not yet presentable. Check that both limits have chart type stacked area.

Activate the lower area, press right mouse button and press fill.

Select no fill, as we want the area to be transparent. Only the area between the limits is colored.

This looks already better, but it needs still some finetuning. The background is too dark.

Press right mouse button, select fill and choose a lighter color.

For me, a light background color looks better.

We could still make background color less striking.

Press right mouse button on top of the background, press fill, select gradient and more gradients.

I set transparency for 80 %.

Now the graph looks even better.

I removed the labels for high and low limits.

We can see visually that cash dropped twice during the period considered below the low limit. The cash line is not in the light-yellow area. Both times cash was just below the limit. 

Now we are ready for the management meeting to present cash profile.