Thread: Formula does not calculate - Have tried F9

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?

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!

Anisette wrote:
>
> 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

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

On Wed, 26 Jul 2006 10:55:02 -0700, Anisette
<Anisette@discussions.microsoft.com> wrote:

>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?

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

"Dave Peterson" wrote:

> 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!
>
> Anisette wrote:
> >
> > 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
>

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



Anisette wrote:
>
> Excel didn't like that (I'm using Excel 2000) - also Excel has stopped
> working - not enough memory!
> Any other ideas?
>
> "Dave Peterson" wrote:
>
> > 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!
> >
> > Anisette wrote:
> > >
> > > 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
> >

--

Dave Peterson

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

..
On Wed, 26 Jul 2006 14:42:30 -0500, Dave Peterson <petersod@verizonXSPAM.net>
wrote:

>Choose smaller ranges (do them one at a time) and do the edit|replace stuff.
>
>
>
>Anisette wrote:
>>
>> Excel didn't like that (I'm using Excel 2000) - also Excel has stopped
>> working - not enough memory!
>> Any other ideas?
>>
>> "Dave Peterson" wrote:
>>
>> > 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!
>> >
>> > Anisette wrote:
>> > >
>> > > 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
>> >

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

On Wed, 26 Jul 2006 13:32:31 -0700, Gord Dibben <gorddibbATshawDOTca> wrote:

>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
>
>.
>On Wed, 26 Jul 2006 14:42:30 -0500, Dave Peterson <petersod@verizonXSPAM.net>
>wrote:
>
>>Choose smaller ranges (do them one at a time) and do the edit|replace stuff.
>>
>>
>>
>>Anisette wrote:
>>>
>>> Excel didn't like that (I'm using Excel 2000) - also Excel has stopped
>>> working - not enough memory!
>>> Any other ideas?
>>>
>>> "Dave Peterson" wrote:
>>>
>>> > 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!
>>> >
>>> > Anisette wrote:
>>> > >
>>> > > 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
>>> >

Gord Dibben MS Excel MVP

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

Gord Dibben wrote:
>
> 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
>
> .
> On Wed, 26 Jul 2006 14:42:30 -0500, Dave Peterson <petersod@verizonXSPAM.net>
> wrote:
>
> >Choose smaller ranges (do them one at a time) and do the edit|replace stuff.
> >
> >
> >
> >Anisette wrote:
> >>
> >> Excel didn't like that (I'm using Excel 2000) - also Excel has stopped
> >> working - not enough memory!
> >> Any other ideas?
> >>
> >> "Dave Peterson" wrote:
> >>
> >> > 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!
> >> >
> >> > Anisette wrote:
> >> > >
> >> > > 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
> >> >

--

Dave Peterson

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

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


Gord

On Wed, 26 Jul 2006 15:20:01 -0700, Anisette
<Anisette@discussions.microsoft.com> wrote:

>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