[Solved] Can't get spreadsheet to recalculate

Discuss the spreadsheet application

[Solved] Can't get spreadsheet to recalculate

Postby manofcash » Sat Nov 27, 2010 5:37 pm

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
manofcash
 
Posts: 4
Joined: Sat Nov 27, 2010 5:20 pm

Re: Can't get spreadsheet to recalculate

Postby Zizi64 » Sat Nov 27, 2010 5:59 pm

Can you upload an example file here?
(see: How to attach a document here)?
Tibor Kovacs, Hungary; AOO4.0.1, LO3.6.4, LO4.2.2 on WinXPprof.SP3
Zizi64
Volunteer
 
Posts: 1784
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Can't get spreadsheet to recalculate

Postby manofcash » Sat Nov 27, 2010 6:38 pm

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 57 times
Open Office version 3 on windows 7
manofcash
 
Posts: 4
Joined: Sat Nov 27, 2010 5:20 pm

Re: Can't get spreadsheet to recalculate

Postby Villeroy » Sat Nov 27, 2010 6:52 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17320
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can't get spreadsheet to recalculate

Postby Zizi64 » Sat Nov 27, 2010 9:26 pm

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; AOO4.0.1, LO3.6.4, LO4.2.2 on WinXPprof.SP3
Zizi64
Volunteer
 
Posts: 1784
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Can't get spreadsheet to recalculate

Postby Villeroy » Sat Nov 27, 2010 9:44 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17320
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can't get spreadsheet to recalculate

Postby MrProgrammer » Sun Nov 28, 2010 4:53 am

=(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
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.6.8.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 1750
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Can't get spreadsheet to recalculate

Postby manofcash » Sun Nov 28, 2010 3:50 pm

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
manofcash
 
Posts: 4
Joined: Sat Nov 27, 2010 5:20 pm

Re: Can't get spreadsheet to recalculate

Postby Villeroy » Sun Nov 28, 2010 3:55 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17320
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 25 guests