random number with text

[T Harris]

This will be easy for most but not me. Here goes:

I want Excel to simply generate a random number between 1 and 75 ( I know
how to do this part) like in BINGO, if the number is between 1 and 15, show
"B15" if 15 was to be generated. Since I in BINGO contains the range
16 -30, then I need any number that occurs randomly between 16 and 30 to
show as "I23" if 23 was the generated random number. N contains the range
31-45, etc.,

I can do it manually now and just add the correct letter myself but that
takes the fun out of it. Can anybody tell me what I do. I didn't even know
the name of the feature I needed to look up to get help on. Thanks.

T Harris

[JE McGimpsey]

It's not easy, mostly because it's difficult to get the same random
number.

If you're willing to use two columns, it's easy:

A1: =MID("BINGO",INT(B1/15)+1,1)
B1: =INT(RAND()*75)+1

In article <ug0q84ECGHA.3920@tk2msftngp13.phx.gbl>,
"T Harris" <tdh1967@bellsouth.net> wrote:

> This will be easy for most but not me. Here goes:
>
> I want Excel to simply generate a random number between 1 and 75 ( I know
> how to do this part) like in BINGO, if the number is between 1 and 15, show
> "B15" if 15 was to be generated. Since I in BINGO contains the range
> 16 -30, then I need any number that occurs randomly between 16 and 30 to
> show as "I23" if 23 was the generated random number. N contains the range
> 31-45, etc.,
>
> I can do it manually now and just add the correct letter myself but that
> takes the fun out of it. Can anybody tell me what I do. I didn't even know
> the name of the feature I needed to look up to get help on. Thanks.
>
> T Harris

[Biff]

Hi!

Depends on how you want to do it.

Assume your random number is displayed in cell B1.

Enter this formula in A1:

=IF(B1<1,"",LOOKUP(B1,{0;16;31;46;61},{"B";"I";"N";"G";"O"}))

Or, you can create a little 2 column table somewhere:

0..........B
16........I
31........N
46........G
61........O

Then use one of these:

=IF(B1<1,"",LOOKUP(B1,C1:C5,D1:D5))

=IF(B1<1,"",VLOOKUP(B1,C1:D5,2))

Or, you can use any one of the above formulas like this:

=IF(B1<1,"",LOOKUP(B1,{0;16;31;46;61},{"B";"I";"N";"G";"O"}))&" "&B1

To get the letter and number together:

B 11

Biff

"T Harris" <tdh1967@bellsouth.net> wrote in message
news:ug0q84ECGHA.3920@tk2msftngp13.phx.gbl...
> This will be easy for most but not me. Here goes:
>
> I want Excel to simply generate a random number between 1 and 75 ( I know
> how to do this part) like in BINGO, if the number is between 1 and 15,
> show "B15" if 15 was to be generated. Since I in BINGO contains the range
> 16 -30, then I need any number that occurs randomly between 16 and 30 to
> show as "I23" if 23 was the generated random number. N contains the range
> 31-45, etc.,
>
> I can do it manually now and just add the correct letter myself but that
> takes the fun out of it. Can anybody tell me what I do. I didn't even
> know the name of the feature I needed to look up to get help on. Thanks.
>
> T Harris
>

[Aladin Akyurek]

=LOOKUP(A2,{0,1,16,31,46},{"","B","I","N","Q"})&A2

where A2 houses a (whole) number.

T Harris wrote:
> This will be easy for most but not me. Here goes:
>
> I want Excel to simply generate a random number between 1 and 75 ( I know
> how to do this part) like in BINGO, if the number is between 1 and 15, show
> "B15" if 15 was to be generated. Since I in BINGO contains the range
> 16 -30, then I need any number that occurs randomly between 16 and 30 to
> show as "I23" if 23 was the generated random number. N contains the range
> 31-45, etc.,
>
> I can do it manually now and just add the correct letter myself but that
> takes the fun out of it. Can anybody tell me what I do. I didn't even know
> the name of the feature I needed to look up to get help on. Thanks.
>
> T Harris
>
>

