Answered by:
Visual Basic Macro to Randomize a Number based on a percentage
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,
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 thoughtstuff. 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
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 thoughtstuff. 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

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 SubI 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) ?

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 thoughtstuff. He builds his castles in the air, from air, creating by exertion of the imagination."  Fred Brooks 

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 
@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 SubIn 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 FunctionSave the work book and reopen. 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 thoughtstuff. He builds his castles in the air, from air, creating by exertion of the imagination."  Fred Brooks 
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