Randomise Column Value

CLIENT

Canada

OBJECTIVE

Client needed help coming up with a random value for over a 1,000 spreadsheet rows. The random value had to fall within a specific range given the value of a letter selected in a different cell.

e.g.

A = 6.1 - 6.5
B = 6.6 - 7.1
C = 7.2 - 7.7
...

The objective was to completely automate the process so immediately after a letter was selected, the random number would appear in the next cell. This would save the client from manually calculating the random value each time, saving precious time and energy.

PROCESS

Tools Used

For this particular project I could have tried using formulas in Google Sheets such as RANDBETWEEN with SWITCH to create a random number based on the letter value. However, I realised that when using a formula such as RANDBETWEEN the random value will actually change when the spreadsheet recalculates and is not permanent.

This is not what the client needed so instead I created a script using Google Apps Script that would check for any changes to the spreadsheet. If there was a change in column “D” (where the letter was selected) it would then take that letter value and return a permanent random value within that letter’s range to the adjacent cell.

RESULTS

The random value script in action

The script allowed the client to easily create random values, given a set range, and greatly reduce the time and energy needed to complete this job. It enabled the client to focus instead on their core business instead of a manual task.

FEEDBACK

I would highly recommend working with David if you are looking to create google sheet formula’s to simplify your data and processes. He has a keen eye for detail, and is an expert in google sheets. Highly recommended 5⭐️+++

Andrew Brunner
Founder