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?

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 ?

Originally Posted by 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?

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" <inkpassion.1utj6b_1125810307.0949@excelforum-nospam.com> wrote
in message news:inkpassion.1utj6b_1125810307.0949@excelforum-nospam.com...
>
> 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?
>
>
>
> --
> 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.

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" <inkpassion.1uyuui_1126058733.6232@excelforum-nospam.com> wrote
in message news:inkpassion.1uyuui_1126058733.6232@excelforum-nospam.com...
>
> 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
>