# How to create a user defined function that maps one value to another in Google Sheets

I would like to create a mapping function that takes as an input 3 arrays of the same length and a double between 0 and 1.

• Array #1: Each element is the minimum value of the nth bin.
• Array #2: Each element is the maximum value of the nth bin.
• Array #3: Each element is the maximum value (0,1) that yields a value in the nth bin.

I want a random number between 0 and 1 (the 4th input) to go into the function. The output should be a number between the minimum value of the 1st bin and maximum value of the last bin.

• Bin 1: 0 to 1 (10%)
• Bin 2: 1 to 2 (30%)
• Bin 3: 2 to 5 (50%)
• Bin 4: 5 to 7 (90%)
• Bin 5: 7 to 10 (100%)

If the random number is <=0.1, the number returned should be from bin 1, if it's >0.1 and <0.3, the number should be returned between 1 and 2, so on and so forth.

Within bins, I'd like to have an exponential decay function (e.g. it's not linearly determined by the random input so 0.05 which is the middle of bin 1 would not yield a 0.5. So an input of 0.5 should yield an output of 5.0 while an input of .9 should be 7, and .91 should be really close to 7 and .99 should be close to 10, but .91 should be much closer to 7 than .99 is to 10.