Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
random number
Could someone help me with the following problem in excel:
I would like to be able to generate 20 random numbers whereby the number generated is between say -15 and 40 where once all the numbers are generated the average of all 20 numbers is 8 for example. I guess I could generate 19 random numbers with the above parameters and the 20th number would be a plug to achieve the correct average but was wondering if there was another way. Thank you for your help. Answer can be either through using excel itself or through a macro code for excel. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
random number
Here is something you can play around with...
-- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Sub FindNumbersThatAverage() ' Provides random numbers that average a predetermined amount. ' Jim Cone - San Francisco, USA - May 29, 2005 Dim lngN As Long Dim lngLow As Long Dim lngTemp As Long Dim lngHigh As Long Dim lngTarget As Long Dim lngQuantity As Long Dim lngArray() As Long 'Establish parameters... lngLow = -15 lngHigh = 40 lngTarget = 8 lngQuantity = 20 'Sanity check If lngLow lngTarget Or lngHigh < lngTarget Then Exit Sub End If 'The number of numbers must be an even number <g If Not lngQuantity Mod 2 = 0 Then lngQuantity = lngQuantity + 1 End If ReDim lngArray(1 To lngQuantity) For lngN = 1 To lngQuantity Step 2 'Get random values between the high and low parameters. Randomize lngTemp lngTemp = Int(Rnd * (lngHigh - lngLow + 1)) + lngLow 'Assign random values lngArray(lngN) = lngTemp lngArray(lngN + 1) = 2 * lngTarget - lngTemp 'If the high/low range is not centered on the target average 'then the random results may need adjusting. If lngArray(lngN + 1) lngHigh Then lngArray(lngN) = 2 * lngTarget - lngHigh + lngN lngArray(lngN + 1) = lngHigh - lngN End If If lngArray(lngN + 1) < lngLow Then lngArray(lngN) = 2 * lngTarget - lngLow - lngN lngArray(lngN + 1) = lngLow + lngN End If Next 'lngN 'Stick it on the worksheet. Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, lngQuantity).Value = lngArray() End Sub '------------------- "Hookster23" wrote in message Could someone help me with the following problem in excel: I would like to be able to generate 20 random numbers whereby the number generated is between say -15 and 40 where once all the numbers are generated the average of all 20 numbers is 8 for example. I guess I could generate 19 random numbers with the above parameters and the 20th number would be a plug to achieve the correct average but was wondering if there was another way. Thank you for your help. Answer can be either through using excel itself or through a macro code for excel. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
random number
Thank you for your help
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Random Number generator | Excel Worksheet Functions | |||
Random letter and number generator | Excel Worksheet Functions | |||
random number generation | Excel Worksheet Functions | |||
random number without repeating? | Excel Worksheet Functions | |||
Random Number Questions | Excel Worksheet Functions |