The Excel Questions Thread

JesterM · Oct 18, 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!

Nobody Important · Oct 18, 2009

Originally Posted by JesterM

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.

JesterM · Oct 18, 2009

Originally Posted by Nobody Important

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.

dsgNYC · Nov 1, 2009

BUMP

I'm bored...

Milhouse · Nov 12, 2009

How random is rand() and how good are the distribution functions in excel?

jgold47 · Nov 12, 2009

Originally Posted by Milhouse

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.

AgentQ · Nov 12, 2009

Originally Posted by Milhouse

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.

dsgNYC · Nov 12, 2009

Originally Posted by Milhouse

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.

Milhouse · Nov 12, 2009

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.

dsgNYC · Nov 12, 2009

Originally Posted by Milhouse

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.

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.

Milhouse · Nov 12, 2009

Originally Posted by dsgNYC

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

gomestar · Nov 13, 2009

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?

CunningSmeagol · Nov 13, 2009

Originally Posted by gomestar

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.

gomestar · Nov 13, 2009

Originally Posted by CunningSmeagol

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.

gomestar · Nov 13, 2009

Originally Posted by CunningSmeagol

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.

The Excel Questions Thread

Well-Known Member

Well-Known Member

Well-Known Member

Senior Member

Distinguished Member

Distinguished Member

Senior Member

Senior Member

Distinguished Member

Senior Member

Distinguished Member

Super Yelper

Distinguished Member

Super Yelper

Super Yelper

FEATURED PRODUCTS

Similar threads

Featured Sponsor

Definitely full canvas only

Half canvas is fine

Really don't care

Depends on fabric

Depends on price

Forum Sponsors

Forum statistics

FOLLOW STYLEFORUM ON: