Google Sheets - Create a randomized list of numerical values (specified quantity) within a certain range (set numbers) that collectively equal a specific total

Here is an interesting challenge...

I am trying to find a way to generate a specific number of random values within a range that add up to a predetermined total in Google Sheets. For example, I want to create a list of 10 numbers that sum up to 100, with each number falling between the range of 8 and 12.

The desired output would look something like this:

8 9 12 11 8 9 11 11 9 12

If anyone knows how to set up a formula in GSheets or Excel to achieve this, where I can input the quantity of numbers needed and the target total, I would greatly appreciate your help! Thank you for your divine intervention!

javascriptarrays

Answer №1

Imagine your dilemma as picking marbles from a container without putting them back in. There are 120 marbles, with 12 of each among 10 different colors. Reserve 8 marbles of each color. Toss the remaining 40 marbles into a bag and draw 20 out randomly, combining them with the set-aside 80 marbles. You now possess 100 marbles, each color varying between 8 to 12 marbles. The distribution of numbers will not be evenly spread out. Look up "sampling without replacement" for the distribution formula.

For your particular scenario, input the following into A1:A10:

=FREQUENCY(INT((ROUND(MOD(SMALL(RANDBETWEEN(-1e12,SIGN(ROW(INDIRECT("1:40")))*1e12)+ROW(INDIRECT("1:40"))/100,ROW(INDIRECT("1:20"))),1),2)*100-1)/4)+1,ROW(INDIRECT("1:10")))+8

Finalize with Ctrl+Shift+Enter.

To make it more general, arrange your spreadsheet like this:

  • B1: the total count of random numbers needed (e.g., 10)
  • B2: the lowest value (e.g., 8)
  • B3: the highest value (e.g., 12)
  • B4: the desired sum (e.g., 100)
  • B5: =(B3-B2)*B1 (count of available slots)
  • B6: =CEILING.MATH(LOG10((B3-B2)*B1))
  • B7: =10^B6
  • B8: =B4-B1*B2 (slots to fill)

The formula for general use is:

=FREQUENCY(INT((ROUND(MOD(SMALL(RANDBETWEEN(-1e12,SIGN(ROW(INDIRECT("1:"&B5)))*1e12)+ROW(INDIRECT("1:"&B5))/B7,ROW(INDIRECT("1:"&B8))),1),B6)*B7-1)/(B3-B2))+1,ROW(INDIRECT("1:"&B1)))+B2

Recalculate the sheet to get a fresh set of numbers (F9 in Excel).

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Twice the charm, as the function `$("#id").ajaxStart(...)` is triggered twice within an AJAX request

