0

I have a table that looks like this:

Item # | Cost
Item 1 | 100
Item 2 | 150
Item 1 | 100
Item 3 | 166
Item 3 | 166
Item 4 | 140
Item 1 | 100

Is it possible to generate a random list of any of these items where the sum of their cost equals a fixed number?

alghsaleh
  • 3
  • 2
  • Excel may not be the best tool for this task. – radiocontrolled Mar 29 '16 at 21:05
  • @radiocontrolled You may be right, but row data comes in an excel sheet. Which tool would you suggest? – alghsaleh Mar 29 '16 at 22:39
  • You can use `=randbetween(bottom;top)` function to create random numbers. But if you use this function, every change you make in the sheet all the random numbers will be generated again so you'll have new numbers. – ctumturk Mar 30 '16 at 05:52
  • Here's a good discussion of the overall algorithm to use: http://stackoverflow.com/q/3696612/5090027. I'll elaborate for Excel with an answer below. – Grade 'Eh' Bacon Mar 30 '16 at 15:16

1 Answers1

0

As shown in this similar PHP question: How can I create a specified amount of random values that all equal up to a specified number in PHP?, as soon as you limit any random number based on the numbers around it, you are impacting the "true randomness" in some way. You need to determine what you want out of your answers - do you want each of the rows in your table to be weighted the same as each other row? With a total limit of 1-100, do you want the first value to have an equal chance at 99 and 20?

Only once you have determined which algorithm you want, can you actually code this in Excel.

My recommendation is that you use the algorithm proposed by Joe Blow in that linked question, which is as follows:

" (1) pick nine random numbers between 0 and 1000 (2) put the nine numbers, a zero, and a 1000, in an array (3) sort the array (4) using subtraction get the ten "distances" between array values. "

So in your case, put this formula in column A of your sheet, starting at A2 and dragging down:

=RANDBETWEEN(0,100)

In A1, above this list, put =0. Below your list, let's say A10, put =100.

Then in B1 and dragging down, put the following formula:

=SMALL($A$1:$A$10,ROW()+1)-SMALL($A$1:$A$10,ROW())

This will give you a list of numbers, which are all equally weighted, that total up to 100. You can then link this list in with your table.

Community
  • 1
  • 1
Grade 'Eh' Bacon
  • 3,773
  • 4
  • 24
  • 46