Results 1 to 7 of 7

20150529, 15:24 #1
 Join Date
 May 2015
 Posts
 3
 Thanks
 3
 Thanked 0 Times in 0 Posts
Automating single iterations to test random data
I'm looking for a way to automate the following:
 I have a 2dimensional range of cells filled with random numbers.
 I have a complex bunch of formulas that crunch those numbers to create some new data in other cells.
 Currently I press F9 repeatedly to get a new set of random data and
 I stop pressing F9 when a certain calculation cell has a certain value.
A super simple example:
A named range "MYDATA" contains cells $A$1:$H$8
 Set every cell in the range MYDATA to contain =RANDBETWEEN(1,6)
 Formulas as follows:
 H11 = COUNTIF(MYDATA,1)
 H12 = COUNTIF(MYDATA,2)
 H13 = COUNTIF(MYDATA,3)
 H14 = COUNTIF(MYDATA,4)
 H15 = COUNTIF(MYDATA,5)
 H16 = COUNTIF(MYDATA,6)
 H21 = SUM(H11,H13,H15) //* sum of count of all odd values
 H22 = SUM(H12,H14,H16) //* sum of count of all even values
 H24 = SUM(H11:H13) //* sum of count of values 1..3
 H25 = SUM(H14:H16) //* sum of count of values 4..5
 G20 = AND(H21=H22,H24=H25) //* success criteria as a boolean value
 Press F9 repeatedly
 ... until G20 = TRUE
As you can imagine it takes a long time to get a "successful" set of data (and this is a simplified example). Is there anyway to automate this process?
If it requires VBA to simulate "pressing F9" and to test the value of G20 that is fine, but I do not want VBA to do any of the other formulas. If this can be done without any VBA that would be really great.
Thanks in advance,
Bryan
PS: My system is Win 8.1 using Excel 2010 64bit.Last edited by BryanA; 20150529 at 15:35. Reason: ADDING INFO

20150529, 15:35 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,731
 Thanks
 395
 Thanked 1,533 Times in 1,390 Posts
Bryan,
Welcome to the Lounge as a new poster!
Google: "excel goal seeking" w/o quotes. I think this is what you are after.
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

The Following User Says Thank You to RetiredGeek For This Useful Post:
BryanA (20150529)

20150529, 16:05 #3
 Join Date
 May 2015
 Posts
 3
 Thanks
 3
 Thanked 0 Times in 0 Posts
Thanks RetiredGeek but I'm afraid I already tried Goal Seeking without much success.
Goal Seeking seems to be designed to systematically change the value of just 1 cell to create a direct derivative result.
In my case I am randomly changing the values of 64 cells and calculating aggregate statistical results.
If you could show me how to make Goal Seeking change multiple random numbers I would love to learn.Last edited by BryanA; 20150529 at 16:05. Reason: typo

20150529, 16:19 #4
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,731
 Thanks
 395
 Thanked 1,533 Times in 1,390 Posts
Bryan,
Ok give this code a try:
Code:Option Explicit Sub Test() Do Calculate Loop Until [$G$20].Value = True End Sub 'Test
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

The Following User Says Thank You to RetiredGeek For This Useful Post:
BryanA (20150529)

20150529, 16:55 #5
 Join Date
 May 2015
 Posts
 3
 Thanks
 3
 Thanked 0 Times in 0 Posts
That did it! Awesome, thank you RetiredGeek!
It immediately worked on my simplified example and it is now working on my complex project 4,000 iterations and counting (I added an iteration counter to your code to measure progress).
Code:Sub Test() [$X$2].Value = 0 ' Reset iteration counter cell Do Calculate [$X$2].Value = [$X$2].Value + 1 Loop Until ([$G$20].Value = TRUE Or [$X$2].Value = 99999) 'Limit to 100K iterations End Sub 'Test
Last edited by BryanA; 20150529 at 17:08.

20150529, 17:14 #6
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,731
 Thanks
 395
 Thanked 1,533 Times in 1,390 Posts
Bryan,
Glad it worked out for you.May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

The Following User Says Thank You to RetiredGeek For This Useful Post:
BryanA (20150529)

20150531, 16:45 #7
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,171
 Thanks
 151
 Thanked 587 Times in 559 Posts
Hi Bryan
8000+ writes to the sheet. Blimey!
..writing the iteration count to the sheet will slow it down a great deal.
You could instead, show the iteration count in the bottom statusbar, and then, if required, save the final count to the sheet.
zeddyLast edited by zeddy; 20150531 at 16:47.