Apache OpenOffice Community Forum

I opened an office 2003 spreadsheet in open office and it won't recalculate when I make changes. Auto calculate is checked in the tools>cell contents. Saved it as an open office spreadsheet and reopened it but it still won't recalculate. Anyone with any ideas?

It does recalculate with ctrl-shift-f9 but this is a PITA and not the way it is supposed to work.

Thanks in advance
Manofcash
It's not "driving" if its not manual; its only steering! SAVE THE MANUALS!!

Flame icon removed. That icon means 'Tagged to a known issue' (TheGurkha, Moderator)

Can you upload an example file here?
(see: How to attach a document here)?

Here is the file you asked for that won't recalculate.

Manofcash
It's not "driving" if its not manual; its only steering! SAVE THE MANUALS!!

Works for me. The rates in G1:G2 are not referenced though.

The results RANDBETWEEN functions always refreshed (the cells get new values) when I hit "F9" in MSOfficeXP/Excel;
but not refresh in OxygenOffice3.2.1 Calc. Only refresh that "RANDBETWEEN" cell, what I modified manually.
The "AutoCalculate" option is checked.

Edit: The Shift-Ctrl-F9 works fine! And at every OPEN refresh the cells in the xls and the ods file.

Contrary to RAND(), RANDBETWEEN(a;b) is not a volatile function. It recalculates only when input values change. Since this file's input values to RANDBETWEEN are constants the do not recalculate unless you reload the file or do a "hard recalculation" (Ctrl+Shift+F9).

=(RANDBETWEEN(-2.75*10^1;9.5*10^1)/(10^1))*0.001 is a somewhat unusual formula on several grounds, though possibly you have chosen to write it that way for reasons I don't understand:

• Expressions -2.75*10^1, 9.5*10^1, and 10^1 are used instead of the constants -2.75E1, 9.5E1, and 10E1
• RANDBETWEEN expects (see Help) integer arguments, but the first is -27.5 and will be rounded down, perhaps unexpectedly, to -28
• The result of RANDBETWEEN is divided by 10^1 and multiplied by .001 instead of just multiplying by .0001

I would have used =RANDBETWEEN(-28;95)*0.0001. You don't need to explain why you did it your way, as long as you meant to use the more complicated calculation.

If you want to use RAND() instead of RANDBETWEEN() you can use =INT(124*RAND()-28)*0.0001 (124 is 95+28+1). This will recalculate when F9 is pressed.

Hey guys, thanks for all your hep with this problem; it is much appreciated. I will have to look at the randbetween formula and see about modifying it to get this to do an automatic recalc since this was originally set up in Excel which appears to have slightly different program parameters for recalc requirements.

Manofcash
It's not "driving" if its not manual; its only steering! SAVE THE MANUALS!!

manofcash wrote:Hey guys, thanks for all your hep with this problem; it is much appreciated. I will have to look at the randbetween formula and see about modifying it to get this to do an automatic recalc since this was originally set up in Excel which appears to have slightly different program parameters for recalc requirements.

No, I don't think that Excel is different in this respect:
RANDBETWEEN(int1;int2): http://office.microsoft.com/en-us/excel ... 09230.aspx
List of volatile functions: http://www.decisionmodels.com/calcsecretsi.htm