[Solved] Can't get spreadsheet to recalculate

Discuss the spreadsheet application
Post Reply
manofcash
Posts: 4
Joined: Sat Nov 27, 2010 5:20 pm

[Solved] Can't get spreadsheet to recalculate

Post by manofcash »

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 :D
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)
Last edited by manofcash on Mon Nov 29, 2010 2:24 am, edited 1 time in total.
Open Office version 3 on windows 7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Can't get spreadsheet to recalculate

Post by Zizi64 »

Can you upload an example file here?
(see: How to attach a document here)?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
manofcash
Posts: 4
Joined: Sat Nov 27, 2010 5:20 pm

Re: Can't get spreadsheet to recalculate

Post by manofcash »

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!!
ProblemFile.xls
(68.5 KiB) Downloaded 217 times
Open Office version 3 on windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can't get spreadsheet to recalculate

Post by Villeroy »

Works for me. The rates in G1:G2 are not referenced though.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Can't get spreadsheet to recalculate

Post by Zizi64 »

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. 
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can't get spreadsheet to recalculate

Post by Villeroy »

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).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Can't get spreadsheet to recalculate

Post by MrProgrammer »

=(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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
manofcash
Posts: 4
Joined: Sat Nov 27, 2010 5:20 pm

Re: Can't get spreadsheet to recalculate

Post by manofcash »

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 :bravo:
It's not "driving" if its not manual; its only steering! SAVE THE MANUALS!!
Open Office version 3 on windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can't get spreadsheet to recalculate

Post by Villeroy »

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply