none
Visual Basic Macro to Randomize a Number based on a percentage RRS feed

  • Question

  • I have a spreadsheet with a lot of numbers, and would like to randomize these using a percentage deviation. For instance if the number is 1200 i want a macro to change this to 1200 (+ or -) x (where x is a random number that is less than or equal to y% of 1200).

    A practical example is:

    1200 - 12 = 1188 (if i used 1% as the deviation)

    Does anyone have a macro that does something like this ?

    Best Regards,

     

    Tuesday, July 19, 2011 2:38 PM

Answers

  • Hi,

    Your basically just looking for away to produce a random number between a minimum and maximum value.

    To produce random integers in a given range, use this formula:

    Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
    

    Here, upperbound is the highest number in the range, and lowerbound is the lowest number in the range.

     

     

     

     


    "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks
    • Marked as answer by dct374 Tuesday, July 19, 2011 3:23 PM
    Tuesday, July 19, 2011 3:15 PM

All replies

  • Hi,

    Your basically just looking for away to produce a random number between a minimum and maximum value.

    To produce random integers in a given range, use this formula:

    Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
    

    Here, upperbound is the highest number in the range, and lowerbound is the lowest number in the range.

     

     

     

     


    "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks
    • Marked as answer by dct374 Tuesday, July 19, 2011 3:23 PM
    Tuesday, July 19, 2011 3:15 PM
  • Hi Derek,

                Many thanks for the reply. That answers my question. I now have the following Macro which works on a single cell :

    Sub AddToCell()
        'ActiveCell.Value places the value or formula into the active cell
        'on the active worksheet.
        '
        'ActiveCell.Value returns the value in the active cell of the active
        'window.
        '
        'InputBox calls up the Excel input box for you to type in the number
        'to be added to the original value. Val takes the text string
        'returned from the input box and turns it into a number.

    randomvalue = CInt(Int((4 - (-4) + 1) * Rnd() + (-4)))

        ActiveCell.Value = ActiveCell.Value + (100 * randomvalue / ActiveCell.Value)
    End Sub

    I just want to know if there's anyway if i can run this macro across multiple cell selections (which i select with my mouse cursor) ?

    Tuesday, July 19, 2011 3:25 PM
  • Function Amount(percentage As Integer, total As Integer) As Integer
        Dim max, min As Integer
        max = (total / 100) * percentage
        min = max - (max * 2)
        
        Randomize

        Dim random As Integer
        random = Int((max - min + 1) * rnd() + min)

        Amount = total + random
    End Function

    Function Test()
        Debug.Print Amount(1, 1200)
        Debug.Print Amount(1, 1200)
        Debug.Print Amount(1, 1200)
        Debug.Print Amount(1, 1200)
    End Function
    "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks
    Tuesday, July 19, 2011 3:30 PM
  • How do i run this function ?
    Tuesday, July 19, 2011 3:36 PM
  • You have:

    Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

    But Rnd goes from 0 to 1: when Rnd = 0, value is lowerbound;  when Rnd = 0, value is upperbound + 1, not just upperbound

    So that line should be

    Int((upperbound - lowerbound) * Rnd + lowerbound

    Also, since the OP was asking for a percent +/-

    NewValue = Int((OldValue * 2 * PCT) * Rnd + OldValue * (1 - PCT))

    where PCT is a decimal between 0 and 1


    HTH, Bernie
    Tuesday, July 19, 2011 7:47 PM
  •  

    @Bernie,

    The formula came from the Excel VBA help files, the help page for Rnd(). Not saying that means it's correct but if you can prove that it's wrong and the formula should be the one you indicate then it would be worth contacting Microsoft about the bug in their help files.   :)  

    I've done a small test with the two formula and the one with the +1 produces values that are inclusive of the lower bound and upper bound values. The formula without the +1 is inclusive of the lower bound value but is exclusive of the upper bound value.

     

    @dct,

    Great a module and paste the code into in. You need to make the function public like this..

    Public Function Amount(percentage As Integer, total As Integer) As Integer

    You can call it just like any other function. The Test function above shows how it's called.

    If you want to call the function for each cell in a selection then you can do that quite easily but to do this with the mouse when a selection is made is rather dangerous. As I found out. If the whole sheet is selected, all cells in the sheet, then Excel will update all billion odd cells. Best to assign to a key press. For example make a selection press a key combination which causes the values to be set.

    In the Workbook module add this code...

    Private Sub Workbook_Open()
        Application.OnKey "^{+}", "DisplayValuesInSelection"
    End Sub

    In a module paste this code....

    Public Function Amount(percentage As Integer, total As Integer) As Integer
        Dim max, min As Integer
        max = (total / 100) * percentage
        min = max - (max * 2)
        
        Randomize

        Dim random As Integer
        random = Int((max - min + 1) * Rnd() + min)

        Amount = total + random
    End Function

    Public Function DisplayValuesInSelection()
        Selection.Formula = "=Amount(1,1200)"
    End Function

    Save the work book and re-open. Select a bunch of cells and press CTRL and +  (at the same time) and all the cells should populate.



     

     

     

     


    "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks
    Wednesday, July 20, 2011 8:43 AM
  • Sorry - I was thinking about the percentage solution and not the INT - If INT is not part of the equation, then the +1 would definitely not be required, but because Rnd cannot be exactly one, the INT form cannot be greater than the upper bound. So I should have posted

    NewValue = (OldValue * 2 * PCT) * Rnd + OldValue * (1 - PCT)

     


    HTH, Bernie
    Wednesday, July 20, 2011 2:56 PM