OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Solved] OO 3.2 Calc DSUM always returns 0.0
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
jabog6
General User
General User


Joined: 11 May 2010
Posts: 8

PostPosted: Tue May 11, 2010 4:49 pm    Post subject: [Solved] OO 3.2 Calc DSUM always returns 0.0 Reply with quote

Hi all,

I've used MS Excel for some time, and have been converting some of my spreadsheets to OpenOffice. I have used the DSUM function extensively before. It works in Excel. When I converted my spreadsheet to OO format, it initially worked fine. DSUM did what it was supposed to do. Now, it doesn't. I noticed this soon after upgrading from Ubuntu 9.04 to 10.04 this past weekend, but I'm not sure if this was the cause...

Ok, I can replicate the problem by following the example in http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_DSUM_function

I set up the sheet as described, and create a cell as follows:
Code:
=DSUM(A1:E10, 4, A13:E14)


The result is 0, whereas it should be 1950.

I'm confused by this, and a little frustrated. I'd appreciate any help you can provide.

Cheers.
_________________
Trying hard to adapt to linux...


Last edited by jabog6 on Sat May 15, 2010 1:02 pm; edited 1 time in total
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Tue May 11, 2010 5:27 pm    Post subject: Reply with quote

Works for me. OOo3.2 under Windows.

Are you sure you have 2 in cell B14 because you will get 0 if you don't?
Back to top
View user's profile Send private message
jabog6
General User
General User


Joined: 11 May 2010
Posts: 8

PostPosted: Tue May 11, 2010 5:52 pm    Post subject: Reply with quote

JohnV wrote:
Works for me. OOo3.2 under Windows.

Are you sure you have 2 in cell B14 because you will get 0 if you don't?


Hi John,
Thanks for your reply.

Yes, I'm sure about that cell. I copied and pasted from the OO wiki link. I have a few spreadsheets that work fine in Excel (Win XP) but when I open them with OO 3.2, all cells that have DSUM functions return values of 0.

My only guess is that there is some environment setting that I can change, or just an outright bug...

Any other ideas?
_________________
Trying hard to adapt to linux...
Back to top
View user's profile Send private message
jabog6
General User
General User


Joined: 11 May 2010
Posts: 8

PostPosted: Wed May 12, 2010 2:29 pm    Post subject: Reply with quote

Would it help if I showed a screenshot of the problem? I can make available a sample spreadsheet that does not work if someone is willing to have a look at it...
_________________
Trying hard to adapt to linux...
Back to top
View user's profile Send private message
jabog6
General User
General User


Joined: 11 May 2010
Posts: 8

PostPosted: Fri May 14, 2010 3:03 pm    Post subject: Reply with quote

A screenshot of this file is here: http://i967.photobucket.com/albums/ae155/jabog6/Screenshot.jpg

Again, this example was taken from http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_DSUM_function

I'd appreciate any help you can offer.
_________________
Trying hard to adapt to linux...
Back to top
View user's profile Send private message
thomasjk
Super User
Super User


Joined: 16 Dec 2005
Posts: 2374

PostPosted: Fri May 14, 2010 7:20 pm    Post subject: Reply with quote

Replace the commas with semicolons.
Back to top
View user's profile Send private message
jabog6
General User
General User


Joined: 11 May 2010
Posts: 8

PostPosted: Sat May 15, 2010 8:14 am    Post subject: Reply with quote

thomasjk wrote:
Replace the commas with semicolons.


That didn't do it.
At first, when I replaced the commas with semicolons, it would automatically revert to commas. I found in Tools/Options (under Calc, Formula) you can change the function separator to whatever you want. When I change this setting, the function in the spreadsheet also changes.

The result is still 0...
_________________
Trying hard to adapt to linux...
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sat May 15, 2010 9:02 am    Post subject: Reply with quote

jabog6:

Just so you understand . . . I am using OpenOffice 3.2 and Windows XP version.

The DSUM function does work for me ( inframous last words ). Smile

Now, just so you understand, the last parameter of the formula:
Code:
=DSUM(A1:E10;"Distance to School";A13:E14)

means . . . EACH and EVERY 'condition' defined in the range A13:E14 must be 'matched' in order to SUM the items in "Distance to School".

