Welcome Guest
Log In | Register )
You last visited December 2, 2016, 7:21 pm
All times shown are
Eastern Time (GMT-5:00)

Excel help Please!

Topic closed. 3 replies. Last post 11 years ago by winsumloosesum.

Page 1 of 1
PrintE-mailLink
Mickie's avatar - Lottery-008.jpg
Spring Lake
United States
Member #313
April 26, 2002
181 Posts
Offline
Posted: January 11, 2006, 7:05 pm - IP Logged

I am using this formula to make a pick 3 number straight. For example: 413 now becomes 134.

=IF(OR($A3="Ns",$A3="Nd"),"",CONCATENATE((MIN(LEFT(R3),MID(R3,2,1),RIGHT(R3))),(MEDIAN(LEFT(R3),MID(R3,2,1),RIGHT(R3))),(MAX(LEFT(R3),MID(R3,2,1),RIGHT(R3))))) 

How can I change this to make a Pick 4 number straight?

 

Thank you for your help

Mickie

    winsumloosesum's avatar - Lottery-060.jpg
    Pennsylvania
    United States
    Member #2218
    September 1, 2003
    5387 Posts
    Offline
    Posted: January 12, 2006, 10:57 am - IP Logged

    Mickie,

    I posted this a few weeks ago.

    For those who use Excel.

    Wanted to share this formula for those interested in converting a Pick 3 and Pick 4 drawn number to its ascending form.

    Example: Pick 4    4 3 9 0 = 0 3 4 9

    Pick 3    6 1 3 = 1 3 6

    The first thing you will need is to download the FREE Add-in form the folowing link:

    http://xcell05.free.fr/english/

    Scroll down the page until you see MOREFUNC.XLL

    Download, unzip and install.

    Open Excel>Tools>AddIns>select Morefunc in the list and select OK

    Go to the cell you want the Pick 3 or Pick 4 in ascending order and copy and paste the formula below into the formula box. You may need to change the formula from A1 in both places to reflect where you have the Pick 3 or Pick 4 number. The example below the formula is looking at cell A1 for either a Pick 3 or Pick 4 number. Press Control>Shift>Enter

    --MCONCAT(VSORT(MID(A1,INTVECTOR(LEN(A1),1),1)+0,,1))

    After you press Control>Shift>Enter you should now have the number in ascending order.


    Good Luck!!





      Mickie's avatar - Lottery-008.jpg
      Spring Lake
      United States
      Member #313
      April 26, 2002
      181 Posts
      Offline
      Posted: January 12, 2006, 12:59 pm - IP Logged

      Thank You!

      I did find that, it does work but it doesn't work with this in front=IF(OR($A3="Ns",$A3="Nd"),"",  of it.

      I use that to get rid of the #Value! that it brings if the cell is blank. I also tried the isblank formula also.

       

      Any other clues that might be helpful.

       

      Thank you again

      Mickie

        winsumloosesum's avatar - Lottery-060.jpg
        Pennsylvania
        United States
        Member #2218
        September 1, 2003
        5387 Posts
        Offline
        Posted: January 12, 2006, 3:04 pm - IP Logged

        Mickie,

        Check out your private message.