I get #VALUE in the cell - sometimes
 

Hello every one!

I have a problem with how cells are being calculated.

I have formulas, which contain nested If statements. They are all th
same in one column, except they are referencing corresponding rows a
usual.

Well the problem is, I do one change on the worksheet and thos
formulas throw me a #VALUE. I go to the "Trace calculation", and trac
every step of the calculation of the formula, it works perfect up t
the very last step. If the last trace step looks like the followin
example:
=if(FALSE,#N/A,{345.45648})
then I can see that up to this point every nested statement wa
calculated correctly and IF function is ready to return a resul
{345.45648}, but instead it throws #VALUE.

If I click on the cell with this formula and the click in the formul
bar and hit ENTER, then it returns a correct result.
Pressing F9 does not help.
Copying formula down - helps.
Pressing just ENTER on that cell - does not help.
Reloading Excel - helps.
The thing is if I get 1000 cells I do not want to reload Excel o
copydown formula all the time or eveen worse to hit ENTER while curso
is in the formula bar.

Any ideas will be greatly appreciated!
Ivan.

Hey I just figured WHY it happens, BUT still I do not have a clue ho
to fix it.
It happens when I make such a change when formula WOULD return #VALUE
but then when I change it back when a formula SHOULD NOT return #VALUE
it still returns me #VALUE. It is like it never goes back to normal onc
it gets to #VALUE

--
Intellihom
-----------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=147
View this thread: http://www.msusenet.com/t-187056368

I get #VALUE in the cell - sometimes
 

Did you try Ctrl + Alt + F9?
--
C


"Intellihome" wrote:


Hello every one!

I have a problem with how cells are being calculated.

I have formulas, which contain nested If statements. They are all the
same in one column, except they are referencing corresponding rows as
usual.

Well the problem is, I do one change on the worksheet and those
formulas throw me a #VALUE. I go to the "Trace calculation", and trace
every step of the calculation of the formula, it works perfect up to
the very last step. If the last trace step looks like the following
example:
=if(FALSE,#N/A,{345.45648})
then I can see that up to this point every nested statement was
calculated correctly and IF function is ready to return a result
{345.45648}, but instead it throws #VALUE.

If I click on the cell with this formula and the click in the formula
bar and hit ENTER, then it returns a correct result.
Pressing F9 does not help.
Copying formula down - helps.
Pressing just ENTER on that cell - does not help.
Reloading Excel - helps.
The thing is if I get 1000 cells I do not want to reload Excel or
copydown formula all the time or eveen worse to hit ENTER while cursor
is in the formula bar.

Any ideas will be greatly appreciated!
Ivan.

Hey I just figured WHY it happens, BUT still I do not have a clue how
to fix it.
It happens when I make such a change when formula WOULD return #VALUE,
but then when I change it back when a formula SHOULD NOT return #VALUE,
it still returns me #VALUE. It is like it never goes back to normal once
it gets to #VALUE.


--
Intellihome
------------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
View this thread: http://www.msusenet.com/t-1870563681

I get #VALUE in the cell - sometimes
 

Thanks Cordell,

It helps! Now could you also tell me how to do the same from VB. Woul
it be Application.CalculateFull ?

Thank you,
Ivan.

Oh, and do you know why this has happened? and may be how to avoid i
at all

--
Intellihom
-----------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=147
View this thread: http://www.msusenet.com/t-187056368

I get #VALUE in the cell - sometimes
 

"Cordell" wrote in message
...
Did you try Ctrl + Alt + F9?

What does that do? Is it like a "super-calculation"?

I get #VALUE in the cell - sometimes
 

Hey I found this message dated October 1, 1997 !!!!!

Microsoft has discovered a bug in its Excel spreadsheet program tha
prevents some worksheet cells from automatically recalculating
According to the company, Microsoft has already fixed the bug, an
plans to post a patch to its Web site within two weeks.

Until the patch is posted, Microsoft reminds Excel users that you ca
force all worksheet cells to recalculate with Ctrl-Alt-F9.


apparently over 390 weeks has passed and still no fix :) I am usin
2003 Excel

--
Intellihom
-----------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=147
View this thread: http://www.msusenet.com/t-187056368

I get #VALUE in the cell - sometimes
 

Ian,

Here are the various calculation methods and Microsoft's description.

Press F9
Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in all open workbooks. If a workbook is set for
automatic calculation, you do not need to press F9 for calculation.

Press SHIFT+F9
Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in the active worksheet.

Press CTRL+ALT+F9
Calculates all formulas in all open workbooks, regardless of whether they
have changed since last time or not.

Press CTRL+SHIFT+ALT+F9
Rechecks dependent formulas, and then calculates all formulas in all open
workbooks, regardless of whether they have changed since last time or not.


--
C


"Ian Ripsher" wrote:

"Cordell" wrote in message
...
Did you try Ctrl + Alt + F9?

What does that do? Is it like a "super-calculation"?

I get #VALUE in the cell - sometimes
 

Ivan,

Yes, application.calculatefull will do it.

It looks from your other post that you found the answer to your other
questions.

C


"Intellihome" wrote:


Thanks Cordell,

It helps! Now could you also tell me how to do the same from VB. Would
it be Application.CalculateFull ?

Thank you,
Ivan.

Oh, and do you know why this has happened? and may be how to avoid it
at all.


--
Intellihome
------------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
View this thread: http://www.msusenet.com/t-1870563681

I get #VALUE in the cell - sometimes
 

"Cordell" wrote in message
...
Ian,

Here are the various calculation methods and Microsoft's description.

Press F9
Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in all open workbooks. If a workbook is set
for
automatic calculation, you do not need to press F9 for calculation.

Press SHIFT+F9
Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in the active worksheet.

Press CTRL+ALT+F9
Calculates all formulas in all open workbooks, regardless of whether they
have changed since last time or not.

Press CTRL+SHIFT+ALT+F9
Rechecks dependent formulas, and then calculates all formulas in all open
workbooks, regardless of whether they have changed since last time or not.

Many thanks for this - I didn't realise there was anything beyond F9!!