Create a unique number within constraints

StuLux

Well-known Member
Joined
Sep 14, 2005
Messages
643
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,087
Messages
5,599,654
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top