cells not calculating

[maacmaac]

I am having a problem with some of the cells in a spreadsheet not calculating.

For example, in column A, all the cells have formulas that pull data from another sheet. When I enter the formula, only the formula shows (not the result). I can't figure out what is wrong as the formulas in column B work fine (the results are showing, but not using the same data that column A is).

I am thinking it may have something to do with the data being used to calculate the formulas but not sure. How can I resolve this?

[Special-K]

Press F9, see if it produces the correct result. If so check your Excel settings, maybe you have calculate automatically turned off. Or you could post the file here?

[DonkeyOte]

It sounds to me as though the range in which only the formulae are displaying is set to Text format in which case do one of the following:

-- highlight the range and do an edit replace - replacing = with =
-- highlight the range and do Data -> Text to Columns -- as soon as step 1 appears just click Finish

both should force the range back into General format and coerce the formulae again -- ie you will see results of the formulae not the formulae themselves.

[maacmaac]

I was able to convert text-to-columns the data I was having the problem with.

Now I am having trouble with the way in which the formula is reading the data. I have three tabs in attached spreadsheet.

First tab (Trades): this is the raw data
Second tab (Formulas): formulas to manipulate raw data
Third tab (Results): desired results

The problem is with the formula. Columns D and E in the trades tab have number that are formatted as '000000'. The formulas are dropping leading zeros, if any, for some reason.

Thanks in advance for any help.

P.S. An additional issue I know I am going to have is I am going to have to pull all the formulas down to ~ row 30,000. I already tried this once and it crashed my file and I wasn't able to reopen it. If anyone knows how to accomplish this using a Macro, I would really appreciate it.

[shg]

I can't open your attachment ("Invalid archive"), but how you format cells has no effect on how the contents are used in downstream formulas. If you need leading zeros, store as text.

[maacmaac]

I attached another file with less data. Hopefully you can open that one. Still having problems with the way the results are displayed. If I change the format to text, I lose the leading zeros. Any other thoughts.

P.S. I would like to do this with a macro code so I wouldn't need all the formulas. Should I start a new thread for this or just ask for help with code in this one? Thanks.

[shg]

mm, you can't just change a numeric value formatted as 0....0 to Text format and preserve the leading zeros; they need to formatted as text from the outset.

You could use a helper column: =text(A1, "000000000")

Then copy that column, and paste as values over the original numeric values.

[maacmaac]

Ok, I now have it working. Once again thanks for all the help.