[T Harris]

Thanks to everyone. Problem solved.


"T Harris" <tdh1967@bellsouth.net> wrote in message
news:ug0q84ECGHA.3920@tk2msftngp13.phx.gbl...
> This will be easy for most but not me. Here goes:
>
> I want Excel to simply generate a random number between 1 and 75 ( I know
> how to do this part) like in BINGO, if the number is between 1 and 15,
> show "B15" if 15 was to be generated. Since I in BINGO contains the range
> 16 -30, then I need any number that occurs randomly between 16 and 30 to
> show as "I23" if 23 was the generated random number. N contains the range
> 31-45, etc.,
>
> I can do it manually now and just add the correct letter myself but that
> takes the fun out of it. Can anybody tell me what I do. I didn't even
> know the name of the feature I needed to look up to get help on. Thanks.
>
> T Harris
>

[Dana DeLouis]

Hi. With a random integer between 1-75 in A1, here is another option:

=CHAR(MOD(391511,1+FLOOR(A1-1,15))+66) & A1

--
Dana DeLouis
Win XP & Office 2003


"T Harris" <tdh1967@bellsouth.net> wrote in message
news:ug0q84ECGHA.3920@tk2msftngp13.phx.gbl...
> This will be easy for most but not me. Here goes:
>
> I want Excel to simply generate a random number between 1 and 75 ( I know
> how to do this part) like in BINGO, if the number is between 1 and 15,
> show "B15" if 15 was to be generated. Since I in BINGO contains the range
> 16 -30, then I need any number that occurs randomly between 16 and 30 to
> show as "I23" if 23 was the generated random number. N contains the range
> 31-45, etc.,
>
> I can do it manually now and just add the correct letter myself but that
> takes the fun out of it. Can anybody tell me what I do. I didn't even
> know the name of the feature I needed to look up to get help on. Thanks.
>
> T Harris

[Sandy Mann]

Another truly esoteric formula Dana! May I please ask how you arrived at
the magic number of 391511? I have tried several versions of LCD's but the
logic of it eludes me. (Please don't tell me that you found it in a
Christmas cracker <g> )
--
Regards


Sandy
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk


"Dana DeLouis" <delouis@bellsouth.net> wrote in message
news:%23ykTUFhCGHA.344@TK2MSFTNGP11.phx.gbl...
> Hi. With a random integer between 1-75 in A1, here is another option:
>
> =CHAR(MOD(391511,1+FLOOR(A1-1,15))+66) & A1
>
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "T Harris" <tdh1967@bellsouth.net> wrote in message
> news:ug0q84ECGHA.3920@tk2msftngp13.phx.gbl...
>> This will be easy for most but not me. Here goes:
>>
>> I want Excel to simply generate a random number between 1 and 75 ( I know
>> how to do this part) like in BINGO, if the number is between 1 and 15,
>> show "B15" if 15 was to be generated. Since I in BINGO contains the
>> range 16 -30, then I need any number that occurs randomly between 16 and
>> 30 to show as "I23" if 23 was the generated random number. N contains
>> the range 31-45, etc.,
>>
>> I can do it manually now and just add the correct letter myself but that
>> takes the fun out of it. Can anybody tell me what I do. I didn't even
>> know the name of the feature I needed to look up to get help on. Thanks.
>>
>> T Harris
>
>

[T Harris]

Dana,

It worked perfectly and I really appreciate it but how on earth did you know
this? Where do you get 391511 from? I am a programming idiot so excuse me
for being so ignorant. Where does a person learn to proram these commands?
I didn't even know the command existed.

T Harris

