| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
|
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)? |
|
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
|
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) |
|
#9
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
|
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) |
|
#10
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
|
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) |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do i insert numbers from Random list (1-60) without repeating | Excel Discussion (Misc queries) | |||
| how do i insert numbers from Random list (1-45) without repeating | Excel Discussion (Misc queries) | |||
| How can I create a list of random numbers with no duplicates? | Excel Discussion (Misc queries) | |||
| I wish to change the last digit in a list of random numbers. To a. | Excel Worksheet Functions | |||
| Random list (1-45) without repeating numbers? | Excel Worksheet Functions | |||
Linear Mode
