using random between formular

[tonylpcs@yahoo.co.uk]

can someone give me a way to use the random between forulra "=RANDBETWEEN(B2,B3)" but to get it to not use the same number as it used in the cells above and below,

i.e if this is in cell D4 but its also doing the same in D3 and D2 to exclude those numbers?

been trying but cant get there!

thanks

Tony

 

[schielrn]

There isn't s straight-forward one forumla for this unless you create a UDF, but here is one approach to achieving this:

http://www.exceltip.com/st/Selecting_a_Set_of_Random_Numbers,_Avoiding_Duplicates/1110.html

I would also have a look at the free download of ASAP-Utilities as it has a tool for this and many other things that are very useful. Here are also some UDF's that will do this as well:

http://www.cpearson.com/excel/RandomNumbers.aspx

Hope that helps.

 

[CharlesChuckieCharles]

ignore that

isn't that just random anyway are you trying for a lottery number generator

 

[tonylpcs@yahoo.co.uk]

Can I have a code then?
please?

 

[tonylpcs@yahoo.co.uk]

sorry but i cant get it to help so far?

any other ideas?

 

[schielrn]

This formula appears to work:

Excel Workbook
	A
2	1
3	2
4	3
5	10
6	7
7	6
8	8
9	9
10	5
11	4
Sheet1

#VALUE!

You will need to change the A$1:A1 to whatever is the cell above where you are starting the data, so this formula cannot be put into row 1. Also there are other changes that would need to be made depending on size of the list and so forth.

Hope that helps.

 

[tonylpcs@yahoo.co.uk]

Thanks,

Its so close i can taste it, but still wrong!

Lets go back a little and I will explain in more detail.


the idea way for me to do this would be:
cell C3 has the start number i.e "1" and E3 has the finnish number i.e "10" (but finish number can change!)

in Cells D5 going down I have Cells that need to randomly pick a number between the lowerst Cell C3 and the highest Cell E3, but each cell must not have the same number as any other cell,

So whats in D5 must be excluded from D6 when it selects a number and what in D6 and D5 must be excluded from D7's list of numbers to choose from and so on

sounds simple doesnt it?

but can i get anything to work? not yet,

please help

Tony

 

[schielrn]

I think this is maybe what you are saying for your specific example:

Excel Workbook
	A	B	C	D	E
1
2
3			1		10
4
5				8
6				7
7				2
8				6
9				3
10				9
11				1
Sheet1

#VALUE!

Hope that helps. Here it also is with a little bit bigger number:

Excel Workbook
	C	D	E
3	1		20
4
5		1
6		2
7		3
8		12
9		6
10		9
11		17
Sheet1

#VALUE!

I did try with a different starting number other than 1 and it did not work, so it would need to be modified if your starting number can be something other than 1?

 

[schielrn]

Actually this should work for any starting and finishing number:

Excel Workbook
	A	B	C	D	E
1
2
3			5		20
4
5				8
6				16
7				7
8				9
9				15
10				14
11				11
Sheet1

#VALUE!

Hope that helps.