Applying random changes to a list of numbers
 

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)

Applying random changes to a list of numbers
 

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

wrote in message
ups.com...
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)

Applying random changes to a list of numbers
 

On Sep 20, 10:03 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
=IF(RANDBETWEEN(0,10)7,LEFT(A1,2)*100+RIGHT(A1)*1 0+MID(A1,3,1),A1)
--
David Biddulph

wrote in message

ups.com...

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)

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

Applying random changes to a list of numbers
 

On Sep 20, 10:03 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
=IF(RANDBETWEEN(0,10)7,LEFT(A1,2)*100+RIGHT(A1)*1 0+MID(A1,3,1),A1)
--
David Biddulph


David

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

[hash/pound sign]NAME

Applying random changes to a list of numbers
 

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

wrote in message
ups.com...

David

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

[hash/pound sign]NAME

On Sep 20, 10:03 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
=IF(RANDBETWEEN(0,10)7,LEFT(A1,2)*100+RIGHT(A1)*1 0+MID(A1,3,1),A1)
--
David Biddulph

Applying random changes to a list of numbers
 

On Sep 20, 10:03 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
=IF(RANDBETWEEN(0,10)7,LEFT(A1,2)*100+RIGHT(A1)*1 0+MID(A1,3,1),A1)
--
David Biddulph

wrote in message

ups.com...

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,

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

Applying random changes to a list of numbers
 

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.
--
David Biddulph

wrote in message
ups.com...
On Sep 20, 10:03 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
=IF(RANDBETWEEN(0,10)7,LEFT(A1,2)*100+RIGHT(A1)*1 0+MID(A1,3,1),A1)
--
David Biddulph

wrote in message

ups.com...

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,

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

Applying random changes to a list of numbers
 

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)

Applying random changes to a list of numbers
 

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

--
Dana DeLouis


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=IF(RANDBETWEEN(0,10)7,LEFT(A1,2)*100+RIGHT(A1)*1 0+MID(A1,3,1),A1)
--
David Biddulph

wrote in message
ups.com...
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)

Applying random changes to a list of numbers
 

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

wrote in message
ups.com...
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)

Applying random changes to a list of numbers
 

On 20 Sep, 21:44, wrote:
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)

notes





SIX DIGIT NUMBER

get the same

=LEFT(Q9,1)*100000+MID(Q9,2,1)*10000+MID(Q9,3,1)*1 000+MID(Q9,4,1)*100+MID(Q9,5,1)*10+MID(Q9,6,1)*1



tranpose 2 digits

=LEFT(Q9,1)*100000+MID(Q9,2,1)*10000+MID(Q9,4,1)*1 000+MID(Q9,3,1)*100+MID(Q9,5,1)*10+MID(Q9,6,1)*1




=IF(RANDBETWEEN(0,10)7,LEFT(Q9,1)*100000+MID(Q9,2 ,1)*10000+MID(Q9,4,1)*1000+MID(Q9,3,1)*100+MID(Q9, 5,1)*10+MID(Q9,6,1)*1,Q9)