| View previous topic :: View next topic |
| Author |
Message |
pirandot
Red Pill
Joined: Jul 28, 2006
Posts: 8
|
 Posted: Thu Apr 02, 2009 11:02 am Post subject: 3.0 - Spreadsheet - Condition doesn't work any longer |
|
| The condition "<>" (<> "") doesn't work any longer in spreadsheet orders like "SUMMEWENN" ("SUMIF" ?). Most probably an OpenOffice error ... |
|
| Back to top |
|
 |
narf
The Anomaly
Joined: Jan 21, 2007
Posts: 1075
|
| Posted: Thu Apr 02, 2009 11:07 am Post subject: |
|
I am not very familiar with functions so can you please give me steps to recreate this issue?
--fran |
|
| Back to top |
|
|
pirandot
Red Pill
Joined: Jul 28, 2006
Posts: 8
|
| Posted: Thu Apr 02, 2009 11:20 am Post subject: |
|
The formula
=SUMMEWENN(T4:T17; "<>"; L4:L17)
should check the cells from T4 to T17. If Tx <> "" (4 ≤ x ≤ 17) then Lx should be added to the entire sum. |
|
| Back to top |
|
|
narf
The Anomaly
Joined: Jan 21, 2007
Posts: 1075
|
| Posted: Thu Apr 02, 2009 11:26 am Post subject: |
|
What result do you get when using that formula in NeoOffice 3.0?
--fran |
|
| Back to top |
|
|
pluby
The Architect
Joined: Jun 16, 2003
Posts: 11949
|
| Posted: Thu Apr 02, 2009 11:37 am Post subject: |
|
| pirandot wrote: | The formula
=SUMMEWENN(T4:T17; "<>"; L4:L17)
should check the cells from T4 to T17. If Tx <> "" (4 ≤ x ≤ 17) then Lx should be added to the entire sum. |
Are you seeing "#NAME" in cells with this formula? If so, the problem is that you have not installed the NeoOffice 3.0 German Language Pack. The German Language Pack contains all of the German names for Calc formulas.
Can you install the NeoOffice 3.0 German Language Pack from the following URL and tell us if that fixes the "SUMMEWENN" function?:
http://www.neooffice.org/neojava/langpackdownload.php
Patrick |
|
| Back to top |
|
|
pirandot
Red Pill
Joined: Jul 28, 2006
Posts: 8
|
| Posted: Thu Apr 02, 2009 1:25 pm Post subject: |
|
NeoOffice and OpenOffice return different results, both not 100% correct, but NeoOffice's bug is worse.
Imagine the following situation (B5 and C6 are empty cells; WENN = IF; ZÄHLENWENN = COUNTIF; SUMMEWENN = SUMIF):
| Code: |
A B C
1 =WENN(B1=""; "yes"; "no") aString 1
2 =WENN(B2=""; "yes"; "no") ="" 2
3 =WENN(B3=""; "yes"; "no") =C6 4
4 =WENN(B4=""; "yes"; "no") "" 8
5 =WENN(B5=""; "yes"; "no") 16
6 =SUMMEWENN($B$1:$B$4; "<>"; $C$1:$C$5) =ZÄHLENWENN($B$1:$B$5; "<>")
|
NeoOffice says:
| Code: |
A B C
1 yes aString 1
2 no 2
3 no 0 4
4 yes "" 8
5 no 16
6 15 4
|
As you can see in A1 and A4, NeoOffice recognizes that only B1 and B4 contain values different from an empty string.
Remember, the 0 in B3 comes from a reference to an empty cell!
The "ZÄHLENWENN" formula in B6 only doesn't count the real empty cell B5.
The "SUMMEWENN" formula in A6 therefore adds 1 + 2 + 4+ 8 = 15 ...
But OpenOffice says:
| Code: |
A B C
1 yes aString 1
2 no 2
3 no 0 4
4 yes "" 8
5 no 16
6 13 3
|
This is more correct since the value (not the formula) in B2 is an empty string.
OpenOffice counts the 3 cells B1, B3, and B4.
So, the sum result is 1 + 4 + 8 = 13.
Unfortunately, even OpenOffice's result isn't consistent, otherwise A3's value had to be "yes" (or B6 should be 2 and A6 should be 1 + 8 = 9). |
|
| Back to top |
|
|
pluby
The Architect
Joined: Jun 16, 2003
Posts: 11949
|
| Posted: Thu Apr 02, 2009 1:36 pm Post subject: |
|
In theory, NeoOffice 3.0 should return the same values as OpenOffice.org 3.0.1. If you are getting different results between these two versions, then I suspect that one of Novell's Go-oo modifications to the OpenOffice.org code is causing this difference.
narf and I will see if we reproduce with the latest code and see if we locate where the difference is coming from.
Patrick |
|
| Back to top |
|
|
narf
The Anomaly
Joined: Jan 21, 2007
Posts: 1075
|
| Posted: Thu Apr 02, 2009 3:00 pm Post subject: |
|
Thank you for providing a sample and different results for us to work with.
| pluby wrote: | | I suspect that one of Novell's Go-oo modifications to the OpenOffice.org code is causing this difference. |
Patrick's theory was correct.: one of Novell's Go-oo modifications is what is causing the different results for the SUMIF() and COUNTIF() functions when the "<>" condition is used.
Although the results you see in NeoOffice may seem incorrect, but they are the same results that these functions produce in Microsoft Excel. Novell's Go-oo engineers apparently made this modification to the OpenOffice.org code to fix the following old OpenOffice.org bug:
Issue 65221
While OpenOffice.org has implied that they will make the "<>" operator work the same as Excel's operator in some future version, Novell's Go-oo engineers went ahead and fixed this bug last year which is why you see this change in NeoOffice 3.0 but no change in OpenOffice.org 3.0.1.
--fran |
|
| Back to top |
|
|
|
