Calc: progressive variation of data


firstly I shall say English isn’t my native language, so forgive me if I say inaccuracies.

I’ll try explaining what my goal is. I have 50 “blocks” of 8 rows of data each. Each block looks like the following:

Block 1

  1. 42 [random between 40 and 48]
  2. 45 [random between 40 and 48]
  3. 45 [random between 40 and 48]
  4. 46 [random between 40 and 48]
  5. 44 [random between 38 and 45]
  6. 42 [random between 38 and 45]
  7. 38 [random between 36 and 42]
  8. 37 [random between 34 and 42]

Block 50

  1. 92 [random between 80 and 100]
  2. 88 [random between 80 and 100]
  3. 91 [random between 75 and 95]
  4. 78 [random between 60 and 90]
  5. 50 [random between 48 and 64]
  6. 29 [random between 29 and 50]
  7. 13 [random between 10 and 17]
  8. 09 [random between 01 and 10]

Each row has to be a number randomly selected within a certain range, as in the example above, BUT, I need that range to be slightly, and progressively different not only between rows of the same blocks, but also for each of the 50 blocks, and I am looking for a way not to do it manually, because I already know that this growth curve will need to be adjusted multiple times as I’ll check the data that progressively comes out. I can’t just adjust that row by row for all the blocks…

So, in general, data in higher rows has to be higher than the last rows of each block. When I get to block 50, I need this “trend” to be maximised, this means that data in the first rows of block 50 needs to be, on average (remember it’s randomised data) higher than what I got on the first rows of the first blocks, but data on the last rows needs to follow the opposite patterns, namely it has to progressively shrink, so that the gap gets wider.

I’d like to be able to adjust both 1) the turning point of where data grows and shrinks (in other words, which row), and 2) the coefficient that defines the range of the random selection. This in a way that can save me from programming each line at a time.

I am no expert in Calc, as you probably can tell, and I realise this might be a bit complicated. I apologise in advance. Please let me know if I can clarify something better.

Thank you.

The phrases “a bit higher”, “statistically even across all rows” and “sensibly smaller than” elude from a precise mathematical definition.

Sorry not to have made that clear. Of course by “how much” that curve is supposed to change across blocks is also something I’d like to be able to control by adjusting some coefficient.

Without any mathematical idea (or sample data), I’m really curious, if anybody dares to answer.

“I need Calc to return a random value comprised within a range, that I can easily adjust”. It’s a very general question, yet the answer is simply “Put your range limits in A1 and A2, then =RANDBETWEEN(A1;A2)”. Does this give the idea? That’s the kind of answer I was hoping to get: a general formula, apart from exact numbers. If it’s not feasible, I’ll try put some concrete numbers as an example, but I don’t know in advance which shape the variation curve is going to take exactly. That’s why I need a coefficient that I can easily adjust and check the data back. I’ll edit the post.