Excel Generate Normalized Data

Question

I am looking to generate data in which Given two averages (Average Click) and (Average Clicks over Time) will correctly output spaced out data.

Hard to explain, but best with an example.

Given the following data:

Days: 10
Average Click Value: 3.5
Average Clicks over days: 0.7

You could then make data such as:

Day 1   
Day 2   
Day 3   4
Day 4   
Day 5   
Day 6   
Day 7   
Day 8   
Day 9   3
Day 10  

Where the average between Day 9 (3) and Day 3 (4) would be 3.5, and the total average (7 over 10 days) is 0.7.

---

The following would NOT work because it has a click value average of 3.5, but an overall average of 1.4:

Day 1   4
Day 2   
Day 3   3
Day 4   
Day 5   
Day 6   4
Day 7   
Day 8   
Day 9   3
Day 10  

The following would NOT work because it has an overall average of 0.7 but a click value of 1.75:

Day 1   1
Day 2   
Day 3   2
Day 4   
Day 5   3
Day 6   
Day 7   
Day 8   
Day 9   1
Day 10  

The click value must be a whole number, so the follow would NOT work:

Day 1   
Day 2   
Day 3   3.5
Day 4   
Day 5   
Day 6   
Day 7   
Day 8   
Day 9   3.5
Day 10  

The spacing between each should be rather random but follow a tight deviation. The pattern should be similar to how often a user visits their favorite website.

I understand it will sometimes not be exact because some combinations cannot be done mathematically, but it should attempt to be as close as it possibly can.

Is this type of pattern possible using excel (VBA can be used).

Answer 1

I think you can quite easily achieve that using the Solver. Make a model like the one you want, define your targets (the 2 averages) as formulae, then give it the range of data to solve. Nut you might well get the same answer every time, though. Give it a try.
Note that the Solver is an optional install, might not be visible if you made a standard install instead of a full one.

Answer 2

Here is a starter array function: you enter it vertically: select the number of rows = to the number of days, enter the function and press Ctrl-Shift-Enter. The degree of randomness is controlled by the Variation constant.

Function ClickSpacer(nDays As Long, ClickAvg As Double, ClicksPerDay As Double)

    Dim Spacing As Long
    Dim Clicks() As Long        ''' output
    Dim Total_Clicks As Double
    Dim nDaysClicked As Double
    Dim j As Long
    Dim ClicksSoFar As Long
    Dim RandSpacing As Long
    Dim RandClicks As Long
    Dim ClickOffset As Long
    ReDim Clicks(1 To nDays, 1 To 1)
    Const Variation As Double = 0.2

    Total_Clicks = Round(nDays * ClicksPerDay, 0)
    nDaysClicked = Round(Total_Clicks / ClickAvg, 0)

    Spacing = nDays / (nDaysClicked + 1)
    RandSpacing = Round(Spacing * Variation, 0) * 2
    ClickOffset = Spacing + Round(Rnd() * RandSpacing, 0) - Round(Rnd() * RandSpacing, 0)
    RandClicks = ClickAvg * Variation * 2

    For j = 1 To nDaysClicked
        If j > 1 Then ClickOffset = ClickOffset + Spacing + Round(Rnd() * RandSpacing, 0) - Round(Rnd() * RandSpacing, 0)
        If j = nDaysClicked Then
            Clicks(ClickOffset, 1) = Round((Total_Clicks - ClicksSoFar) / (nDaysClicked - j + 1), 0)
        Else
            Clicks(ClickOffset, 1) = Round((Total_Clicks - ClicksSoFar) / (nDaysClicked - j + 1) + (RandClicks * Rnd() - RandClicks * Rnd()), 0)
        End If
        ClicksSoFar = ClicksSoFar + Clicks(ClickOffset, 1)
    Next j

    ClickSpacer = Clicks
End Function