MS Excel 2003 Problem: Why won't formulae work?

Factual Questions
1

I am an experienced Microsoft Excel user.

We recently bought a new computer, and I purchased Microsoft Office with the computer. I’m running Excel 2003, which is a version or two newer than I’ve used in the past.

I’ve run into a problem where Excel flat out won’t calculate formulae I put in there.

For instance, if I have a value in cell F6 that I want to use elsewhere on the sheet, I can normally just type “=F6” elsewhere in the sheet, and that value is there. For some reason, at selected times, this doesn’t work for me. I type in “=F6”, all I see in the cell is “=F6” instead of my copied value.

With a lot of rigamarole, I have sometimes gotten around that. For instance, I can sometimes move elsewhere on the sheet and type in “=F6”, and if I find a spot where it works, I can then cut and paste that formula cell to where I originally wanted to have it, and it works. This works, but I don’t like having to brute-force this. It takes what should normally take me about 5 seconds to do and turns it into a 2-3 minute task.

Also, the sum function is now selectively not working for me.

I have a 128-member column of numbers, and wish to sum them. I put in “=sum(F1:F128)”, and instead of giving me the answer, it just shows 0.

Does anyone have a clue what could be going on? I have checked the format of the cells. They were defaulted to a format of text, but I have changed that to Number. I can add up individual numbers ("=F1+F2+F3") and get the correct numerical result, but if I just try "=sum(F1:F3), it doesn’t work.

If anyone has any advice to offer, I’d appreciate it. I’m not doing anything at all exotic here — this is about as basic as you can get.

2

I think it is a formatting problem. Starting any formula in Excel with “=” followed by the cell reference should put that value in the cell. Have you tried changing the format of the cells experiencing this problem to “General”? If I remember correctly, that is what they should be defaulting to unless you have changed it. (Of course it’s difficult to give an exact answer to this without actually seeing the problem spreadsheet file in question.)

3

Also, have you tried downloading the latest updated to MS Office 2003? It’s possible this is a bug that they know about. You could also try searching Microsoft’s Knowledgebase.

4

I just checked with Access 2000. It gave me that effect when the cells in question were formatted as text.

5

This is specifically caused by formatting a cell as text, either explicitly or implicitly.

Cells can end up as text if you import data into Excel and select Text rather than General as the column format. The default is General, and by hard experience I’ve learned to leave it that way!

Cells can also implicitly end up as text if you add a column next to a text column. Excel defaults to creating the column with the same format as the column to the left. Since the UI normally doesn’t indicate the format of a cell or column, you may get unexpected results.

This weirdness with formulas is one of the more annoying “features” of Excel. For a long time, I’ve had a cartoon posted on my wall at work. On the left side is the top view of a cockroach, and under it the label “Bug”. On the left side is another cockroach, this one wearing what looks like a long-tailed evening jacket. The label under it is “Feature”.

6

Thank you, Dragwyr, Canadjun, and 633squadron. With your help, I now not only have the problem fixed, but I know what was causing it.

I fixed the problem by changing the formatting to “General” as you suggested, Dragwyr. When I had checked the formatting earlier and seen that it was listed as Text, I had changed the format to Number, but that didn’t solve the problem. (Well, it would let me add the cells, but it wouldn’t let me sum the cells.) When I chose General as an option, it popped up with a comment box saying that my numbers had been converted to left justified text; did I want to convert it to numbers? When I chose that option, everything worked from then on.

And you correctly diagnosed what was causing the problem, 633squadron. I had imported a large chunk of the data in this spreadsheet from someone else’s spreadsheet. (I always just leave my formatting at the default of general, and never use text formatting; that’s why I’d never run into this particular bug …er, “feature” … before.) And I had indeed inserted some columns within that other dataset and entered my own numbers into it, so that’s why my data picked up the dreaded text formatting.

I really appreciate your help. You were infinitely more helpful and easier to navigate than the time I wasted on Microsoft Office’s online “Help.”

:slight_smile:

7

That’s one of the obligatory Excel scars everyone has to earn. It looks as excel has two separate levels of ‘format’ - one that’s controlled via the Format Cell dialog, and one that is a lower-level one that you can’t override. The newer versions of excel will sometimes offer to correct ‘text numbers’ to proper numbers, but it’s still a PITA. The spreadsheet assistant helps with some of this (convert text numbers to numbers) is sometimes helpful, but often the only solution is to paste over the offending column (if it’s blank) or else do something like =f6*1 in another column and paste special values and formats over the problem area.