I have a sheet with 11 columns and 11 rows. Each cell has in it this formula: =RANDOM()*99.
I need every cell to be filled with a random, two digit number. I also want to somehow make sure that there are no adjacent cells with the same number.
Can someone help me with this?
How about a UDF. Put =FancyRand() in each of the cells. It will take a bit to settle down, but I think it will do what you want. You may want to set Calculation to manual.
Please Login or Register to view this content.
Austin,
See also:
Please Login or Register to view this content.
HTH
Myles
...constantly looking for the smoother pebble while the whole ocean of truth lies before me.
Austin,
Disregard my original code. I cobbled it together in haste and allowed for only 2 criteria circumspection checks instead of four. The following fixes the problem.
[By the way, if you want 2 digit numbers in every cell, the Worksheet formula =RAND*99 may yield single digit numbers 0 to 9 as wells as 10 to 98. Note that, 99 will be excluded as the RAND() function has the range 0 to less than 1 . The correct WorkSheet formula should be Int(10+Rand()*90) and in VBA Int(10+Rnd*90) or if you have AnalysisToolPack installed =RANDBETWEEN(10 ,99).Please Login or Register to view this content.
Hi
This is a formula solution.
Leave Row 1 and Column A empty (or you can write text, but not numbers).
In B2:
=SMALL(IF((ROW($10:$99)<>A1)*(ROW($10:$99)<>B1)*(ROW($10:$99)<>A2),ROW($10:$99)),1+INT((90-IF((ROW(B2)=ROW($B$2))*(COLUMN(B2)=COLUMN($B$2)),0,IF((ROW(B2)=ROW($B$2))+(COLUMN(B2)=COLUMN($B$2)),1,3)))*RAND()))
Copy down and accross
This in an array formula, you have to confirm it with CTRL+SHIFT+ENTER.
Remark: You say that the cells must have 2 digit numbers. I interpreted it as numbers from 10 till 99.
HTH
lecxe
Last edited by lecxe; 11-13-2007 at 07:37 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks