Random Number generator for knitting pattern

[knitknotes]

My first knitting question was¦ and I thank you for the reply.
http://forum.chandoo.org/threads/changing-left-to-right-top-to-bottom.6612/



I would like to use Excel to generate the knitting pattern for the colour changes in the skirt of this little dress.
http://www.ravelry.com/discuss/the-testing-pool/3087027/1-25

I have 242 sts on my needle - actually 22 stitches 11 times
For the first few rows, I need one random number in each of the 11 blocks of stitches. That's easy.
For the next few rows, I need two random numbers in each block of stitches.
After that, I need three random numbers, then four.
Would be nice if I could type =RANDBETWEEN(1,22)2 =RANDBETWEEN(1,22)3
Is there a way to say to excel "give me 3 random numbers between 1 and 22" ?

Second part
I would like to open a second sheet that paints the data from sheet one. Fill all cells pink.
If the first 11 random numbers began 7, 3,15, 22, etc. Excel would change cell G1 to the contrasting colour, followed by y1 (=22+3), bg1 (44+15), cj1 (66+22)
I'm thinking; fill all cells to 0 and pink. Then if cell not equal 0, colour = white.
But I don't know how to write that, and there may be a better way.

Lastly, we need to flip the result left to right and top to bottom. I can do that quite easily in a screen capture and graphic editor.

Thanks in advance for reading and responding.

 

[Hui]

The second link to Ravelry.com doesn't work or needs a login/password

Can you post a spreadsheet with the layout you need as i have a few ideas

 

[knitknotes]

Many thanks for your prompt reply. Apologies for not responding sooner - we are spending this week high in the mountains, with only a slow dial-up connxn. Bet you didn't know such things still existed!

Sorry for posting a link that fails - wanted to give credit to the source. But the .pdf provides the details of the pattern that I'd like you and Excel to solve.

Attached is simple .xls file. Sheet one are the stitches generated by the random number generator. Sheet 2 is the painted picture.

Many thanks for your interest and for responding. Happy New Year.

 

[Hui]

I think the attached does what you want
It uses a number of helper cells

Press F9 to generate new numbers
If you get duplicates press F9

The other way to do it would be with a User Defined Function

 

[knitknotes]

Thank you Hui. Works great.

Can Excel create the Sheet 2 I wanted, or is that asking too much?

 

[Hui]

Can you explain how Sheet 2 is or should be related to Sheet 1 ?

 

[knitknotes]

Sure, I can try.

The first Excel row matches the first row of my knitting. 242 stitches in 242 cells takes us from A1:IH1. There are 11 blocks of 22 stitches in this row. In the first block, all the stitches are pink except for the first random number generated. If that number were 7, the 7th stitch becomes white, and the contents of G7 show white.

If the next number generated were 3, then the 3rd stitch in the second block, which is also the 25th stitch (22+3) is now white. If the 3rd random number generated were 15, then the 59th stitch (22+22+15) is now white.

In the first 3 rows of the Excel sheet, I need 1 white cell in every block. In the next 3 rows, I need 2 white cells in every block. Gradually the number of white cells will increase. When the number of white cells equal the number of pink cells, we reverse the process until the last row of the Excel sheet is all white. This should match the bottom of the little dress in the photo.

Just so you don't try to stand on your head, we knitters are actually knitting from the bottom of the skirt, up. We read knitting written instructions as people read text, but we are actually knitting from right to left, and from bottom to top. The beauty of charts, which sheet 2 will be if this works, is that we can flip charts vertically and horizontally, to match our knitting. Modern knitters prefer to use charts more than written instructions, but both are common.

Once again, thanks for your interest, and your response.