| View previous topic :: View next topic |
| Author |
Message |
neilg12
General User
Joined: 18 Jun 2012
Posts: 5
|
 Posted: Tue Jun 19, 2012 8:24 pm Post subject: Cell Data ignored, DSUM, database |
 |
|
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.  |
|
| Back to top |
|
 |
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
Location: Kingston ON Canada
|
| Posted: Wed Jun 20, 2012 5:43 am Post subject: Re: Cell Data ignored, DSUM, database |
|
|
| 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. |
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 |
|
|
neilg12
General User
Joined: 18 Jun 2012
Posts: 5
|
| Posted: Wed Jun 20, 2012 7:26 am Post subject: First Reply |
|
|
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 |
|
|
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
Location: Kingston ON Canada
|
| Posted: Thu Jun 21, 2012 4:26 am Post subject: Re: First Reply |
|
|
| 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 |
|
|
neilg12
General User
Joined: 18 Jun 2012
Posts: 5
|
| Posted: Fri Jun 22, 2012 1:35 am Post subject: The file in question |
|
|
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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Jun 22, 2012 3:10 am Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
neilg12
General User
Joined: 18 Jun 2012
Posts: 5
|
| Posted: Fri Jun 22, 2012 3:42 am Post subject: Thank you |
|
|
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.  |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Jun 22, 2012 4:22 am Post subject: |
|
|
| 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 http://forum.openoffice.org |
|
| Back to top |
|
|
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
Location: Kingston ON Canada
|
| Posted: Fri Jun 22, 2012 5:38 am Post subject: |
|
|
| 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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Jun 22, 2012 7:12 am Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Jun 22, 2012 9:39 am Post subject: |
|
|
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 http://forum.openoffice.org
Last edited by Villeroy on Sat Jun 23, 2012 1:37 am; edited 1 time in total |
|
| Back to top |
|
|
neilg12
General User
Joined: 18 Jun 2012
Posts: 5
|
| Posted: Fri Jun 22, 2012 7:42 pm Post subject: Wow |
|
|
| 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 |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sat Jun 23, 2012 7:43 am Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
|
