RE-submitting of: constructing (complex) variables with worksheet functions

[broer konijn]

I do not know how to re-submit a thread. I do not know if this is allowed either. Since it is an issue I have not been able to solve myself, I would like to ask once more for advice. If this is prohibited, my appologies upfront, but I take the risk since this is important for me.

It is a difficult issue (basically it are two issues) and if anyone can help me, perhaps with either one of the issues, it would really save me days of manual entering data (which is my alternative......)

Therefore again the issue. Do not hesitate to ask clearifying questions.

Many thanx again!

Previous post: 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!