"Dana DeLouis" <delouis@bellsouth.net> wrote in message
news:%23ykTUFhCGHA.344@TK2MSFTNGP11.phx.gbl...
> Hi. With a random integer between 1-75 in A1, here is another option:
>
> =CHAR(MOD(391511,1+FLOOR(A1-1,15))+66) & A1
>
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "T Harris" <tdh1967@bellsouth.net> wrote in message
> news:ug0q84ECGHA.3920@tk2msftngp13.phx.gbl...
>> This will be easy for most but not me. Here goes:
>>
>> I want Excel to simply generate a random number between 1 and 75 ( I know
>> how to do this part) like in BINGO, if the number is between 1 and 15,
>> show "B15" if 15 was to be generated. Since I in BINGO contains the
>> range 16 -30, then I need any number that occurs randomly between 16 and
>> 30 to show as "I23" if 23 was the generated random number. N contains
>> the range 31-45, etc.,
>>
>> I can do it manually now and just add the correct letter myself but that
>> takes the fun out of it. Can anybody tell me what I do. I didn't even
>> know the name of the feature I needed to look up to get help on. Thanks.
>>
>> T Harris
>
>

[Dana DeLouis]

> May I please ask how you arrived at the magic number of 391511?
> I have tried several versions of LCD's but the logic of it eludes me.
> Regards
>
Hi. Thanks. Glad you found this interesting. Basically, I have a program
that does an automatic search for me

Basically, your output are the character codes for "BINGO", which are
66,73,78,71,79.
I used the Floor function earlier, but that was probably not the best. I
think I should have used the Ceiling function. With Ceiling (ie
CEILING(A1,15), our input numbers (1-75), are transformed into
15,30,45,60,75.
The program does a quick check of the numbers to quickly learn that there
are no solutions to this problem. It then does a quick scan of the numbers
with scaling and offset to check for a simple solution. There are none, so
it moves on.
The next step it does is to offset the input, and not the output. However,
this usually results in larger numbers than Excel's Mod function can handle.
I don't know why this isn't fixed. It causes a lot of problems.

XL: MOD() Function Returns #NUM! Error Value
http://support.microsoft.com/kb/119083/en-us

First two solutions are:
=CHAR(MOD(3400616087,CEILING(A1,15)+52)) & A1

The program checks that "3400616087" will work, but since it is right on the
edge, it will search again for a smaller number.

=CHAR(MOD(90809279,CEILING(A1,15)+56)) & A1

other attempts result in larger numbers, so it exits this portion. Program
will then look at the output numbers.

The output numbers are larger than the input numbers, so there are no
solutions. It then offsets the input back towards 0. The first attempt is
a reduction of 66. The new output numbers are 0,7,12,5,13.
There is still no solution, so it offsets the input. The first attempt is
an offset of -14, since each input needs to be larger than similar output
number. The program will loop in a search. I call the function with a few
options, but we are in luck here on the program's first loop, as discussed
below.

I have custom functions that I call, but this is the basic idea for this
problem since you were interested. :>)
The output number is 391511. This longer version will "usually" have a
smaller number than the other examples. Therefore:
=CHAR(MOD(391511,CEILING(A1,15)-14)+66) & A1

If still no solution, the program moves on to a scaling factors between
2-10. There are usually no solutions with numbers larger than this. If
still no solution, it will then attempt to break the problem into two. It
partitions both lists into two groups via a function called KSetPartition.
(of size 2). It will then retry with both smaller sets and lists the best
solutions. If still no luck, the next partition is size 3, and retries. If
still no luck, I have the program end.
Basically, here is the logic to this problem. HTH :>)

Sub Demo()
Dim Answer
Dim Inn, Out
Dim t(1 To 5)
Dim v(1 To 5)
Dim ds
Dim m
Dim z
Dim p As Long

Inn = Array(1, 16, 31, 46, 61)
Out = Array(0, 7, 12, 5, 13) ' "BINGO" - 66

With WorksheetFunction
' Make base-1
Inn = .Transpose(.Transpose(Inn))
Out = .Transpose(.Transpose(Out))

'= = = = = = = = = = =
' Most pair of numbers within "Inn" are Relatively Prime
' except for 1 pair!!! Darn!!
' Therefore, call TaYen and attempt to reduce...

' Call Ta-Yen Rule:
' Most pairs are Relatively Prime
' However...
' 16=2^4, and 46=2*23
' Keep 16, but cancel 2 in 46(leave 23)
'ds = TaYen(Inn)
'= = = = = = = = = = =

