Restrict digits to stop "borrowing"

rline101

Board Regular
Joined
Dec 22, 2005
Messages
70
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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
assuming your first and second numbers are in A1 and B1, Paste the following codes in the macro window ( Alt F11)


Code:
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
how about this

Code:
=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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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
AB
128031102
Sheet3
 

rline101

Board Regular
Joined
Dec 22, 2005
Messages
70
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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:
Code:
=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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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...

Code:
=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 #.
 

Forum statistics

Threads
1,181,658
Messages
5,931,271
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top