If you change the LAST parameter, from A13:E14 to B13:B14 . . . do you get a value of zero, OR, 1950 ? ? ?

What is possible, you have a SPACE ( or multiple spaces ) along line 14, rather than a 'blank / empty cell'. If so . . . the 'condition' would NOT be met, as written, since SPACE will NOT match the corresponding row(s), and, a SPACE does not NOT show in the graphic.

Alternatively, you can just go over all the cell in row 14, and, 'delete' the contents, and, re-enter 2 in cell B14 . . . to be sure that ONLY cell B14 contains a value, and, other cells in the row are all 'blank'.

I hope this helps, please bue sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
rja
General User
General User


Joined: 15 May 2010
Posts: 5

PostPosted: Sat May 15, 2010 9:48 am    Post subject: Reply with quote

Got the same problem and using Sumif() doesn't work either. Problem has occured since upgrading to 3.2 (with Ubuntu upgrade). Formulas were working in 3.1 and this has caused figures to change in an important financial spreadsheet!
Back to top
View user's profile Send private message
jabog6
General User
General User


Joined: 11 May 2010
Posts: 8

PostPosted: Sat May 15, 2010 10:00 am    Post subject: Reply with quote

Sliderule, thanks so much!

This was exactly the problem - a space was in the 'empty' cells. Once I deleted it, the answer was 1950. I appreciate the help.

If I may: a follow-up question. Why can't I put '=1+1' in cell B14 and get the same result? It only works with '2' in there.
_________________
Trying hard to adapt to linux...
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sat May 15, 2010 10:14 am    Post subject: Reply with quote

On my machine, with OpenOffice 3.2 and Windows . . . if I put:

Code:
=1+1


in cell B14, I do get the answer I expect . . . that is 1950.

Make sure in cell B14, what you enter is: =1+1 and it does NOT start with a single quote.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
jabog6
General User
General User


Joined: 11 May 2010
Posts: 8

PostPosted: Sat May 15, 2010 1:02 pm    Post subject: Reply with quote

sliderule wrote:
On my machine, with OpenOffice 3.2 and Windows . . . if I put:

Code:
=1+1


in cell B14, I do get the answer I expect . . . that is 1950.

Make sure in cell B14, what you enter is: =1+1 and it does NOT start with a single quote.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.


I do the same (running Ubuntu 10.04 and OpenOffice 3.2) the formula does not work. I'm clearly entering a formula and not a string (i.e. does not start with a quote). This is in fact why I started investigating this problem, as I have a few spreadsheets that use DSUM, where the criterion is made with formulas.

I'll keep poking at that, but my initial problem is solved. Thanks again.
_________________
Trying hard to adapt to linux...
Back to top
View user's profile Send private message
rja
General User
General User


Joined: 15 May 2010
Posts: 5

PostPosted: Tue May 18, 2010 8:19 am    Post subject: DSUM() in 3.2 Reply with quote

My problem is that I am using 'greater than today' in the search criteria by entering

=CONCATENATE(">", TODAY())

in the search criteria cells. This worked fine in OO 3.1 (and still does in my 3.1 version running in Windows, but not in the Ubuntu (Oracle version) upgrade OO 3.2. It also works on my wife's Windows laptop using (non Oracle) OO 3.2). Is this a planned change, or a bug? Who should I inform?
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue May 18, 2010 8:28 am    Post subject: Reply with quote

rja:

If I understand you correctly, what you are using is not an 'official' version of OpenOffice. Put another way, you did NOT download it from the office OpenOffice site at:

http://download.openoffice.org/other.html#tested-full

Therefore, you should report it to the site you downloaded it from.

Sliderule
Back to top
View user's profile Send private message
rja
General User
General User


Joined: 15 May 2010
Posts: 5

PostPosted: Tue May 18, 2010 8:36 am    Post subject: Reply with quote

The OO 3.2 upgrade came with the Ubuntu 10.04 upgrade. Is this not an official upgrade. I am slightly confused (bemused) by the "Oracle" sub-title that occurs on the start up 'splash title' for OO 3.2 in Ubuntu linux, but not in Windows OO 3.2. Are you saying that I should report this to Ubuntu? (Sorry for being thick!)
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group