I am trying to implement the following code: <script language="javascript"> function add(idautomobile,marque,model,couleur,type,puissance,GPS){ $("#notification").ajaxStart(function(){ $(this).empty().append("<center><br/><i ...

javascriptjqueryajax

A guide on setting up custom themes in Vue.js with TypeScript

I've been trying to update the color scheme of a Vue template (using vuetify) that I'm working with, but after spending hours on the documentation, I'm at a loss for what to do next. Here is my main file: //src/main.ts import '@babel/ ...

javascripttypescriptvue.jsvuetify.js

Is it possible for a browser to debug a JavaScript function automatically even if there are no errors present in the function?

While it's common knowledge that browsers can debug JavaScript functions that have errors, is there a way to debug a JavaScript function that doesn't have any errors? For instance, if I have a JavaScript function in my button control that runs wi ...

javascriptjqueryasp.net

When a React component written in TypeScript attempts to access its state, the object becomes

Throughout my app, I've been consistently using a basic color class: const Color = { [...] cardBackground: '#f8f8f8', sidebarBackground: '#eeeeee', viewportBackground: '#D8D8D8', [...] } export defau ...

javascriptreactjstypescriptwebpackreact-redux

Issue: appbridgeError: APP::ERROR::INVALID_CONFIG: shopOrigin is a required field and must be provided

While developing my Shopify App using Koa and Nextjs, I encountered the following error: appbridgeError: APP::ERROR::INVALID_CONFIG: shopOrigin must be provided The behavior of the app is a bit odd as it works fine when accessed for the first time. Howev ...

javascriptnode.jsreactjscookiesshopify

The Vue.js application is experiencing issues with displaying Google Maps functionalities

I have developed an application using Vue.js in Monaca and Cordova with onsenUI. My goal is to display my location on a Google map within the page. I attempted to achieve this by utilizing the npm package named vue2-google-maps, but unfortunately, it' ...

javascriptgoogle-mapscordovavue.jsmonaca

I am looking to retrieve data from the Graph API JSON and gradually refine my search to achieve successful

I am looking to retrieve data from the "fb_page_categories" endpoint, which provides an array of categories a page can be categorized under. The format for this request is as follows: GET graph.facebook.com /fb_page_categories? Once this request is mad ...

javascriptjsonfacebookfacebook-graph-apifacebook-fql

What is the best way to bind the value of total when working with forms and the bind method?

I am working on a form where I need to pass the value of total. Regarding the total: I have successfully passed the value of the cart, which is an array. const [total, setTotal] = useState<number | undefined>(undefined); const calculateTotal = () ...

javascriptreactjstypescriptformsnext.js

Extract list of string or integer values separated by commas from a JSON and convert them into an array

My current task involves extracting key/value pairs stored in JSON format. Sometimes, the values of these pairs can be arrays. Below is an example of such a JSON file: { "name":"abc", "address":"some address value", "phonenumber": "\"123123&bso ...

arraysjsongo

Guide on implementing asyncWithLDProvider from Launch Darkly in your Next.js application

Launch Darkly provides an example (https://github.com/launchdarkly/react-client-sdk/blob/main/examples/async-provider/src/client/index.js) showcasing how to use asyncWithLDProvider in a React project (as shown below). However, I'm struggling to integr ...

javascriptreactjsasynchronousnext.jslaunchdarkly

Using javascript to quickly change the background to a transparent color

I am in the process of designing a header for my website and I would like to make the background transparent automatically when the page loads using JavaScript. So far, I have attempted several methods but none seem to be working as desired. The CSS styles ...

javascripthtmlcss

Missing Values in jQuery Variable

I'm having trouble with adding a link after a block of text. Although the links render fine, the href tag seems to disappear. var eventstuff = data.text; var eventElement = $("<div class='well well-sm eventsWells'>"); var deleteButton ...

javascriptjqueryhtmlangularjs

What are the steps for integrating Socket.IO into NUXT 3?

I am in search of a solution to integrate Socket.IO with my Nuxt 3 application. My requirement is for the Nuxt app and the Socket.IO server to operate on the same port, and for the Socket.IO server to automatically initiate as soon as the Nuxt app is ready ...

javascripttypescriptsocket.ionuxtjs3nuxt3

"Can anyone provide guidance on how to initiate a css 3d animation by clicking a button

Currently, I am developing a folding hide/show animation that can be triggered using Javascript. If you would like to take a look at the code and see a working example, please visit this link: You can also view just the gist here: https://gist.github.com ...

javascriptjquerycssanimationwebkit

Is it possible to conceal the contents of a details tag without using a summary tag?

I'm looking for a way to hide the details tag without the summary. In my code, the summary is only visible when a condition [isvisible == false] is met. However, even when the summary is not visible, the details keyword is still shown and I want to hi ...

javascripthtmlcssvue.jscss-selectors

Verify whether any values in the resulting array match the values in the current array using CodeIgniter

I'm struggling to figure out how to display a month list view where months with deposits are green and months without deposits are red. I'm having difficulty comparing the array values, and I'm working with codeigniter. I have a table with ...

phparrayscodeignitercompare

Having trouble with Django's submit POST method for creating objects

Latest Updates: I have successfully implemented a feature where the page does not reload upon clicking the submit button. To achieve this, I filled out the form and inspected the page source code. The form structure was as follows: https://i.sstatic.net/ ...

javascriptdjangotwitter-bootstrap

Developing a feature to organize content by categories and implement a functionality to load more data efficiently using NodeJS routes

I am looking to develop a system that limits the number of displayed posts and includes a "load more" button to retrieve additional posts from where the limit was previously reached. Additionally, I want to implement the functionality to change the orderin ...

javascriptmysqlnode.jsformsejs

Is there a way to automatically collapse all the collapsible elements within the accordion when closing it?

I came across a similar topic on Stack Overflow about closing all children accordions when the parent accordion is closed, which seems to address my issue. Currently, I am using Bootstrap 4 and struggling to modify the code from the other topic to ensure ...

javascriptjquerybootstrap-4

Strategies for effectively handling errors in the requestAnimationFrame function

I'm currently facing issues with the animate() function, as it tends to crash my browser and cause my computer to heat up when errors occur. I attempted to use a try/catch handler to handle these errors but it did not work as expected. animate(){ ...

javascripterror-handlingthree.jsgsaprequestanimationframe