constructing (complex) variables with worksheet functions

Perhaps the worksheet function experts can advise me on the issue below?

Thanx!
---


Dear all,

I am preparing a spreadsheet in order to perform some analysis (i.e. OLS regressions). I use independent variables to explain one dependent variable. I tried to construct the independent variables myself, but I cannot figure out how to build them with worksheet functions. My difficulties for two variables are attached as simplified examples and illustrate the issue.

I would prefer solving these issues with worksheet functions, although my several attempts get stuck at certain the points in both issues:

-In the 'similarity of previous purchases' (issue 2): how do I manage to 1) observe the most frequent occurring code, 2) remember this code and calculate the ratio with this code at any point in time?

-In the 'average time' (issue 2): when a F4-locked starting cell of a moving average should be unlocked, given a certain criterium and re-lock again at a new place in the spreadsheet.

I know, the spreadsheet is perhaps more clear....Thanx in advance for all attempts!