I get #VALUE in the cell - sometimes

I

Intellihome

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
 
I

Intellihome

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
 
I

Intellihome

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
 
C

Cordell

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

Cordell

Ivan,

Yes, application.calculatefull will do it.

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

C
 
I

Ian Ripsher

Cordell said:
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.
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula appearing as Value in Formula Bar 1
Display formulae as values of each cell reference 7
Macroto delete extra space in a cell 3
Getting #VALUE in cells with User Defined Functions 2
writing a value to a particular cell from function 3
Cell values not being properly evaluated by VLOOKUP() orSUMPRODUCT()... why? 1
Display value of a VBA variable inside a cell 2
Sting to VALUE conversion 3

Top