randbetween different than

**Ramzes** · 02-16-2011, 04:35 AM

Hi Guys,

I was trying to put a formula down to give me in one line of six cells a random number between a range of numbers (the same range for all cells) but in each cell the number to be different than in the others.

So if the range would be 1-99 then let me say:

5 66 24 ...

I have tried for the first cell:

Please Login or Register  to view this content.

for the next one I tried:

Please Login or Register  to view this content.

hoping that it will exclude the previous result.

Even when living a static value in cell one the cell two came back occassionaly with the same number.

Would anyone of you know if there is an alteration to that formula that I can use ar maybe a macro with connection to some formula to live values only when all the numbers deffire from each other?

I know how to make macro for values only but only bassed on selection / copy / paste special / values only. Don't know if there is a way to give conditions to copy.

Thanks for reading and giving it a go.

**Bob Phillips** · 02-16-2011, 05:18 AM

Here is one way

First, ensure cell A1 is empty and
Excel 2003: goto Tools>Options and on the Calculation tab
Excel 2007: goto Excel Options>Formulas>Calculation Options
check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell A2
=IF(($A$1="")+(AND(A2>0,COUNTIF($A$2:$F$2,A2)=1)),A2,RANDBETWEEN(1,99))
it should show a 0

Copy A2 down to F2.

Finally, put some value in A1, say an 'x', and all the random numbers will
be generated, and they won't change.

To force a re-calculation, clear cell A1, edit cell A2, don't change it,
just edit to reset to 0, copy A2 down to F2, and re-input A1.