Random Values totaling Cell value
 

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:


--
inkpassion
------------------------------------------------------------------------
inkpassion's Profile: http://www.excelforum.com/member.php...o&userid=16972
View this thread: http://www.excelforum.com/showthread...hreadid=401757

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 numbers
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 ?




inkpassion Wrote:
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:


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=401757

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"inkpassion" wrote
in message ...

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:


--
inkpassion
------------------------------------------------------------------------
inkpassion's Profile:
http://www.excelforum.com/member.php...o&userid=16972
View this thread: http://www.excelforum.com/showthread...hreadid=401757

After the number is input in A2, if you wish to regenerate the random
numbers in col C, just press the F9 key
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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.


--
inkpassion
------------------------------------------------------------------------
inkpassion's Profile: http://www.excelforum.com/member.php...o&userid=16972
View this thread: http://www.excelforum.com/showthread...hreadid=401757

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"inkpassion" wrote
in message ...

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.


--
inkpassion
------------------------------------------------------------------------
inkpassion's Profile:
http://www.excelforum.com/member.php...o&userid=16972
View this thread: http://www.excelforum.com/showthread...hreadid=401757