Hi there,

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

ID, NAME, ADDRESS

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

I have created a random number in a cell away from this data (A13, as
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 the
number produced by the random function = the ID column, it should apply
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. it
does display an integer, the conditional formatting appears unable to
match it.

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


Many thanks,

Baldy


--
BaldySlaphead
------------------------------------------------------------------------
BaldySlaphead's Profile: http://www.excelforum.com/member.php...fo&userid=1260
View this thread: http://www.excelforum.com/showthread...hreadid=386779

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.)

BaldySlaphead wrote:

Hi there,

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

ID, NAME, ADDRESS

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

I have created a random number in a cell away from this data (A13, as
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 the
number produced by the random function = the ID column, it should apply
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. it
does display an integer, the conditional formatting appears unable to
match it.

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

Many thanks,

Baldy

--
BaldySlaphead
------------------------------------------------------------------------
BaldySlaphead's Profile: http://www.excelforum.com/member.php...fo&userid=1260
View this thread: http://www.excelforum.com/showthread...hreadid=386779

--

Dave Peterson

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

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=386779

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.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=386779

Thank you, sir, that works fine! :)


--
BaldySlaphead
------------------------------------------------------------------------
BaldySlaphead's Profile: http://www.excelforum.com/member.php...fo&userid=1260
View this thread: http://www.excelforum.com/showthread...hreadid=386779

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"
wrote in message
news:BaldySlaphead.1s45mw_1121267162.1931@excelfor um-nospam.com...

Thank you, sir, that works fine! :)


--
BaldySlaphead
------------------------------------------------------------------------
BaldySlaphead's Profile:
http://www.excelforum.com/member.php...fo&userid=1260
View this thread: http://www.excelforum.com/showthread...hreadid=386779

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!
==============================================


"RagDyeR" wrote in message
...
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"
wrote in message
news:BaldySlaphead.1s45mw_1121267162.1931@excelfor um-nospam.com...

Thank you, sir, that works fine! :)


--
BaldySlaphead
------------------------------------------------------------------------
BaldySlaphead's Profile:
http://www.excelforum.com/member.php...fo&userid=1260
View this thread: http://www.excelforum.com/showthread...hreadid=386779

RagDyeR Wrote:
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
------------------------------------------------------------------------
BaldySlaphead's Profile: http://www.excelforum.com/member.php...fo&userid=1260
View this thread: http://www.excelforum.com/showthread...hreadid=386779