The Excel Questions Thread

Discussion in 'General Chat' started by CunningSmeagol, May 4, 2009.

  1. JesterM

    JesterM Well-Known Member

    Messages:
    45
    Likes Received:
    0
    Joined:
    Oct 5, 2009
    I have a column of dates that I'm trying to get into the format yearmonthday only I don't want any characters or spaces between them, just values like this 20090418.

    Right now it looks like this:
    3/1/2007
    3/2/2007
    3/5/2007
    3/6/2007
    3/7/2007
    3/8/2007

    I can get it switched so it's year/month/day but I can't seem to get rid of the /'s. I've tried a couple of things like copying the column and just pasting the values but that doesn't work. Any ideas? Also it's not every day of the year, it excludes weekends and holidays. Thanks!
     


  2. Nobody Important

    Nobody Important Well-Known Member

    Messages:
    74
    Likes Received:
    3
    Joined:
    Oct 30, 2007
    I have a column of dates that I'm trying to get into the format yearmonthday only I don't want any characters or spaces between them, just values like this 20090418.

    Right now it looks like this:
    3/1/2007
    3/2/2007
    3/5/2007
    3/6/2007
    3/7/2007
    3/8/2007

    I can get it switched so it's year/month/day but I can't seem to get rid of the /'s. I've tried a couple of things like copying the column and just pasting the values but that doesn't work. Any ideas? Also it's not every day of the year, it excludes weekends and holidays. Thanks!


    Right-click a cell, go to Format Cells. Choose Custom. Type in yyyymmdd as the format. Verify that it's what you need. Select other cells, hit ctrl-Y to repeat the format.
     


  3. JesterM

    JesterM Well-Known Member

    Messages:
    45
    Likes Received:
    0
    Joined:
    Oct 5, 2009
    Right-click a cell, go to Format Cells. Choose Custom. Type in yyyymmdd as the format. Verify that it's what you need. Select other cells, hit ctrl-Y to repeat the format.

    Thanks! That was driving me crazy.
     


  4. dsgNYC

    dsgNYC Senior member

    Messages:
    422
    Likes Received:
    0
    Joined:
    Dec 9, 2008
    BUMP

    I'm bored...
     


  5. Milhouse

    Milhouse Senior member

    Messages:
    2,059
    Likes Received:
    0
    Joined:
    May 11, 2007
    How random is rand() and how good are the distribution functions in excel?
     


  6. jgold47

    jgold47 Senior member

    Messages:
    1,629
    Likes Received:
    13
    Joined:
    Mar 23, 2008
    Location:
    The Mitten
    How random is rand() and how good are the distribution functions in excel?

    Pretty damn random?? I use it to pick lottery #'s. Still havent won with it yet.
     


  7. AgentQ

    AgentQ Senior member

    Messages:
    433
    Likes Received:
    1
    Joined:
    Sep 26, 2008
    Location:
    Boston/NYC
    How random is rand() and how good are the distribution functions in excel?

    Seems like 2003 & 2007 are pretty good:
    http://support.microsoft.com/kb/828795

    No clue about other products, but if you're doing serious simulation work, consider something other than Excel for generating numbers.
     


  8. dsgNYC

    dsgNYC Senior member

    Messages:
    422
    Likes Received:
    0
    Joined:
    Dec 9, 2008
    How random is rand() and how good are the distribution functions in excel?

    Rand() is purely random for numbers between 0 and 1. You can also force it to return random values between two set values (say, 1 and 100), however it will never choose the top value of the range (in this case, 100. It'll return up to 99.999999999999).

    Here's a website that details choosing a random number between two given values:

    http://www.techonthenet.com/excel/formulas/rand.php

    As for distribution functions, I searched google and found the following website:

    http://www.exceluser.com/explore/statsnormal.htm

    Excel has a bunch of statistics formulas that you can find by clicking on the fx button next to the formula bar. That'll pull up a window, and you can choose the "Statistical" category from the drop-down box.
     


  9. Milhouse

    Milhouse Senior member

    Messages:
    2,059
    Likes Received:
    0
    Joined:
    May 11, 2007
    Interesting stuff, thanks. I never actually thought this would happen to me. . . but more and more I'm going with open source stuff for anything that I need to know exactly how it works.

    Perhaps I'm becoming a nerd. [​IMG]
     


  10. dsgNYC

    dsgNYC Senior member

    Messages:
    422
    Likes Received:
    0
    Joined:
    Dec 9, 2008
    Interesting stuff, thanks. I never actually thought this would happen to me. . . but more and more I'm going with open source stuff for anything that I need to know exactly how it works.

    Perhaps I'm becoming a nerd. [​IMG]


    I'm not familiar with any open source stuff specifically for Excel. What have you used and for what purpose?

    And yes, one you start using open source you're a nerd.
     


  11. Milhouse

    Milhouse Senior member

    Messages:
    2,059
    Likes Received:
    0
    Joined:
    May 11, 2007
    I'm not familiar with any open source stuff specifically for Excel. What have you used and for what purpose?

    And yes, one you start using open source you're a nerd.


    Open source FOR Excel? No idea.

    Open source replacements for Excel? Openoffice.org (has spreadsheet) and R (stats software).
     


  12. gomestar

    gomestar Super Yelper

    Messages:
    19,407
    Likes Received:
    3,871
    Joined:
    Oct 21, 2008
    Location:
    NYC
    Here's one that I can't seem to figure out in the Vista Excel.

    I have a document, 7400 rows and 227 columns. But, despite the rows I'm using, Excel is insisting on showing over 1,000,000 blank rows and it makes for a terrible time navigating the document. And forget about using the vertical scroll bar. How can I fix this?
     


  13. CunningSmeagol

    CunningSmeagol Senior member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    Here's one that I can't seem to figure out in the Vista Excel. I have a document, 7400 rows and 227 columns. But, despite the rows I'm using, Excel is insisting on showing over 1,000,000 blank rows and it makes for a terrible time navigating the document. And forget about using the vertical scroll bar. How can I fix this?
    Click on a cell in a row below 7400 (i.e. B7410) and hit ctrl+shift+down. Right click on the highlighted range (should go to the end of the workbook (1,000,000 rows)) and select "delete". Then select entire row, scroll all the way up, click inside your data set and save. This at least works in 2003. I've never used vista excel, but I would think the problem is the same, that is, excel remembers you once having edited and deleted the contents of some cells way down there.
     


  14. gomestar

    gomestar Super Yelper

    Messages:
    19,407
    Likes Received:
    3,871
    Joined:
    Oct 21, 2008
    Location:
    NYC
    Click on a cell in a row below 7400 (i.e. B7410) and hit ctrl+shift+down. Right click on the highlighted range (should go to the end of the workbook (66,000 something)) and select "delete". Then select entire row, scroll all the way up, click inside your data set and save.

    I tried deleting the cells (I am far from a novice), Excel said "cannot complete with the available resources" or something like that. then I tried in batches of a few thousand and got the same error message. And then I tried in batches of 10-15, no better.

    Official row count is 1,048,576 at this point. Just annoying.
     


  15. gomestar

    gomestar Super Yelper

    Messages:
    19,407
    Likes Received:
    3,871
    Joined:
    Oct 21, 2008
    Location:
    NYC
    This at least works in 2003. I've never used vista excel, but I would think the problem is the same, that is, excel remembers you once having edited and deleted the contents of some cells way down there.

    Yes, I know what you mean, the document was originally 20,000 rows. I can deal with 13,000 blank rows, but a million blows.
     


Share This Page

Styleforum is proudly sponsored by