Create a unique number within constraints

[StuLux]

I need to create a formula that will create a unique number between 30.1 and 499.9 The number must be to one decimal place and not be a whole number e.g. 39.9, 421.3, 32.7 are OK but not 40.0, 100.0 etc.

I actually need to do this in MS Project but think I can convert an Excel formula - the one advantage in MS Project is the field for Unique ID I think I can use as the "seed" to create the number.

For the sake of this formula in Excel assume I have a column of unique whole numbers to start with but these could range from 1 to 100,000 and I need to convert them to a number within the constraints above. Therefore the formula needs to take the existing number, whatever its value, and convert it to a unique number between 30.1 and 499.9 as per above.

Hope this makes sense.

 

[Sal Paradise]

Go to Tools -> Add-ins -> Analysis Toolpack Add-in and add if you haven't before (otherwise you can't use RANDBETWEEN):

=RANDBETWEEN(30,499)+RANDBETWEEN(1,9)/10

That will do it. If you mean that you can't repeat the same number, then you will need to do a VBA solution of some sort, or create an algorithm. Can you just start at 30.1 and add .1 each line?

Something like:
=IF(MOD(A1,1)=0.9,A1+0.2,A1+0.1)