Random Values totaling Cell value

I

inkpassion

What Im looking to do is have 2 cells 1 with the number of random
numbers and 2 the total value of these numbers. I need the random
numbers to be 1-6.

Something like this:

Coins Value
4 10

C1 C2 C3 C4
1 4 3 2


What would the formula be to accomplish this?
:confused:
 
B

Bryan Hessey

In cells C1 to C4 set

=INT(RAND()*6+1)

In the 'count' cell

=COUNT(C1:C4)
(this would always appear to be 4)

In the sum cell,

=SUM(C1:C4)

Did you intend the 'count' cell to be a count of the variety of number
given ?
(ie, for a 1, 2, 3, 3 selection to show 3)
or did you intend that cells C1 to C4 all have different 'random
numbers ?
 
M

Max

One quick set-up to try ..

Assume A1:B1 contain the labels: Coins, Value
Input the number of coins in A2, say enter: 4

Put in B2: =SUM(C:C)
Put in C1:
=IF(OR($A$2="",ROWS($A$1:A1)>$A$2),"",RANDBETWEEN(1,6))
Copy C1 down to say, C20, to cover the max expected number (of coins)
that will be input in A2

The above will give you the set-up that you're after.

B2 provides the sum of the random numbers generated in col C, while the
number of cells "activated" in col C will be dependent on the input in A2

Note that RANDBETWEEN requires the Analysis Toolpak
to be installed and activated.

Check the "Analysis Toolpak" box (via Tools > Add-Ins)

Chip Pearson's page has details on the ATP at:
http://www.cpearson.com/excel/ATP.htm

Alternatively, we could replace RANDBETWEEN(1,6) in the formula
with: INT(RAND()*6+1)
(as suggested by Bryan)

i.e. put instead in C1:
=IF(OR($A$2="",ROWS($A$1:A1)>$A$2),"",INT(RAND()*6+1))
and copy down to C20 as before
 
M

Max

After the number is input in A2, if you wish to regenerate the random
numbers in col C, just press the F9 key
 
I

inkpassion

You got me on a good start but I didnt make myself clear (sorry!)

What I need is the random numbers to total a value entered by a user.
So In this game I will need 5 coins that add up to 20. I enter in the
20points spread over 5 coins and then it random outputs 5 numbers that
total up to 20.
 
M

Max

I'm not sure there's a way to fulfil this directly,
but try this slight variation to the earlier construct ..
(link to a sample file is provided below)

Instead of the formula in B2,
enter the target value in B2, e.g.: 20

Put a label in say, A4: Target reached?
Put in A5: =IF(SUM(C:C)=B2,"Yes!!","")

The Gameplay will go along these lines
--------------------------------------------
a. Enter the number of coins and the target value in A2:B2.

b. Tap F9 key to regenerate the random values in col C until a "Yes!!"
appears in A5, signalling target value reached

c. Freeze the results of the random values in col C elsewhere with a copy >
paste special > values > ok

Here's a link to a sample file with the construct to play with:
http://www.savefile.com/files/5917225
File: Random Values Totalling Cell Value _inkpassion_misc.xls
 
Top