I need to generate a list of numbers that fall within a certain range based
on a "base" percentage. I am generating a sample gradebook. Student "A" has a
semester grade of 83%. I need to generate a list of 24 "assignments" that
would average out to that 83% semester grade -- the student would get a few
95%; a couple of 70%; but the average would be 83 (or some given number) I am
certain there is an easy way to do this in excel . . .

Simple would be to use =RandBetween(desired average + 10, desired average -10) in each of the 24
assignement grades.

For example,

=RANDBETWEEN(73,93)

The numbers could be in cells that are referenced:

=RANDBETWEEN($A2,$B2)


HTH,
Bernie
MS Excel MVP


"emvpix" wrote in message
...
I need to generate a list of numbers that fall within a certain range based
on a "base" percentage. I am generating a sample gradebook. Student "A" has a
semester grade of 83%. I need to generate a list of 24 "assignments" that
would average out to that 83% semester grade -- the student would get a few
95%; a couple of 70%; but the average would be 83 (or some given number) I am
certain there is an easy way to do this in excel . . .

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 = 70
lngHigh = 100
lngTarget = 83
lngQuantity = 24

'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
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"emvpix"

wrote in message
I need to generate a list of numbers that fall within a certain range based
on a "base" percentage. I am generating a sample gradebook. Student "A" has a
semester grade of 83%. I need to generate a list of 24 "assignments" that
would average out to that 83% semester grade -- the student would get a few
95%; a couple of 70%; but the average would be 83 (or some given number) I am
certain there is an easy way to do this in excel . . .

certain there is an easy way to do this in excel . . .

One of a few ways...
Under Data Analysis, one can select "Random Number Generation."
Select a "Normal" distribution, and set a mean (83%), and a Standard
deviation of your choice.
Takes about 2 seconds in Excel 2003, and over 1 hour in Excel 2007. That's
how long it took me to find it. Excel 2007 is simply the worst program I
have ever seen.
--
HTH :)
Dana DeLouis
Windows XP & Office 2007


"emvpix" wrote in message
...
I need to generate a list of numbers that fall within a certain range based
on a "base" percentage. I am generating a sample gradebook. Student "A"
has a
semester grade of 83%. I need to generate a list of 24 "assignments" that
would average out to that 83% semester grade -- the student would get a
few
95%; a couple of 70%; but the average would be 83 (or some given number) I
am
certain there is an easy way to do this in excel . . .

Dana,
"Excel 2007 is simply the worst program I have ever seen."

I see in the news today that Dell has started offering Windows XP as
the operating system on six laptop/desktop units instead of Vista.
http://www.computerworld.com/action/...c=news_ts_head

Maybe there is hope that a "classic" version of Office 2007 is being held
in reserve by Microsoft?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Dana DeLouis"
wrote in message
certain there is an easy way to do this in excel . . .

One of a few ways...
Under Data Analysis, one can select "Random Number Generation."
Select a "Normal" distribution, and set a mean (83%), and a Standard
deviation of your choice.
Takes about 2 seconds in Excel 2003, and over 1 hour in Excel 2007. That's
how long it took me to find it. Excel 2007 is simply the worst program I
have ever seen.
--
HTH :)
Dana DeLouis
Windows XP & Office 2007

Or maybe at least a 'fix' that will allow us to move worthless junk off the
ribbon and put meaningful commands there.

"Jim Cone" wrote:


Dana,
"Excel 2007 is simply the worst program I have ever seen."

I see in the news today that Dell has started offering Windows XP as
the operating system on six laptop/desktop units instead of Vista.
http://www.computerworld.com/action/...c=news_ts_head

Maybe there is hope that a "classic" version of Office 2007 is being held
in reserve by Microsoft?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Dana DeLouis"
wrote in message
certain there is an easy way to do this in excel . . .

