Hi. I have a dataset that, in part, contains a lot of rows that give Min, Mode (most likely) and Max values for a particular phenomenon, as follows;

Min Mode Max 1 2 10 3 7 20 2 13 19 5 22 60 Etc

For a simple simulation I’m doing, I need to generate, for each row, a value that lies between the Min and the Max (so RANDBETWEEN is fine), but is then adjusted in some way to take the Mode into account.

If I dont do this adjustment, the random value for each row would, over multiple runs, average out at the mean. But the given mode rarely lies at that point (it generally but not always lies somewhere to the left of the mean. I summed each of the Min, Mode and Max columns and the sum of the Modes is about a third of the way from the sum of the Mins and two thirds from the sum of the Maxs)

I also don’t want to just use the Mode in my simulation, as I want to have the possibility that big or small values (ie close to the Max or Min) will fairly routinely feature on particular rows, as I know they replicates the real world situation I’m simulating.

What do people think a sensible formula would be in this situation?