Applying random changes to a list of numbers

[greg.jobs]

Using excel, if I have a list of numbers

e.g.
1000
2223
2448
1229
3883
2238
4488
2388

etc.


Is there a way to do it
- Generate a random number between 0 and 10
- If number is greater than 7 then change the number so that the last
digit & the second last digit are tranposed (e.g. 2448 becomes 2484)

 

[David Biddulph]

=IF(RANDBETWEEN(0,10)>7,LEFT(A1,2)*100+RIGHT(A1)*10+MID(A1,3,1),A1)

 

[greg.jobs]

=IF(RANDBETWEEN(0,10)>7,LEFT(A1,2)*100+RIGHT(A1)*10+MID(A1,3,1),A1)

Thanks! Very grateful

Is there a way to get excel to only display the numerical result of
the formula (i.e. if someone tries to edit a cell, rather than
displaying the formula it displays the number only)?

 

[greg.jobs]

=IF(RANDBETWEEN(0,10)>7,LEFT(A1,2)*100+RIGHT(A1)*10+MID(A1,3,1),A1)

David

i've just tried that, but in excel it displays:

[hash/pound sign]NAME

 

[David Biddulph]

For the answer to the #NAME error in that situation, look up RANDBETWEEN in
Excel help.
--
David Biddulph

David

i've just tried that, but in excel it displays:

[hash/pound sign]NAME

 

[greg.jobs]

=IF(RANDBETWEEN(0,10)>7,LEFT(A1,2)*100+RIGHT(A1)*10+MID(A1,3,1),A1)

David,

I am very interested in how you have done this:
=LEFT(A1,2)*100+RIGHT(A1)*10+MID(A1,3,1)

You have successfully found a way to transpose the 3rd and 4th number.
Is it just as easy to transpose the 1st and 2nd number (e.g. so that
2839 becomes 8239)

Thanks again

 

[David Biddulph]

LEFT, RIGHT, and MID are all text manipulation functions.
If there is any Excel function that you don't understand, look it up in
Excel help. They are all (with the one exception of DATEDIF) listed there,
and it will tell you the syntax, give examples, and often (through a "See
also" link) show related functions.

Yes, something similar would let you swap the 1st 2 digits.

 

[greg.jobs]

David,

Thanks for the tip. I have just read up the basics on this page -
http://www.excel-vba.com/excel-22-text-formulas.htm (a good read for
beginners)

I think I fully understand how to do it now!

One more thing -

I want to tell excel to do something DEPENDING on how long the number
is (I presume using the LEN formula)

so if the number is

1234, I want it to change to 1324 - I can do this using
=LEFT(A1,2)*100+RIGHT(A1)*10+MID(A1,3,1)

However, if the number is 12345, I want it to change to: 12354

(and if the LEN is neither 4 nor 5 digits long, I just want it to
leave the number alone)

 

[Dana DeLouis]

LEFT(A1,2)*100+RIGHT(A1)*10+MID(A1,3,1)

It appears that the equation for a 4-digit number can also be written as:

=A1-9*(MID(A1,3,1)-RIGHT(A1))

 

[David Biddulph]

Yes, LEN is the function to use in your IF statement.