excel help

[vertigo]

hi, im not very good with excel and need some help

i need to generate 3 random numbers between 0 and 9, and the numbers cannot be the same

ive been using the randbetween() function, but im just struggling to get them so they do not show the same numbers

please any help will be much appriciated

and id prefer not to use VB, if it is possible

many thanks

[Evil_Genious]

I'm not really a Excel programmer LOL.

But my penis tells me you need something called a "conditional" here.

i.e. you need to create a formula in a cell. So you say that randbetween() is a function which 'generates a random number between', assuming this is the case do something like the following. What the syntax is - is perhaps irrelevent.

Say you have a matrix A1 to E1 horizontal and A1 to A5.

If you want to generate a random number in A1 you would write randbetween();

So now you have a number to start with.

in A2 you want to a 'Do While' loop or something similar - if you are using VB, there must be a phenomenon as such.

would be something like:

Do randbetween() while (A2 == A1);

This will execute randbetween once and then evaluate the conditional statement, and if A2 'is equal to' A1 then another randbetween is done, until the condition evaluates to False. i.e. when the value is unique.

Subsequent formulas will be similar but with different cells:

A3:

Do randbetween() while (A3 == A1 || A3 == A2 || A3 == n);

etc......

With syntax, I can't help you.

[vertigo]

ive jsut cheated and done this

=RANDBETWEEN(2,7)
=RANDBETWEEN(0,D10-1)
=RANDBETWEEN(D10+1, 9)

but what you say sounds better, ill give it a go tomorrow, but one question is that a visual basic code, coz im not using any VB

but thanks for helping anyways

[Evil_Genious]

LOL yeah I see what you mean, but that formula is flawed!

Say the first number generated is 4 (which is D10) the second number will have a roof of 3 (because 4-1 is 3). Plus thats not really a random number, because you're confining it to logic.

I'm pretty sure there's a loop in there somewhere. Else why would there be mention of VB? At the mo you're only using a bog Excel function.

[vertigo]

i know its flawed, but im so pissed off with this shit, that i just dont care anymore, perhaps in the morning ill feel differently, been sitting in this library since 3pm doing nothing but coursework alll day

but yea cheers,,,,,and if anyone else who can come up with a better function id very much appreciate it

[vertigo]

just an update i fixed it, i joined some excel forum and got someone else to do the whole thing for me

shoulda done that from the start instead of wasting so much time

[vertigo]

I'm not really a Excel programmer LOL.

But my penis tells me you need something called a "conditional" here.

i.e. you need to create a formula in a cell. So you say that randbetween() is a function which 'generates a random number between', assuming this is the case do something like the following. What the syntax is - is perhaps irrelevent.

Say you have a matrix A1 to E1 horizontal and A1 to A5.

If you want to generate a random number in A1 you would write randbetween();

So now you have a number to start with.

in A2 you want to a 'Do While' loop or something similar - if you are using VB, there must be a phenomenon as such.

would be something like:

Do randbetween() while (A2 == A1);

This will execute randbetween once and then evaluate the conditional statement, and if A2 'is equal to' A1 then another randbetween is done, until the condition evaluates to False. i.e. when the value is unique.

Subsequent formulas will be similar but with different cells:

A3:

Do randbetween() while (A3 == A1 || A3 == A2 || A3 == n);

etc......

With syntax, I can't help you.

hahahaha i just got that now, i was quite stressed earlier so i didnt get that bit

[Evil_Genious]

just an update i fixed it, i joined some excel forum and got someone else to do the whole thing for me

shoulda done that from the start instead of wasting so much time

Show us the answer blad.

[vertigo]

i did rand() from A1:A10

then
=RANK(A1,$A$1:$A$10)-1
=RANK(A2,$A$1:$A$10)-1
=RANK(A3,$A$1:$A$10)-1


but!!!!! ive been going about this whole assignment wrong from the start....ididnt actually have to do that from the start, its a long thing to explain