Conditional Formating & Rand() function

[BaldySlaphead]

Hi there,

I have an Excel 97 (I know, not my fault - blame work!) workbook wit
the following column headings:

ID, NAME, ADDRESS

ID is numbered with integers from 1-9 and the others populated a
appropriate.

I have created a random number in a cell away from this data (A13, a
it happens), using

=RAND()*(9-1)+1

As you all no doubt know, this creates a random number between 1 & 9
The number it produces is not an integer, and is, I have little doubt
the root of my problem.

What I wanted to do was apply conditional formatting such that if th
number produced by the random function = the ID column, it should appl
conditional formatting to the cell, thus highlighting the chosen line.

Even if I set the format of A13 to have no decimal places - i.e. i
does display an integer, the conditional formatting appears unable t
match it.

Probably really simple, but it's annoying me! Anyone help? :confused


Many thanks,

Bald

 

[Dave Peterson]

If you made it an integer, would that help:

=int(RAND()*(9-1))+1

(You could use =int() in the conditional formatting formula, too--but I think
just changing the formula would be easier.)

 

[BenjieLop]

1. In Cell A13, enter the formula

=INT(RAND()*(9-1)+1)

2. In Cell A1, enter the number "1" (without the quotes)

3. Go to Format/Conditional Formatting

4. Choose "Formula Is"

5. Type "=A1=$A$13" (without the quotes)

6. Click on "Format" and choose the color you want to use

7. Click OK

8. Click OK

9. Go back to Cell A1 and place your cursor in the lower right corner
until you see
a black plus sign (hold this position)

10. Press "Ctrl" and note that there is a small plus sign that will
appear

11. Left click on your mouse (while still pressing on the "Ctrl" key)

12. Drag down until A9


As usual, there maybe a more elegant solution but meantine, this will
work for your situation.

Regards.

 

[BaldySlaphead]

Thank you, sir, that works fine!

 

[BenjieLop]

You are welcome ... just glad that I can help.

Regards

 

[RagDyeR]

The only problem with your formula is that you'll *never* get a "9".

This bug (wrong information) has been around for years in the Help files,
where I'll bet, is where you got this formula from.

Try this instead:

=INT(RAND()*(9)+1)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"BaldySlaphead" <[email protected]>
wrote in message

Thank you, sir, that works fine!

 

[RagDyeR]

Actually, the info is correct, *except* when using Int().

Either of these produce the range you want (1 to 9):

=ROUND(RAND()*(9-1)+1,0)

=INT(RAND()*(9)+1)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


The only problem with your formula is that you'll *never* get a "9".

This bug (wrong information) has been around for years in the Help files,
where I'll bet, is where you got this formula from.

Try this instead:

=INT(RAND()*(9)+1)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"BaldySlaphead" <[email protected]>
wrote in message

Thank you, sir, that works fine!

 

[BaldySlaphead]

Actually, the info is correct, *except* when using Int().

Either of these produce the range you want (1 to 9):

=ROUND(RAND()*(9-1)+1,0)

=INT(RAND()*(9)+1)
--

HTH,

RD

RD,

Many thanks for your assistance. You're quite correct that it wasn't
generating a 9 - I must have kept F9 depressed for a couple of minutes
and not one sign - okay, it could happen but I'm willing to accept
you're right and the formula doesn't work!

I have an additional enquiry, out of pure interest. Would I be able to
record which number is generated in any way? What I imagined was
setting up an additional COL 'Results' and then trying to increment an


if A13=1 then x=x+1

style argument for each unique line to increment the count each time
the screen was refreshed. Thus I could see how often a number was
picked by the RAND.

Is this possible, and how might I do it? Any comments gratefully
received.

Regards,

Baldy

 

[RagDyeR]

I don't know where or how you're utilizing the returns of your formula, but
wouldn't simply copying it down a column give you the type of list that
you're looking for?

You could remove the formulas, via <Copy - PasteSpecial - Values>, leaving
the random numbers behind.

"BaldySlaphead" <[email protected]>
wrote in message

Actually, the info is correct, *except* when using Int().

Either of these produce the range you want (1 to 9):

=ROUND(RAND()*(9-1)+1,0)

=INT(RAND()*(9)+1)
--

HTH,

RD

RD,

Many thanks for your assistance. You're quite correct that it wasn't
generating a 9 - I must have kept F9 depressed for a couple of minutes
and not one sign - okay, it could happen but I'm willing to accept
you're right and the formula doesn't work!

I have an additional enquiry, out of pure interest. Would I be able to
record which number is generated in any way? What I imagined was
setting up an additional COL 'Results' and then trying to increment an


if A13=1 then x=x+1

style argument for each unique line to increment the count each time
the screen was refreshed. Thus I could see how often a number was
picked by the RAND.

Is this possible, and how might I do it? Any comments gratefully
received.

Regards,

Baldy

 

[BaldySlaphead]

I don't know where or how you're utilizing the returns of your formula
but
wouldn't simply copying it down a column give you the type of lis
that
you're looking for?

You could remove the formulas, via <Copy - PasteSpecial - Values>
leaving
the random numbers behind.

I don't believe so!

Every time I refresh the data, the Rand will recalculate. For a bit o
fun, I wanted to record that the Rand had generated the result 1 te
times, 2 seven time, 3 not at all etc etc

 

[RagDyeR]

Like I said, I don't know *how* you're using your data, but copying that
formula down a column, and then eliminating the formula, leaving the random
numbers behind, gives you a list of random numbers that you can *USE* by
polling down that column, in the same way that you're using the return of
the single formula.

If you can do that (use the random numbers in the list), then you have
exactly what you're looking for, and you'll even know ahead of time, exactly
what random return will be used next.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"BaldySlaphead" <[email protected]>
wrote in message

I don't know where or how you're utilizing the returns of your formula,
but
wouldn't simply copying it down a column give you the type of list
that
you're looking for?

You could remove the formulas, via <Copy - PasteSpecial - Values>,
leaving
the random numbers behind.

I don't believe so!

Every time I refresh the data, the Rand will recalculate. For a bit of
fun, I wanted to record that the Rand had generated the result 1 ten
times, 2 seven time, 3 not at all etc etc.