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)

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)
>

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)*10+MID(A1,3,1),A1)
> --
> David Biddulph
>
> <greg.j...@yahoo.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > 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)?

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)*10+MID(A1,3,1),A1)
> --
> David Biddulph


David

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

[hash/pound sign]NAME

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...

> 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)*10+MID(A1,3,1),A1)
>> --
>> David Biddulph

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)*10+MID(A1,3,1),A1)
> --
> David Biddulph
>
> <greg.j...@yahoo.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > 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

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)*10+MID(A1,3,1),A1)
>> --
>> David Biddulph
>>
>> <greg.j...@yahoo.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>> > 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
>

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)

> 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
news:_(E-Mail Removed)...
> =IF(RANDBETWEEN(0,10)>7,LEFT(A1,2)*100+RIGHT(A1)*10+MID(A1,3,1),A1)
> --
> David Biddulph
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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)
>>
>
>

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)
>
>
>
>