'// Returns this array instead of Inn
ds = Array(1, 16, 31, 23, 61)

' Make base-1
ds = .Transpose(.Transpose(ds))
End With 'WorksheetFunction

' Call your LCM function, but for now...
m = [LCM(1, 16, 31, 23, 61)] 'm = 695888

For p = 1 To 5
t(p) = m / ds(p)
Next p

'// Now we need to Solve for k for each t & ds in:
'// Mod(t * k, ds)=1 for k.
'// "Should" have a solution if ds was reduced correctly...
'// There are a few interesting number theory techniques,
'// but this is the basic general idea...

For p = 1 To 5
'// t(p) may be large, so we try to reduce...
z = t(p) Mod ds(p)
If z = 0 Then
v(p) = 0
Else
v(p) = FindOne(z, ds(p))
End If
Next p

For p = 1 To 5
Answer = Answer + Out(p) * v(p) * t(p)
Next p

'// Number may not be the "least"...
Answer = Answer Mod m
Debug.Print Answer
End Sub

Function FindOne(x, y)
'/ There are better methods, but for here ...
Dim p As Long

For p = 1 To y
If ((p * x) Mod y) = 1 Then Exit For
Next p
FindOne = p
End Function

--
Dana DeLouis
Win XP & Office 2003

[T Harris]

Thanks Dana



"Dana DeLouis" <delouis@bellsouth.net> wrote in message
news:el6oSdNDGHA.1288@TK2MSFTNGP09.phx.gbl...
>> May I please ask how you arrived at the magic number of 391511?
>> I have tried several versions of LCD's but the logic of it eludes me.
>> Regards
>>
> Hi. Thanks. Glad you found this interesting. Basically, I have a
> program that does an automatic search for me
>
> Basically, your output are the character codes for "BINGO", which are
> 66,73,78,71,79.
> I used the Floor function earlier, but that was probably not the best. I
> think I should have used the Ceiling function. With Ceiling (ie
> CEILING(A1,15), our input numbers (1-75), are transformed into
> 15,30,45,60,75.
> The program does a quick check of the numbers to quickly learn that there
> are no solutions to this problem. It then does a quick scan of the
> numbers with scaling and offset to check for a simple solution. There are
> none, so it moves on.
> The next step it does is to offset the input, and not the output.
> However, this usually results in larger numbers than Excel's Mod function
> can handle. I don't know why this isn't fixed. It causes a lot of
> problems.
>
> XL: MOD() Function Returns #NUM! Error Value
> http://support.microsoft.com/kb/119083/en-us
>
> First two solutions are:
> =CHAR(MOD(3400616087,CEILING(A1,15)+52)) & A1
>
> The program checks that "3400616087" will work, but since it is right on
> the edge, it will search again for a smaller number.
>
> =CHAR(MOD(90809279,CEILING(A1,15)+56)) & A1
>
> other attempts result in larger numbers, so it exits this portion.
> Program will then look at the output numbers.
>
> The output numbers are larger than the input numbers, so there are no
> solutions. It then offsets the input back towards 0. The first attempt
> is a reduction of 66. The new output numbers are 0,7,12,5,13.
> There is still no solution, so it offsets the input. The first attempt is
> an offset of -14, since each input needs to be larger than similar output
> number. The program will loop in a search. I call the function with a
> few options, but we are in luck here on the program's first loop, as
> discussed below.
>
> I have custom functions that I call, but this is the basic idea for this
> problem since you were interested. :>)
> The output number is 391511. This longer version will "usually" have a
> smaller number than the other examples. Therefore:
> =CHAR(MOD(391511,CEILING(A1,15)-14)+66) & A1
>
> If still no solution, the program moves on to a scaling factors between
> 2-10. There are usually no solutions with numbers larger than this. If
> still no solution, it will then attempt to break the problem into two.
> It partitions both lists into two groups via a function called
> KSetPartition. (of size 2). It will then retry with both smaller sets and
> lists the best solutions. If still no luck, the next partition is size 3,
> and retries. If still no luck, I have the program end.
> Basically, here is the logic to this problem. HTH :>)
>
> Sub Demo()
> Dim Answer
> Dim Inn, Out
> Dim t(1 To 5)
> Dim v(1 To 5)
> Dim ds
> Dim m
> Dim z
> Dim p As Long
>
> Inn = Array(1, 16, 31, 46, 61)
> Out = Array(0, 7, 12, 5, 13) ' "BINGO" - 66
>
> With WorksheetFunction
> ' Make base-1
> Inn = .Transpose(.Transpose(Inn))
> Out = .Transpose(.Transpose(Out))
>
> '= = = = = = = = = = =
> ' Most pair of numbers within "Inn" are Relatively Prime
> ' except for 1 pair!!! Darn!!
> ' Therefore, call TaYen and attempt to reduce...
>
> ' Call Ta-Yen Rule:
> ' Most pairs are Relatively Prime
> ' However...
> ' 16=2^4, and 46=2*23
> ' Keep 16, but cancel 2 in 46(leave 23)
> 'ds = TaYen(Inn)
> '= = = = = = = = = = =
>
> '// Returns this array instead of Inn
> ds = Array(1, 16, 31, 23, 61)
>
> ' Make base-1
> ds = .Transpose(.Transpose(ds))
> End With 'WorksheetFunction
>
> ' Call your LCM function, but for now...
> m = [LCM(1, 16, 31, 23, 61)] 'm = 695888
>
> For p = 1 To 5
> t(p) = m / ds(p)
> Next p
>
> '// Now we need to Solve for k for each t & ds in:
> '// Mod(t * k, ds)=1 for k.
> '// "Should" have a solution if ds was reduced correctly...
> '// There are a few interesting number theory techniques,
> '// but this is the basic general idea...
>
> For p = 1 To 5
> '// t(p) may be large, so we try to reduce...
> z = t(p) Mod ds(p)
> If z = 0 Then
> v(p) = 0
> Else
> v(p) = FindOne(z, ds(p))
> End If
> Next p
>
> For p = 1 To 5
> Answer = Answer + Out(p) * v(p) * t(p)
> Next p
>
> '// Number may not be the "least"...
> Answer = Answer Mod m
> Debug.Print Answer
> End Sub
>
> Function FindOne(x, y)
> '/ There are better methods, but for here ...
> Dim p As Long
>
> For p = 1 To y
> If ((p * x) Mod y) = 1 Then Exit For
> Next p
> FindOne = p
> End Function
>
> --
> Dana DeLouis
> Win XP & Office 2003
>

