Please help me format Excel cell correctly - URGENT!

October 24, 2008 11:00 AM

I need to enter the following information into excel, but excel wants to transform one of them into a date format. This is for a statistics class and being used to make a graph of some kind.

These are ranges of data, not anything special, just numbers.
10-14, 15-19 etc. need to be in one cell. Each range of numbers must be in their own cell. By that I mean I can not enter the 10 in one cell and the 14 in another cell. The minus sign is not meant to subtract 14 from 10, but rather just show the range from 10 to 14. The following sets of numbers are in one column. Each "range" of numbers are in their own cell.

10-14,
15-19,
20-24,
25-29,
30-34,

Excel wants to make the cell with the 10-14 into Oct 14th or 10/14/08 - it automatically changes it to a date format cell. And into the number 39735 if I change the format of the cell to Number->General.

I just need the cells to stay just as I have enter the information into them. How can I do that?

posted by Jackie_Treehorn to computers & internet (9 comments total)

Set the format to 'Text' instead of 'General'. This should do it.
posted by clarkie666 at 11:05 AM on October 24, 2008 [1 favorite]

Start each value with an apostrophe. e.g. '10-14
posted by sanko at 11:05 AM on October 24, 2008 [1 favorite]

'10-14
posted by unixrat at 11:05 AM on October 24, 2008

Doh, beaten to the punch. The apostrophe means "treat this cell as text, even if it looks like numeric data".
posted by reptile at 11:07 AM on October 24, 2008

Though to be honest and not to question the process, you are losing out on a lot of the cool stuff that you can do with Excel if you put the values in like this, and I question if it can produce meaningful graphs. How about assigning two columns - the first with the first number, the second with the second number.

You can even create a third in-between the first and second and fill this with the 'dash' character. Then in the fourth column - you can use the following formula without the quotes "=A1&B1&C1" to automatically produce the 10-14 notation.
Then when producing graphs - the graphee can build using the individual numbers.

(on preview didn't know about the apostrophie, cool. still win though)
posted by clarkie666 at 11:12 AM on October 24, 2008 [1 favorite]

You might want to determine what kind of graph you're supposed to be making, by the way. For example, if you're generating a histogram and you've been given those as the bin ranges, you'll need to enter the data in a different way. Folks here will probably be willing to give more advice on how to get Excel to do precisely what you need to do if you can give more information about the task at hand.
posted by leahwrenn at 11:25 AM on October 24, 2008

Leahwren,

Bingo. The graph to be made is a histogram and the provided data in this post is the bin ranges.
posted by Jackie_Treehorn at 11:44 AM on October 24, 2008

You can even create a third in-between the first and second and fill this with the 'dash' character. Then in the fourth column - you can use the following formula without the quotes "=A1&B1&C1" to automatically produce the 10-14 notation.

If you do it this way, you don't need a third column with the dash; just make the combined-column formula like =A1 & "-" & B1.
posted by inigo2 at 1:31 PM on October 24, 2008

So, the Excel help files are actually pretty useful. Under histogram, it says

"This topic describes the available options for the Histogram data analysis tool (Tools menu, Data Analysis command). If the Data Analysis command is not available, you need to install and load the Analysis ToolPak add-in."

and then discussing bin range:

"Enter the cell reference for a range that contains an optional set of boundary values that define bin ranges. These values should be arranged in ascending order. Microsoft Excel counts the number of data points between the current bin number and the adjoining higher bin, if any. A number is counted in a particular bin if it is equal to or less than the bin number down to the last bin. All values below the first bin value are counted together, as are the values above the last bin value."

So, in fact, you don't want to enter ranges for the graphing part, although you might want to, conceivably, to label your histogram bars appropriately.
posted by leahwrenn at 1:49 PM on October 24, 2008