Generate a number between min and max value

**Mrstickball** · 01-07-2010, 03:18 AM

First time poster. Been using Excel for awhile. I am not a savy user by any means, so this may be an easy question.

Here is what I am needing:

I have three values for for a row:
A minimum value
A maximum value
An known number

What I need is a way to insert a command in a cell that will create a number that doesn't exceed the min/max values in each column, but can be multiplied by the known number.

Example:

A product has a minimum value of '100' and a maximum value of '500' and an known value of '40'.

The function would be able to assign a value in a 4th cell - between 2.5 and 12.5 (it doesn't matter which value it returns, as long as it is correct), as those would be the only correct answers. I am unsure if this requires a macro which tries every number until it is greater than the minimum or what.

I hope I have explained this properly. If you need an Excel spreadsheet with what I need, I can do that (but I don't have it available to produce at this very second).

Thank you so much for your time.

**teylyn** · 01-07-2010, 03:42 AM

If you're using XL 2007, and the number does not matter, you could use RANDBETWEEN() where you can specify the minimum and maximum limits for a random number to be generated.

=RANDBETWEEN(2.5,12.5)

hth

**Mrstickball** · 01-07-2010, 03:44 AM

That would work for that specific problem, however, I have multiple rows with various min, max, and known values.

**rwgrietveld** · 01-07-2010, 03:44 AM

Option 1) The value is random between 2.5 and 12.5

Use =floor(rand()*10+2.5,0.1)

Option 2) User cannot enter values outside 2.5 and 12.5. Use Validation 'between'

**rwgrietveld** · 01-07-2010, 03:46 AM

I do not see the relation between 40, 100, 500, 2.5 and 12.5

Show a few examples please.

**teylyn** · 01-07-2010, 03:50 AM

I have multiple rows with various min, max, and known values.

If these values are stored somewhere, you can have the formula refer to them. Say in column A you have the min value, in column B you have the max value. Different in each row. So you can use in row 1

=RANDBETWEEN(a1,b1)

and copy this down. The values in columns A and B will be applied for each respective row.

hth

**daddylonglegs** · 01-07-2010, 06:05 AM

You say you want to generate a random number between 2.5 and 12.5 for your example but you don't say what numbers would be allowed.

If you have MIN value (100) in A2, MAX value (500) in B2 and "known number" (40) in C2 then you could use either

=RANDBETWEEN(A2,B2)/C2

or

=RAND()*(B2-A2)/C2+A2/C2

both will give you numbers between 2.5 and 12.5 but the former will only be in increments of 1/C2 (assuming A2 and B2 are always integers), e.g. where C2 is 40, 10.95 or 7.525. While the second can give any number between 2.5 and 12.5, e.g. 10.9999979 [but it will never give you exactly 12.5]