Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I find averages?
I have a number, lets say 80. I want to find other values, lets say 10 number
values, between 50 and 100 such as that those numbers added have an average of 80. In other words: a + b + c + d + e + f + g +... = 80 How can I find random values between 50 and 100 (a, b, c, d, etc) whose average is 80. Is it possible in Excel 2000? thank you. Daniel Lugones |
#2
|
|||
|
|||
Can any of the numbers be zero?
-- Gary's Student "Daniel Lugones" wrote: I have a number, lets say 80. I want to find other values, lets say 10 number values, between 50 and 100 such as that those numbers added have an average of 80. In other words: a + b + c + d + e + f + g +... = 80 How can I find random values between 50 and 100 (a, b, c, d, etc) whose average is 80. Is it possible in Excel 2000? thank you. Daniel Lugones |
#3
|
|||
|
|||
Put =RAND()*16 in cell A1 and =16-A1 in cell B1 .
Copy these cells down thru row 5. Now the sum of A1:B5, which is a set of 10 numbers, will always equal 80. To get different numbers, just push F9. -- Gary's Student "Daniel Lugones" wrote: I have a number, lets say 80. I want to find other values, lets say 10 number values, between 50 and 100 such as that those numbers added have an average of 80. In other words: a + b + c + d + e + f + g +... = 80 How can I find random values between 50 and 100 (a, b, c, d, etc) whose average is 80. Is it possible in Excel 2000? thank you. Daniel Lugones |
#4
|
|||
|
|||
Daniel,
See if this comes close... '--------------------------------- 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 = 50 lngHigh = 100 lngTarget = 80 lngQuantity = 10 '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. Range("A1", Cells(1, lngQuantity)).Value = lngArray() End Sub --------------------------------- "Daniel Lugones" <Daniel wrote in message ... I have a number, lets say 80. I want to find other values, lets say 10 number values, between 50 and 100 such as that those numbers added have an average of 80. In other words: a + b + c + d + e + f + g +... = 80 How can I find random values between 50 and 100 (a, b, c, d, etc) whose average is 80. Is it possible in Excel 2000? thank you. Daniel Lugones |
#5
|
|||
|
|||
Do you want the total or the average to be 80 (in the equation you have shown
the total as 80)? By the very nature of this problem, only 9 of those 10 numbers can be truly random (the 10th is constrained by the fact that the average of the 10 numbers has got to be 80). Generate 9 random numbers between 50 and 100, using =(rand()+1)*50, say in A1 thru A9. The 10th number (A10) is =800-sum(A1:A9). Due to the random nature of the first 9 numbers, the 10th may not necessarily fall in the required range, 50-100; hit F9 repeatedly till the 10th number also falls in that range (this may need several attempts). B.R.Ramachandran "Daniel Lugones" wrote: I have a number, lets say 80. I want to find other values, lets say 10 number values, between 50 and 100 such as that those numbers added have an average of 80. In other words: a + b + c + d + e + f + g +... = 80 How can I find random values between 50 and 100 (a, b, c, d, etc) whose average is 80. Is it possible in Excel 2000? thank you. Daniel Lugones |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What function would let me find Batting Averages? | Excel Worksheet Functions | |||
What function would let me find Batting Averages? | Excel Worksheet Functions | |||
What function would let me find Batting Averages? | Excel Worksheet Functions | |||
What function would let me find Batting Averages? | Excel Worksheet Functions | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) |