Calling the Excel Guru's !!

[markwales]

Excel Guru's,

I'm really not sure if this can be done or not....but it's got to be worth a try :D

I've been working on an automated excel sim training spreadsheet, which works well thus far......but now I'm stuck!

What I need to do is the following.....

If Cell A1 = 1 then give me a random number between 6 and 10, else
If Cell A1 = 2 then give me a random number between 7 and 10, else
If Cell A1 = 3 then give me a random number between 8 and 10, else
If Cell A1 = 4 then give me a random number between 9 and 10, else
If Cell A1 = 5 then 10

Can this be done?...and if so, how?

I'm reasonably confident it CANNOT be done....

Thanks in advance.
Mark.

[mathare]

Can you use a macro? I know it can be done with a macro but are you trying to avoid them for any reason?

[mathare]

It can be done :)

Try this:
=IF(A1=1,6+ROUND(4*RAND(),0),IF(A1=2,7+ROUND(3*RAND(),0),IF(A1=3,8+ROUND(2*RAND(),0),IF(A1=4,9+ROUND(RAND(),0),IF(A1=5,10,"")))))

Assuming you want integer values ie 6, 7, 8, 9 or 10 only

[markwales]

Macro's are fine.....but got no idea how to program it :(

The only macro's I use currently are recorded macro's rather than written ones. (does that make sense?)

I'll scour t'internet for a tutorial

[mathare]

I could write you the macro if preferred but I think the above should work. Not thoroughly tested but the logic makes sense to me

[markwales]

It can be done :)

Try this:
=IF(A1=1,6+ROUND(4*RAND(),0),IF(A1=2,7+ROUND(3*RAND(),0),IF(A1=3,8+ROUND(2*RAND(),0),IF(A1=4,9+ROUND(RAND(),0),IF(A1=5,10,"")))))

Assuming you want integer values ie 6, 7, 8, 9 or 10 only

Mathare = GENIUS !!!

Many many thanks.....it does exactly what I wanted it to do :D:D:D

Rep on the way fella.

[vegyjones]

What is +ROUND for?

[mathare]

What is +ROUND for?ROUND rounds a number off to a given number of decimal places. Syntax is ROUND(<number>, <no. decimal places>) so in this case:

ROUND (random number between 0 & 1, to zero decimal places) ie a random number that is either 0 or 1, both equally likely. This can be multiplied by any factor you choose and added to a constant so when A1=1 a randon number between 0 and 4 is added to 6 to give a random number between 6 and 10.

Had Mark wanted to include say tenths of a point the number after the comma in the ROUND call would have been 1. Then we would have got a random number rounded to the nearest 0.1.

[vegyjones]

Cheers Mat! :D

[MarcusMel]

Vegy Rand() supplies a random number between 0 and 1. If you then multiply that number 10 you for example 3.1853016 but you are only intersted in the '3' so you round up the number with no decimal places. However if you have 3.77393 and round up with no decimal places you get 4 as the answer.

[mathare]

It's a function I always use when writing betting spreadsheets, rounding stakes and returns to 2dp so that everything is in pounds and pence, not fractions of a penny which when summed can put your totals a few pence out. And you can spend hours searching for where the few pence difference in your sum and the expected total has come from. Trust me, I have done it

[vegyjones]

I always go

Format > Cell > Number

and tfrom there you can select 2 decimal places!

But, in mty efforts to become an Excel Nephew :D
I wjust thought I should understand Mat's equation
as best I could

So fanks to Marcus and Mat! :D

And Marcus, if you can still think clearly n your birthday,
you ain't drinking fast enough :laugh

[mathare]

Using Format > Cell > Number will not change the underlying value though Vegy.

If you have 3.444444 and 5.181818 in cells they will display as 3.44 and 5.18 but the sum will display as 8.63, not 8.62 as you may expect since the true sum is 8.626262. Changing the cell formatting doesn't change the true value, just the way it is displayed to you. This is particularly annoying for dates as regardless of how you choose to display them Excel treats them as a serial number (number of days since 1st Jan 1900 or 1st Jan 1904 depending on settings)

[vegyjones]

Okay, I got ya! :D