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

Cell Data ignored, DSUM, database

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
neilg12
General User
General User


Joined: 18 Jun 2012
Posts: 5

PostPosted: Tue Jun 19, 2012 8:24 pm    Post subject: Cell Data ignored, DSUM, database Reply with quote

I created a Data Base and Search Criteria modeled after the example in the help section.
Works Great. I inserted a row into the data base. The Formulas updated to the new size.
I added a few values in the new line. Now DSUM reads zero, always, even for different columns. Numbers have been formatted as numbers and text as text.

DSUM(A1:E7;D1;A11:E12)

I would be happy to send the file if I knew how. Rolling Eyes
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Wed Jun 20, 2012 5:43 am    Post subject: Re: Cell Data ignored, DSUM, database Reply with quote

neilg12 wrote:
I created a Data Base and Search Criteria modeled after the example in the help section.
Works Great. I inserted a row into the data base. The Formulas updated to the new size.
I added a few values in the new line. Now DSUM reads zero, always, even for different columns. Numbers have been formatted as numbers and text as text.

DSUM(A1:E7;D1;A11:E12)

I would be happy to send the file if I knew how. Rolling Eyes


This forum does not accept file uploads. You can upload a file to someplace such as MediaFire for other forum readers to examine.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
neilg12
General User
General User


Joined: 18 Jun 2012
Posts: 5

PostPosted: Wed Jun 20, 2012 7:26 am    Post subject: First Reply Reply with quote

I was informed that I cannot upload files to this forum.

My data sheet is very similar to the example given for DSUM in OOo-CALC Help

If you really need my file, please suggest a way to get it to you.
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Thu Jun 21, 2012 4:26 am    Post subject: Re: First Reply Reply with quote

neilg12 wrote:
I was informed that I cannot upload files to this forum.

My data sheet is very similar to the example given for DSUM in OOo-CALC Help

If you really need my file, please suggest a way to get it to you.


Please re-read my previous message. It suggests a way.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
neilg12
General User
General User


Joined: 18 Jun 2012
Posts: 5

PostPosted: Fri Jun 22, 2012 1:35 am    Post subject: The file in question Reply with quote

The file in question can be found at http://www.mediafire.com/file/d9umwl4t11xrfvz.

I know the solution to my problem is so simple that I will be totally embarrassed. I have spent literally hours on this with no solution. I have worked with many programs before but this is my first experience with Open office.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Jun 22, 2012 3:10 am    Post subject: Reply with quote

If you had actually created a database rather than a spreadsheet, the solution would be a very simple query.

Quote:
I have worked with many programs before but this is my first experience with Open office.

So we do not need to explain the most basic facts such as the difference between number and text (value 1 and character "1").

Since you try to mimic a database in a spreadsheet, the solution is exactly the same as in any other spreadsheet program.

DSUM can be used with the so called "advanced filter" which reads criteria from the same type of criteria range.

Any filtering method can be used with the SUBTOTAL function which ignores filtered cells.
Then there is the SUBTOTAL function which can be used to mimic a multiple SUMIF or COUNTIF.
SUM and COUNT in array context can do the same. Just read about spreadsheets and array functions.

Most simple, most database-ish solution:
http://wiki.services.openoffice.org/wiki/Documentation/OOo3_User_Guides/Calc_Guide/DataPilot
This method does not work with many spreadsheet programs. Only Excel and Calc can generate such tables.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
neilg12
General User
General User


Joined: 18 Jun 2012
Posts: 5

PostPosted: Fri Jun 22, 2012 3:42 am    Post subject: Thank you Reply with quote

If I understand correctly, stop using data base functions and use "if" functions. They seem to do the same thing. I was only trying to do what the help files were explaining. You suggestion seems be a better way way. Thank you.

I only need to do simple calculations like in the example. Very Happy
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Jun 22, 2012 4:22 am    Post subject: Reply with quote

myself wrote:
So we do not need to explain the most basic facts such as the difference between number and text (value 1 and character "1").

I was wrong. Your sheet does not contain a single number. It's all text. This is not a matter of formatting. It is a matter of cell values.
[Tutorial] Ten concepts that every Calc user should know
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Fri Jun 22, 2012 5:38 am    Post subject: Reply with quote

Villeroy wrote:
myself wrote:
So we do not need to explain the most basic facts such as the difference between number and text (value 1 and character "1").

I was wrong. Your sheet does not contain a single number. It's all text. This is not a matter of formatting. It is a matter of cell values.
[Tutorial] Ten concepts that every Calc user should know


Are you sure that they are all text? The 'numeric' ones appear to to text to me, just left adjusted for some reason.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Jun 22, 2012 7:12 am    Post subject: Reply with quote

SORRY, SORRY, SORRY.
Sooo sorry. With my current system setup Ctrl+F8 triggers something outside of the office window. I thought Ctrl+F8 turned on value highlighting when in fact it did not.
WIth your document shown in an office window, menu:View>Highlight Values shows all the numbers in blue and your DSUM formula cell in green.
It's the same zero result in
AOO 3.4
LibreOffice 3.5
LibreOffice 3.6(beta)
OOo 1.1.5(2005)
and surprisingly it is the same in the Gnumeric spreadsheet application.

I have no idea what's wrong.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Jun 22, 2012 9:39 am    Post subject: Reply with quote

This is how to fix this file: Fill all criteria cells with values and remove them again. Now everything behaves as it should.

Filed a bug report with a link to your file on mediafire.com
https://issues.apache.org/ooo/show_bug.cgi?id=120063
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org


Last edited by Villeroy on Sat Jun 23, 2012 1:37 am; edited 1 time in total
Back to top
View user's profile Send private message
neilg12
General User
General User


Joined: 18 Jun 2012
Posts: 5

PostPosted: Fri Jun 22, 2012 7:42 pm    Post subject: Wow Reply with quote

II was afraid to ask for help because it appeared to just be a newbie mistake. Thanks for your insight and help. It now works. I do know how long it would have taken me to figure it out. Solved Very Happy
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Jun 23, 2012 7:43 am    Post subject: Reply with quote

Well, the problem has been solved indeed. My bug report has been rejected as invalid because your criteria cells contain spaces.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
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
Page 1 of 1

 
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