Formula does not calculate - Have tried F9

[Guest]

I have a very simple problem that is frustrating me...

I have an existing column of numbers and they Format/Cells/Number/General
assigned to them.
Over to the side I have a series of IF functions, each one comparing the
value of each cell of numbers with a defined limit.
The IF function only works when I go in to each cell in my column of numbers
and hit return.

I have more than 5000 entries, so I am desperate to find a way of getting
the IF function to recognise the numbers and give a result without having to
hit Enter on every number.

I have checked Tools/Options/Calculation and Automatic is ticked. I have
set it to manual, and then set it back to Automatic just to be sure.

I have also hit F9, Ctl+F9, Ctl+Alt+F9, Ctl+Alt+Shift+F9 - but no joy.

Any ideas?

 

[Dave Peterson]

I'd select a single cell (so that all cells are changed)
then
Edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

Maybe it'll wake xl up!

 

[Guest]

Excel didn't like that (I'm using Excel 2000) - also Excel has stopped
working - not enough memory!
Any other ideas?

 

[Gord Dibben]

Could be the numbers are text.

Re-formatting alone will not change them to real numbers.

Format all to General.

Copy an empty cell.

Select the column of numbers and Paste Special>Add>OK>Esc.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Choose smaller ranges (do them one at a time) and do the edit|replace stuff.

 

[Gord Dibben]

Why the suggestion to edit the formulas when OP stated

only works when I go in to each cell in my column of numbers
and hit return

I interpreted this to mean the numbers were bogus so posted the "change text
nums to real nums fix"


Gord

..

 

[Gord Dibben]

On the other hand..............if the numbers were bogus, what would trigger the
formulas to work by selecting a number and hitting Enter?

So maybe OP is not hitting Enter on the numbers column, but on the formulas
column.

I have confused myself thoroughly so I will have some lunch and go play 18
holes.


Gord

Why the suggestion to edit the formulas when OP stated


I interpreted this to mean the numbers were bogus so posted the "change text
nums to real nums fix"


Gord

.

Gord Dibben MS Excel MVP

 

[Dave Peterson]

I thought that the problem was that the formula returned a value--just not the
correct value (it wasn't displaying just the formula).

And by changing = to =, excel will see that as a change to the formula and
reevaluate those cells.

(And by limiting the range to something smaller, xl might not hang.)

 

[Guest]

Hi Guys,

Have just spent last few hours trying your suggestions and battling with
Excel - but in the end I did it the hard way and went down through each cell
hitting F2 and then Enter, F2 & Enter, F2 & Enter... Arms now about to fall
off!

The issue was with the original list of numbers - on their own they looked
fine. They were supplied by another person so they might have had a
different version of Excel - maybe. It was only when I tried the IF function
that I became aware that something was amis with the original column of
numbers.

I tried copying the column of numbers into a compleletely new spreadsheet in
a new executive of Excel. I used Paste Special/Values so that none of the
formatting code was carried across - but as before it didn't work. When I
then selected these fresh numbers and tried to Format/Numbers/General -
nothing changed.

I also tried the Find & Replace with the = but it didn't work either.

I tried both the above with a smaller extract of the column of numbers to
see if a smaller range would help - but no joy.

And just to asure you - I checked my Automatic Calculation Check Box several
times and it was in the Automatic position.

Very weird.

Anyhoo, I really appreciate your help and suggestions.

Best Regards,
Ani

 

[Gord Dibben]

Did you ever try the "copy an empty cell and paste special method" I gave you?


Gord

Hi Guys,

Have just spent last few hours trying your suggestions and battling with
Excel - but in the end I did it the hard way and went down through each cell
hitting F2 and then Enter, F2 & Enter, F2 & Enter... Arms now about to fall
off!

The issue was with the original list of numbers - on their own they looked
fine. They were supplied by another person so they might have had a
different version of Excel - maybe. It was only when I tried the IF function
that I became aware that something was amis with the original column of
numbers.

I tried copying the column of numbers into a compleletely new spreadsheet in
a new executive of Excel. I used Paste Special/Values so that none of the
formatting code was carried across - but as before it didn't work. When I
then selected these fresh numbers and tried to Format/Numbers/General -
nothing changed.

I also tried the Find & Replace with the = but it didn't work either.

I tried both the above with a smaller extract of the column of numbers to
see if a smaller range would help - but no joy.

And just to asure you - I checked my Automatic Calculation Check Box several
times and it was in the Automatic position.

Very weird.

Anyhoo, I really appreciate your help and suggestions.

Best Regards,
Ani

Gord Dibben MS Excel MVP