Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    3
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Question Automating single iterations to test random data

    I'm looking for a way to automate the following:


    1. I have a 2-dimensional range of cells filled with random numbers.
    2. I have a complex bunch of formulas that crunch those numbers to create some new data in other cells.
    3. Currently I press F9 repeatedly to get a new set of random data and
    4. 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

    1. Set every cell in the range MYDATA to contain =RANDBETWEEN(1,6)
    2. Formulas as follows:
      1. H11 = COUNTIF(MYDATA,1)
      2. H12 = COUNTIF(MYDATA,2)
      3. H13 = COUNTIF(MYDATA,3)
      4. H14 = COUNTIF(MYDATA,4)
      5. H15 = COUNTIF(MYDATA,5)
      6. H16 = COUNTIF(MYDATA,6)
      7. H21 = SUM(H11,H13,H15) //* sum of count of all odd values
      8. H22 = SUM(H12,H14,H16) //* sum of count of all even values
      9. H24 = SUM(H11:H13) //* sum of count of values 1..3
      10. H25 = SUM(H14:H16) //* sum of count of values 4..5
      11. G20 = AND(H21=H22,H24=H25) //* success criteria as a boolean value

    3. Press F9 repeatedly
    4. ... 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 64-bit.
    Last edited by BryanA; 2015-05-29 at 16:35. Reason: ADDING INFO

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 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.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    BryanA (2015-05-29)

  4. #3
    New Lounger
    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; 2015-05-29 at 17:05. Reason: typo

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Bryan,

    Ok give this code a try:

    Code:
    Option Explicit
    
    Sub Test()
    
       Do
       
         Calculate
        
       Loop Until [$G$20].Value = True
       
       
    End Sub  'Test
    Test File: Bryan Testloop.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    BryanA (2015-05-29)

  7. #5
    New Lounger
    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
    UPDATE: Found my first good dataset in only 8,630 iterations (this was for the complex spreadsheet, not the simple one). Soooo glad I did not have to do that by hand. Thank you again!
    Last edited by BryanA; 2015-05-29 at 18:08.

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 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

  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    BryanA (2015-05-29)

  10. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 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.

    zeddy
    Last edited by zeddy; 2015-05-31 at 17:47.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •