-1

Suppose several dices (3 for example) thrown each time. It also could be more than six possible outcomes per "dice", but I took six for better illustration.

enter image description here

1). Columns E or G:

Lookback is simply the size of an array. Arrey should include only unique values and ignore zero values. The tricky thing is that the series of observations are sorted from oldest to newest, and values of an array must be updated based on the newest series of 3 numbers (largest row number in the selected range).

So the parameters of a function should include (array range, max value, array size).

What I need to do is simply to take all values from 1 to 'max value' (1,2,3,...) and subtract all values from an array. In other words, take only those values, which are not included in array for a given range. Finally, type them in ascending order using comma delimiter.

2). Columns D or F:

Here we take any particular range of values, and compare it with our comma delimited list. If there is a match, then type matched numbers similarly using comma delimiter.

ArthurV
  • 113
  • 2
  • 8
  • What is your question? – Kharoof Dec 27 '15 at 16:49
  • Kharoof My questions are: How can I maintain the fixed array of last 4 or 5 unique values? Considering that it should analysed based on the following principle in my example: C5, B5, A5, C4, B4, A4, and so on. Last 4 or 5 unique values in the given range – ArthurV Dec 27 '15 at 17:02
  • What is the definition of lookback and what's the difference between col e and g? What do you use col d and f for? – Kharoof Dec 27 '15 at 17:08
  • After, we take all numbers beween 1 and max value and answer this question: Which of them we don't have in our array? The result should look like in cell E3, G3, for a range A2:C3 – ArthurV Dec 27 '15 at 17:10
  • lookback - size of my array, which is number of 'n' unique values, appeared last in the given range – ArthurV Dec 27 '15 at 17:13
  • Range A2:C3 for E3 and G3. E3 takes last 4 unique values, G3 takes last 5. Then, since max value is 6, we have 1,2,3,4,5,6. Which of them are not in that list? 2 & 5 for E3; 5 for G3 – ArthurV Dec 27 '15 at 17:18
  • Please make an attempt to solve the problem, and we will be happy to help you take it to a good end. – A.S.H Dec 27 '15 at 17:51

1 Answers1

0

I suggest splitting out a lookup table in col h to m with 1,2,3,4,5,6... Across the top in h1 to m1 then in each row you can do a hlookup( h1, a3:c3, 1,false) in cell h3 to m3. This will return either a number or error, you could further wrap this function in an if function if(iserror(hlookup...),h1, ""). This would give you a row of numbers that it does not find in your dice roll which you could concatenate to get what your looking for.

Kharoof
  • 597
  • 1
  • 6
  • 21
  • Max value could be large enough, and I need udf solution – ArthurV Dec 27 '15 at 17:27
  • Excel 2007+ has 36k columns. Are you sure you can't build a matrix like this? – Kharoof Dec 27 '15 at 17:29
  • It's not comforable, since there will be hundreds of different max values to test.. I would need to create a new matrix each time.. Therefore, I need to take max value as a parameter in my udf funcion – ArthurV Dec 27 '15 at 17:35
  • What UDF have you tried. It's the same logic you would need to use. Post some snippets of code. – Kharoof Dec 27 '15 at 17:43