[Sandy Mann]

Thank you very much Dana,

> Hi. Thanks. Glad you found this interesting. Basically, I have a
> program that does an automatic search for me

And there was me thinking that you did it all in your head! <g>

I will digest it all over the holiday.

Thank you again for going to so much trouble.

--
Happy New Year

Sandy
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

"Dana DeLouis" <delouis@bellsouth.net> wrote in message
news:el6oSdNDGHA.1288@TK2MSFTNGP09.phx.gbl...
>> May I please ask how you arrived at the magic number of 391511?
>> I have tried several versions of LCD's but the logic of it eludes me.
>> Regards
>>
> Hi. Thanks. Glad you found this interesting. Basically, I have a
> program that does an automatic search for me
>
> Basically, your output are the character codes for "BINGO", which are
> 66,73,78,71,79.
> I used the Floor function earlier, but that was probably not the best. I
> think I should have used the Ceiling function. With Ceiling (ie
> CEILING(A1,15), our input numbers (1-75), are transformed into
> 15,30,45,60,75.
> The program does a quick check of the numbers to quickly learn that there
> are no solutions to this problem. It then does a quick scan of the
> numbers with scaling and offset to check for a simple solution. There are
> none, so it moves on.
> The next step it does is to offset the input, and not the output.
> However, this usually results in larger numbers than Excel's Mod function
> can handle. I don't know why this isn't fixed. It causes a lot of
> problems.
>
> XL: MOD() Function Returns #NUM! Error Value
> http://support.microsoft.com/kb/119083/en-us
>
> First two solutions are:
> =CHAR(MOD(3400616087,CEILING(A1,15)+52)) & A1
>
> The program checks that "3400616087" will work, but since it is right on
> the edge, it will search again for a smaller number.
>
> =CHAR(MOD(90809279,CEILING(A1,15)+56)) & A1
>
> other attempts result in larger numbers, so it exits this portion.
> Program will then look at the output numbers.
>
> The output numbers are larger than the input numbers, so there are no
> solutions. It then offsets the input back towards 0. The first attempt
> is a reduction of 66. The new output numbers are 0,7,12,5,13.
> There is still no solution, so it offsets the input. The first attempt is
> an offset of -14, since each input needs to be larger than similar output
> number. The program will loop in a search. I call the function with a
> few options, but we are in luck here on the program's first loop, as
> discussed below.
>
> I have custom functions that I call, but this is the basic idea for this
> problem since you were interested. :>)
> The output number is 391511. This longer version will "usually" have a
> smaller number than the other examples. Therefore:
> =CHAR(MOD(391511,CEILING(A1,15)-14)+66) & A1
>
> If still no solution, the program moves on to a scaling factors between
> 2-10. There are usually no solutions with numbers larger than this. If
> still no solution, it will then attempt to break the problem into two.
> It partitions both lists into two groups via a function called
> KSetPartition. (of size 2). It will then retry with both smaller sets and
> lists the best solutions. If still no luck, the next partition is size 3,
> and retries. If still no luck, I have the program end.
> Basically, here is the logic to this problem. HTH :>)
>
> Sub Demo()
> Dim Answer
> Dim Inn, Out
> Dim t(1 To 5)
> Dim v(1 To 5)
> Dim ds
> Dim m
> Dim z
> Dim p As Long
>
> Inn = Array(1, 16, 31, 46, 61)
> Out = Array(0, 7, 12, 5, 13) ' "BINGO" - 66
>
> With WorksheetFunction
> ' Make base-1
> Inn = .Transpose(.Transpose(Inn))
> Out = .Transpose(.Transpose(Out))
>
> '= = = = = = = = = = =
> ' Most pair of numbers within "Inn" are Relatively Prime
> ' except for 1 pair!!! Darn!!
> ' Therefore, call TaYen and attempt to reduce...
>
> ' Call Ta-Yen Rule:
> ' Most pairs are Relatively Prime
> ' However...
> ' 16=2^4, and 46=2*23
> ' Keep 16, but cancel 2 in 46(leave 23)
> 'ds = TaYen(Inn)
> '= = = = = = = = = = =
>
> '// Returns this array instead of Inn
> ds = Array(1, 16, 31, 23, 61)
>
> ' Make base-1
> ds = .Transpose(.Transpose(ds))
> End With 'WorksheetFunction
>
> ' Call your LCM function, but for now...
> m = [LCM(1, 16, 31, 23, 61)] 'm = 695888
>
> For p = 1 To 5
> t(p) = m / ds(p)
> Next p
>
> '// Now we need to Solve for k for each t & ds in:
> '// Mod(t * k, ds)=1 for k.
> '// "Should" have a solution if ds was reduced correctly...
> '// There are a few interesting number theory techniques,
> '// but this is the basic general idea...
>
> For p = 1 To 5
> '// t(p) may be large, so we try to reduce...
> z = t(p) Mod ds(p)
> If z = 0 Then
> v(p) = 0
> Else
> v(p) = FindOne(z, ds(p))
> End If
> Next p
>
> For p = 1 To 5
> Answer = Answer + Out(p) * v(p) * t(p)
> Next p
>
> '// Number may not be the "least"...
> Answer = Answer Mod m
> Debug.Print Answer
> End Sub
>
> Function FindOne(x, y)
> '/ There are better methods, but for here ...
> Dim p As Long
>
> For p = 1 To y
> If ((p * x) Mod y) = 1 Then Exit For
> Next p
> FindOne = p
> End Function
>
> --
> Dana DeLouis
> Win XP & Office 2003
>