One of a few ways...
Under Data Analysis, one can select "Random Number Generation."
Select a "Normal" distribution, and set a mean (83%), and a Standard
deviation of your choice.
Takes about 2 seconds in Excel 2003, and over 1 hour in Excel 2007. That's
how long it took me to find it. Excel 2007 is simply the worst program I
have ever seen.
--
HTH :)
Dana DeLouis
Windows XP & Office 2007

Maybe it is just me, but it takes me 100 times longer to do anything w/
2007.
Don't even get me started on the Help system. All the links, Methods, &
Properties were removed in 2007.
Some of the wording of some function still refers to them, but they are not
there.
I have to spend a lot of time turning on another computer that has Excel
2003 just to read the help files. The list goes on ...
The op's question finally made me determined to find that Analysis Tookpak
that was supposedly built-in according to some help files that I've read in
the past.

--
Dana DeLouis

"Duke Carey" wrote in message
...
Or maybe at least a 'fix' that will allow us to move worthless junk off
the
ribbon and put meaningful commands there.

"Jim Cone" wrote:


Dana,
"Excel 2007 is simply the worst program I have ever seen."

I see in the news today that Dell has started offering Windows XP as
the operating system on six laptop/desktop units instead of Vista.
http://www.computerworld.com/action/...c=news_ts_head

Maybe there is hope that a "classic" version of Office 2007 is being held
in reserve by Microsoft?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Dana DeLouis"
wrote in message
certain there is an easy way to do this in excel . . .

One of a few ways...
Under Data Analysis, one can select "Random Number Generation."
Select a "Normal" distribution, and set a mean (83%), and a Standard
deviation of your choice.
Takes about 2 seconds in Excel 2003, and over 1 hour in Excel 2007.
That's
how long it took me to find it. Excel 2007 is simply the worst program I
have ever seen.
--
HTH :)
Dana DeLouis
Windows XP & Office 2007

Dana -

You wrote: The op's question finally made me determined to find that
Analysis Tookpak that was supposedly built-in according to some help files
that I've read in the past. <

The worksheet functions of the Analysis ToolPak are built into Excel 2007.

The Data Analysis tools still require installing the add-in, and the feature
then appears on Excel 2007's Data ribbon.

- Mike

"Dana DeLouis" wrote in message
...
Maybe it is just me, but it takes me 100 times longer to do anything w/
2007.
Don't even get me started on the Help system. All the links, Methods, &
Properties were removed in 2007.
Some of the wording of some function still refers to them, but they are
not there.
I have to spend a lot of time turning on another computer that has Excel
2003 just to read the help files. The list goes on ...
The op's question finally made me determined to find that Analysis Tookpak
that was supposedly built-in according to some help files that I've read
in the past.

--
Dana DeLouis

"Duke Carey" wrote in message
...
Or maybe at least a 'fix' that will allow us to move worthless junk off
the
ribbon and put meaningful commands there.

"Jim Cone" wrote:


Dana,
"Excel 2007 is simply the worst program I have ever seen."

I see in the news today that Dell has started offering Windows XP as
the operating system on six laptop/desktop units instead of Vista.
http://www.computerworld.com/action/...c=news_ts_head

Maybe there is hope that a "classic" version of Office 2007 is being
held
in reserve by Microsoft?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Dana DeLouis"
wrote in message
certain there is an easy way to do this in excel . . .

One of a few ways...
Under Data Analysis, one can select "Random Number Generation."
Select a "Normal" distribution, and set a mean (83%), and a Standard
deviation of your choice.
Takes about 2 seconds in Excel 2003, and over 1 hour in Excel 2007.
That's
how long it took me to find it. Excel 2007 is simply the worst program
I
have ever seen.
--
HTH :)
Dana DeLouis
Windows XP & Office 2007

"Jim Cone" wrote...
....
I see in the news today that Dell has started offering Windows XP as
the operating system on six laptop/desktop units instead of Vista.
....
Maybe there is hope that a "classic" version of Office 2007 is being held
in reserve by Microsoft?
....

Not too likely. Coke had Pepsi, RC and store brands to keep them
honest. Microsoft lacks competitors.