Restrict digits to stop "borrowing"

[rline101]

I figure excel is mathematical so I bet it can do this, but as usual I'm not proficient enough yet to work it out...

I have generated two random numbers in different cells between previously specified values. For instance, the first might be 4137 and the second 3982. (The first generate number will always be larger than the second.)

Doing the subtraction "first" - "second" will in this case mean 4137 - 3982. This involves borrowing or trading or exchanging (or whatever name you want). If I generate the first number, how can I tell excel to make sure the second number does *not* involve any borrowing?

Eg. if first number is 4137, then digit 1 of second number can only be 3,2 or 1. Digit 2 of second number can only be 1 or 0. Digit 3 can only be 3,2 or 1. Digit 4 can only be 7,6,5,4,3,2 or 1 (but not 8 or 9).

I figure the solution will need to somehow involve looking at each digit in turn. Is it possible?

Thanks.

 

[ravishankar]

Hi
assuming your first and second numbers are in A1 and B1, Paste the following codes in the macro window ( Alt F11)

for a = 1 to 4
if mid(cells(a,1),a,1) < mid(cells(a,2),a,1) then
msgbox " trading required at digit : - " & a 
endif
next a
Ravi

Run the macro

 

[Jonmo1]

how about this

=RANDBETWEEN(0,MID($A1,1,1))&RANDBETWEEN(0,MID($A1,2,1))&RANDBETWEEN(0,MID($A1,3,1))&RANDBETWEEN(0,MID($A1,4,1))

Randbetween requires the Data Analysis Toolpack Addin.

 

[Lewiy]

Here's a rather crude way of doing it (hope I've understood correctly!). I've used RANDBETWEEN to make it easier to see what I'm doing but you could just as easily use the INT(RAND()) method:

Excel Workbook
	A	B
1	2803	1102
Sheet3

 

[rline101]

Thanks people!!

I bet this stuff is child's play for you all, but it still amazes me how easily excel will do things I think are difficult. I initially saw the first two answers, and went for the second one as I prefer to stay away from vba if I can. Then the 3rd one was posted, which is very nice indeed. I might try it as well but I know the 2nd one works.

You've given me some closure on this one, so thanks again and I guarantee I'll be posting some more soon...

Cheers

 

[Lewiy]

Actually, Jonmo’s solution works on the same principal as mine, however, it will output the number as a text value. Not a major problem because as soon as you use it for calculations, Excel will treat it as a number, but for completion purposes, stick a VALUE() around it, i.e:

=VALUE(RANDBETWEEN(0,MID($A1,1,1))&RANDBETWEEN(0,MID($A1,2,1))&RANDBETWEEN(0,MID($A1,3,1))&RANDBETWEEN(0,MID($A1,4,1)))

 

[Jonmo1]

Also, on mine I used between 0 and whatever... That means sometimes the first digit might be a 0, making it a 3 digit #. Not sure if that matters. But you might just change it to use 1 for the first digit...

=VALUE(RANDBETWEEN(1,MID($A1,1,1))&RANDBETWEEN(0,MID($A1,2,1))&RANDBETWEEN(0,MID($A1,3,1))&RANDBETWEEN(0,MID($A1,4,1)))

This way, the first digit will always be at least a 1 and you will always have a 4 digit #.