Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
plino wrote: I thought something was wrong because when I replaced all the text cells with values I got a 0 (zero). I incorrectly assumed it would show All Good but that was not possible since the final function was MIN :) Yeah, sorry about that. After I posted the formula, I realised that the All Good bit wouldn't work out so well. But seeing as it returns a zero value when there are no non-numeric values, I didn't mention it. At least the formula is still useful, even though it's not as user friendly. Regards Jack -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
Michael D. Setzer II wrote: Was interested if it could be modified for multi-column searches. Did a same test using H2:I5 and used this formula. = SMALL((IF(ISNUMBER($H$2:$I$5), ALL GOOD,COLUMN($H$2:$I$5)*1000+ROW($H$2:$I$5))), 1) That returns the number 8004 and 2nd one returns 9005. Then used this formula to convert to a cell address. =CHAR(INT(L2/1000)+64)MOD(L2,1000) That only works for first 26 columns, and upto 999 rows, but could be modified. Hi Michael, I've got another way of finding the address of the first non-numeric value in a multi-column search. = ADDRESS(MIN(IF(ISNUMBER($G$4:$H$7), All good, ROW($G$4:$H$7))), MIN(IF(ISNUMBER(OFFSET($G$4:$H$7, MIN(IF(ISNUMBER($G$4:$H$7), All good, ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1)), All good, COLUMN(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), All good, ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1) Please note that it should be entered as an array formula by pressing CTRL+SHIFT+ENTER instead of just plain ENTER (curly braces will appear around the formula if it is done correctly). The advantage of this formula is that it should work for any contiguous range, not limited to 999 rows or 26 columns. There are also disadvantages to this formula. The first obvious disadvantage is debugging/understanding the formula (if anyone wants an explanation of how it works, feel free to ask). The second disadvantage is that I can't think of a logical way to extend it to find the 2nd or 3rd non-numeric value. Your (Michael's) formula can easily be modified to search for the 2nd, 3rd, 4th etc. non-numeric cell. The formula also references the range in question quite a few times, which makes it a pain to change the reference to another range (in this case I would suggest a find replace for $G$4:$H$7) The above formula returns Err:502 if all the values are non-numeric. To get a more user friendly result, use the following formula: = IF(MIN(ISNUMBER(G4:H7)), All values are numeric, ADDRESS(MIN(IF(ISNUMBER($G$4:$H$7), All good, ROW($G$4:$H$7))), MIN(IF(ISNUMBER(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), All good, ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1)), All good, COLUMN(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), All good, ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1)) Once again, remember to enter it as an array formula (by pressing CTRL+SHIFT+ENTER instead of just ENTER). Regards Stephan -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
plino wrote: I hope this will also contribute for the array bug to be fixed since I reported it on the bug tracker... Just for reference (if anyone is curious), the bug is: https://bugs.freedesktop.org/show_bug.cgi?id=38014 -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
Hi Stephan I will test this one later. With don't you simply name the range Jack? It's much easier to use and modify ;) I think that there is no need to know the second, third, etc text celss. You only need to know the first error to go there and fix it. One error at a time :) Thanks! -- View this message in context: http://nabble.documentfoundation.org/Calc-Has-anyone-tested-the-backward-compatibility-of-LibreOffice-3-4-tp3022764p3038516.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
On 8 Jun 2011 at 10:37, Stephan Zietsman wrote:
Date sent: Wed, 8 Jun 2011 10:37:07 +0200
Subject:Re: [libreoffice-users] Re: Calc: Has anyone
tested the
backward-compatibility of LibreOffice 3.4?
From: Stephan Zietsman szi...@gmail.com
To: users@libreoffice.org
Send reply to: users@libreoffice.org
Stephan wrote:
To get a more user friendly result, use the following formula:
= IF(MIN(ISNUMBER(G4:H7)), All values are numeric,
ADDRESS(MIN(IF(ISNUMBER($G$4:$H$7), All good, ROW($G$4:$H$7))),
MIN(IF(ISNUMBER(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), All
good, ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1)), All good,
COLUMN(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), All good,
ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1))
I just have a small correction to make. For consistency, the first
range reference in the formula should also be absolute (i.e. G4:H7
should be $G$4:$H$7). So the formula should actually be:
= IF(MIN(ISNUMBER($G$4:$H$7)), All values are numeric,
ADDRESS(MIN(IF(ISNUMBER($G$4:$H$7), All good, ROW($G$4:$H$7))),
MIN(IF(ISNUMBER(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), All
good, ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1)), All good,
COLUMN(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), All good,
ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1))
Once again, remember to enter it as an array formula (by pressing
CTRL+SHIFT+ENTER instead of just ENTER).
The Address function was something I didn't recall, use to teach a
spreadsheet class, but haven't in a long time. Did come up with
some other testing.
Filled in A1:AF20 with a bunch of numbers, and then I randomly
put in some a characters in cells.
In Cell AG1 placed this to get the number of non-numeric
=COUNTA(A1:AF20)-COUNT(A1:AF20)
In Cells AH1 thru AH15 put the numbers 1 to 15
In Cell AI1 thru AI15 put this formula
{= SMALL((IF(ISNUMBER($A$1:$AF$20), ALL
GOOD,COLUMN($A$1:$AF$20)*1000+ROW($A$1:$AF$20))),
AH1)}
Then in AJ1 thru AJ15 put this formula
=ADDRESS(MOD(AI1,1000),INT(AI1/1000))
This is the result.
13 non-numeric fields and their addresses.
13
1
1007
$A$7
2
4011
$D$11
3
8010
$H$10
4
11012
$K$12
5
13007
$M$7
6
17011
$Q$11
7
19015
$S$15
8
24017
$X$17
9
26009
$Z$9
10
29009
$AC$9
11
31015
$AE$15
12
32011
$AF$11
13
32015
$AF$15
14
#VALUE!
#VALUE!
15
#VALUE!
#VALUE!
Could add an if to the AJ formula to only display if less than or
equal to $AG$1.
Regards
Stephan
--
Unsubscribe instructions: E-mail to users+h...@libreoffice.org
In case of problems unsubscribing, write to postmas...@documentfoundation.org
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
Changed formula in AJ1 thru AJ15
=IF(AH1=$AG$1,ADDRESS(MOD(AI1,1000),INT(AI1/1000)),)
Paste of cells directly from spreadsheet didn't look correctly
131 1007$A$7
2 4011$D$11
3 8010$H$10
4 11012 $K$12
5 13007 $M$7
6 17011 $Q$11
7 19015 $S$15
8 24017 $X$17
9 26009 $Z$9
10 29009 $AC$9
11 31015 $AE$15
12 32011 $AF$11
13 32015 $AF$15
14 #VALUE!
15 #VALUE!
On 8 Jun 2011 at 20:19, Michael D. Setzer II wrote:
From: Michael D. Setzer II
mi...@kuentos.guam.net
To: Stephan Zietsman szi...@gmail.com,
users@libreoffice.org
Date sent: Wed, 08 Jun 2011 20:19:23 +1000
Subject:Re: [libreoffice-users] Re: Calc: Has anyone
tested the
backward-compatibility of LibreOffice 3.4?
Priority: normal
Send reply to: users@libreoffice.org
On 8 Jun 2011 at 10:37, Stephan Zietsman wrote:
Date sent:Wed, 8 Jun 2011 10:37:07 +0200
Subject: Re: [libreoffice-users] Re: Calc: Has anyone
tested the
backward-compatibility of LibreOffice 3.4?
From: Stephan Zietsman szi...@gmail.com
To: users@libreoffice.org
Send reply to:users@libreoffice.org
Stephan wrote:
To get a more user friendly result, use the following formula:
= IF(MIN(ISNUMBER(G4:H7)), All values are numeric,
ADDRESS(MIN(IF(ISNUMBER($G$4:$H$7), All good, ROW($G$4:$H$7))),
MIN(IF(ISNUMBER(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), All
good, ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1)), All good,
COLUMN(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), All good,
ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1))
I just have a small correction to make. For consistency, the first
range reference in the formula should also be absolute (i.e. G4:H7
should be $G$4:$H$7). So the formula should actually be:
= IF(MIN(ISNUMBER($G$4:$H$7)), All values are numeric,
ADDRESS(MIN(IF(ISNUMBER($G$4:$H$7), All good, ROW($G$4:$H$7))),
MIN(IF(ISNUMBER(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), All
good, ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1)), All good,
COLUMN(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), All good,
ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1))
Once again, remember to enter it as an array formula (by pressing
CTRL+SHIFT+ENTER instead of just ENTER).
The Address function was something I didn't recall, use to teach a
spreadsheet class, but haven't in a long time. Did come up with
some other testing.
Filled in A1:AF20 with a bunch of numbers, and then I randomly
put in some a characters in cells.
In Cell AG1 placed this to get the number of non-numeric
=COUNTA(A1:AF20)-COUNT(A1:AF20)
In Cells AH1 thru AH15 put the numbers 1 to 15
In Cell AI1 thru AI15 put this formula
{= SMALL((IF(ISNUMBER($A$1:$AF$20), ALL
GOOD,COLUMN($A$1:$AF$20)*1000+ROW($A$1:$AF$20))),
AH1)}
Then in AJ1 thru AJ15 put this formula
=ADDRESS(MOD(AI1,1000),INT(AI1/1000))
This is the result.
13 non-numeric fields and their addresses.
13
1
1007
$A$7
2
4011
$D$11
3
8010
$H$10
4
11012
$K$12
5
13007
$M$7
6
17011
$Q$11
7
19015
$S$15
8
24017
$X$17
9
26009
$Z$9
10
29009
$AC$9
[libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
plino wrote: I couldn't make it work. In fact I just found out that array functions don't work in Lib (3.3 or 3.4) nor in OOo (3.4 beta) Hey plino, array formulas in LibO/OOo are a little tricky (little more so than in Excel). If you've entered the formula correctly and pressed ENTER (instead of CTRL+SHIFT+ENTER), then obviously it does not work. If you then select the cell/formula again and press CTRL+SHIFT+ENTER, then it *still* won't work, because it doesn't register that you've changed the formula (thus won't take the CTRL+SHIFT+ENTER). You'll need to physically change the formula again and then press CTRL+SHIFT+ENTER (I usually type and erase a space). plino wrote: I don't know if it ever worked (I use it every now and then, so I couldn't say if I used it before in OOo/LO) but if it did, someone broke it :) I've tested the array formula I sent you on LibO3.3.2, I can confirm that it works there. I can't comment on LibO 3.4 as I don't have it yet. Regards Jack -- View this message in context: http://nabble.documentfoundation.org/Calc-Has-anyone-tested-the-backward-compatibility-of-LibreOffice-3-4-tp3022764p3033637.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
Jack wrote: array formulas in LibO/OOo are a little tricky (little more so than in Excel). If you've entered the formula correctly and pressed ENTER (instead of CTRL+SHIFT+ENTER), then obviously it does not work. If you then select the cell/formula again and press CTRL+SHIFT+ENTER, then it *still* won't work, because it doesn't register that you've changed the formula (thus won't take the CTRL+SHIFT+ENTER). You'll need to physically change the formula again and then press CTRL+SHIFT+ENTER (I usually type and erase a space). I pasted your formula so there were no typing errors. Of course that triggered the bug you have already found a workaround. Well done ;) But that is definitely a BUG. I have reported it here https://bugs.freedesktop.org/show_bug.cgi?id=38014 and I will use your explanation to provide further details. Thanks! Even with the array working it's not detecting non-numeric cells properly. Can you upload your spreadsheet somewhere so I can see what I'm doing wrong? -- View this message in context: http://nabble.documentfoundation.org/Calc-Has-anyone-tested-the-backward-compatibility-of-LibreOffice-3-4-tp3022764p3033666.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
plino wrote: Even with the array working it's not detecting non-numeric cells properly. Can you upload your spreadsheet somewhere so I can see what I'm doing wrong? Hi plino, sure, the upload link is: http://www.2shared.com/file/WuzUYsyS/FindNonNumeric.html See the formula in cell C2. Let me know whether or not you succeed. Regards Jack -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
On 7 Jun 2011 at 11:47, Stephan Zietsman wrote: Date sent: Tue, 7 Jun 2011 11:47:25 +0200 Subject:Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4? From: Stephan Zietsman szi...@gmail.com To: users@libreoffice.org Send reply to: users@libreoffice.org plino wrote: Even with the array working it's not detecting non-numeric cells properly. Can you upload your spreadsheet somewhere so I can see what I'm doing wrong? Hi plino, sure, the upload link is: http://www.2shared.com/file/WuzUYsyS/FindNonNumeric.html See the formula in cell C2. Let me know whether or not you succeed. Interesting... I did some other things with it. Calculate number of non-numeric values =COUNTA($A$2:$A$21)-COUNT($A$2:$A$21) Interestingly, the 3rd non-numeric value was a text formated filed in cell a9. Was interested if it could be modified for multi-column searches. Did a same test using H2:I5 and used this formula. = SMALL((IF(ISNUMBER($H$2:$I$5), ALL GOOD,COLUMN($H$2:$I$5)*1000+ROW($H$2:$I$5))), 1) That returns the number 8004 and 2nd one returns 9005. Then used this formula to convert to a cell address. =CHAR(INT(L2/1000)+64)MOD(L2,1000) That only works for first 26 columns, and upto 999 rows, but could be modified. Regards Jack -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted +--+ Michael D. Setzer II - Computer Science Instructor Guam Community College Computer Center mailto:mi...@kuentos.guam.net mailto:msetze...@gmail.com http://www.guam.net/home/mikes Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ +--+ http://setiathome.berkeley.edu (Original) Number of Seti Units Returned: 19,471 Processing time: 32 years, 290 days, 12 hours, 58 minutes (Total Hours: 287,489) BOINC@HOME CREDITS SETI10842777.379512 | EINSTEIN 5986464.870851 ROSETTA 3221339.084814 | ABC 6190575.950334 -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
plino wrote:
But as you said, I really don't want to browse 300.000 lines to spot
errors ;)
Hi plino,
for your specific case I might have a possible workaround. The following
formula (place it in any cell) would give you the row number for the first
non-numeric value in the range A1:A3.
{= MIN(IF(ISNUMBER(A1:A3), ALL GOOD, ROW(A1:A3)))}
Do not explicitly the curly braces, they appear automatically. It must be
entered as an array function, therefore press CTRL+SHIFT+ENTER to accept the
formula, not just ENTER.
Change the 2 references (A1:A3) to the cells you want to check. If you've
corrected the value (i.e. went to the row where the non-numeric value
appears and made it numeric) then it will refer to the next non-numeric row.
This way you can systematically address each issue (if there aren't too
many).
It's not a solution for the problem, just a workaround. Nevertheless, I
hope it helps.
Regards
Jack
--
View this message in context:
http://nabble.documentfoundation.org/Calc-Has-anyone-tested-the-backward-compatibility-of-LibreOffice-3-4-tp3022764p3029781.html
Sent from the Users mailing list archive at Nabble.com.
--
Unsubscribe instructions: E-mail to users+h...@libreoffice.org
In case of problems unsubscribing, write to postmas...@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/www/users/
All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
Hi Jack I couldn't make it work. In fact I just found out that array functions don't work in Lib (3.3 or 3.4) nor in OOo (3.4 beta) I don't know if it ever worked (I use it every now and then, so I couldn't say if I used it before in OOo/LO) but if it did, someone broke it :) Another bug to fix for 3.5 :) -- View this message in context: http://nabble.documentfoundation.org/Calc-Has-anyone-tested-the-backward-compatibility-of-LibreOffice-3-4-tp3022764p3032323.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
On Mon, Jun 6, 2011 at 3:40 PM, plino pedl...@gmail.com wrote: Hi Jack I couldn't make it work. In fact I just found out that array functions don't work in Lib (3.3 or 3.4) nor in OOo (3.4 beta) I don't know if it ever worked (I use it every now and then, so I couldn't say if I used it before in OOo/LO) but if it did, someone broke it :) Another bug to fix for 3.5 :) -- View this message in context: http://nabble.documentfoundation.org/Calc-Has-anyone-tested-the-backward-compatibility-of-LibreOffice-3-4-tp3022764p3032323.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted From nvrk - Here's a crude attempt at efficiently finding non numerical entries in a long column of numbers. For each entry =if(isnumber(A1)1;Row();) conditionally formatted in a bright color if desired. Single cell of =min(A1:Axxx) -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
Hello Tom, On Sat, 4 Jun 2011 15:22:50 +0100 (BST) (your time) you said: ... i played around with Format Cells. I couldn't get an ideal answer that shows a - even when the cell is empty but i could get one when the cell has 0 value. Well, too much time later and I haven't been able to work it out either, Tom. Even if you do get a - to show up in an empty cell (using COUNTBLANK for example), it still isn't useful because - in a time formatted cell always results is '#VALUE!'. I clearly don't know enough to be able help here. :-) -- Si (PLO) #31064. Now Hew Sore Dig? ¶ Auxiliary Information: • LibreOffice 3.4.0 OOO340m1 (Build:12) • Windows XP Pro 5.1.2600 Service Pack 3 -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
Hi :) Blimey!! Good work! Hopefully that might help with other issues later even if it couldn't solve this problem this time. Thanks and regards from Tom :) - Original Message From: PLO protect.libreoff...@inboxshield.co.uk To: users@libreoffice.org Sent: Sun, 5 June, 2011 13:10:01 Subject: Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4? Hello Tom, On Sat, 4 Jun 2011 15:22:50 +0100 (BST) (your time) you said: ... i played around with Format Cells. I couldn't get an ideal answer that shows a - even when the cell is empty but i could get one when the cell has 0 value. Well, too much time later and I haven't been able to work it out either, Tom. Even if you do get a - to show up in an empty cell (using COUNTBLANK for example), it still isn't useful because - in a time formatted cell always results is '#VALUE!'. I clearly don't know enough to be able help here. :-) -- Si (PLO) #31064. Now Hew Sore Dig? ¶ Auxiliary Information: • LibreOffice 3.4.0 OOO340m1 (Build:12) • Windows XP Pro 5.1.2600 Service Pack 3 -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
I have a solution to the specific problem, based on Michael D. Setzer II's suggestion (see below): =IF(AND(ISNUMBER(C227), ISNUMBER(B227)),IF(C227-B2270,C227-B227,0), 0) =IF(ISNUMBER(F227),IF(ISNUMBER(E227),IF(ISNUMBER(G227),IF(F227-E227-G2270,F227-E227-G227,0),IF(F227-E2270,F227-E227,0)),IF(F2270,F227,0)), 0) However, this doesn't resolve the main issue of compatibility with Microsoft Office and OpenOffice.org: what is LibreOffice's target user? If it is an OpenDocument-compliant user, then there is no issue here at all, also if the target user is a Microsoft Office user, then this issue will not exist either, however OpenOffice.org users could be dissuaded, as the issue will affect them. An option to relax the restrictions in the N() function could work for former OOo users? Fortunately MS Excel users will be happy! -- View this message in context: http://nabble.documentfoundation.org/Calc-Has-anyone-tested-the-backward-compatibility-of-LibreOffice-3-4-tp3022764p3027868.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
It must be a new feature :) I advise you to test 3.3 RC1 and update to 3.3 when it is released. Version 3.4 is not ready for real work as stated in the release announcement http://nabble.documentfoundation.org/The-Document-Foundation-announces-LibreOffice-3-4-0-tt3019206.html -- View this message in context: http://nabble.documentfoundation.org/Calc-Has-anyone-tested-the-backward-compatibility-of-LibreOffice-3-4-tp3022764p3022791.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
Actually I had never noticed that spreadsheets (all including Excel) ignore text values mixed with numbers. This worries me a lot! I work with 300.000+ line spreadsheets and if one line has a text value (because of a typo) I wouldn't notice that. Is there any setting that triggers a warning (instead of ignoring the cell(s)) in this situation? -- View this message in context: http://nabble.documentfoundation.org/Calc-Has-anyone-tested-the-backward-compatibility-of-LibreOffice-3-4-tp3022764p3023116.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
The following screenshot is taken from a spreadsheet that previously showed 00:00 in LibreOffice 3.3.2, where LibreOffice 3.4.0 is now showing #VALUE! (NB: all the cells are formatted as Time values, and the text cells contain -): http://nabble.documentfoundation.org/file/n3023141/%23VALUE-screenshot.png -- View this message in context: http://nabble.documentfoundation.org/Calc-Has-anyone-tested-the-backward-compatibility-of-LibreOffice-3-4-tp3022764p3023141.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
Hi :) I think those empty cells (that now contain - ) might be causing the error. If you delete the - in just 1 row does that fix the formula to display correctly? I think number cells can be formatted to show a - mark if empty but 3.4 might have got confused and thought those - were keyed in rather than being part of the formatting. It's a long time since i dealt with this sort of thing and that was in Excel so i could easily be utterly wrong. Regards from Tom :) - Original Message From: prholland o...@hollandnumerics.com To: users@libreoffice.org Sent: Sat, 4 June, 2011 14:26:56 Subject: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4? The following screenshot is taken from a spreadsheet that previously showed 00:00 in LibreOffice 3.3.2, where LibreOffice 3.4.0 is now showing #VALUE! (NB: all the cells are formatted as Time values, and the text cells contain -): http://nabble.documentfoundation.org/file/n3023141/%23VALUE-screenshot.png -- View this message in context: http://nabble.documentfoundation.org/Calc-Has-anyone-tested-the-backward-compatibility-of-LibreOffice-3-4-tp3022764p3023141.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
Hello prholland, On Sat, 4 Jun 2011 06:26:56 -0700 (PDT) (your time) you said: The following screenshot is taken from a spreadsheet that previously showed 00:00 in LibreOffice 3.3.2, where LibreOffice 3.4.0 is now showing #VALUE! (NB: all the cells are formatted as Time values, and the text cells contain -): Tom is correct, just tried it. If the cell is formatted as time, you get '#VALUE!' as '-' isn't recognised as a time value. Taking the '-' out and leaving the cells empty works. I've worked with time sheets in 3.3.2 as well but then I never used a hyphen to indicate 'no hours'. Maybe it was a 'fix' in this new version. -- Si (PLO) #32955. Ego Sow Whir Den? ¶ Auxiliary Information: • LibreOffice 3.4.0 OOO340m1 (Build:12) • Windows XP Pro 5.1.2600 Service Pack 3 -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
Hi :) Thanks PLO. Now that you have confirmed that is likely to be the issue i played around with Format Cells. I couldn't get an ideal answer that shows a - even when the cell is empty but i could get one when the cell has 0 value. Select the range of cells and from the top menus select Format - Cells - Number and look in the bottom box Format code. The standard format for time is HH:MM which pedantically only covers +ve values. Adding something specific for -ve values, such as HH:MM;[RED]HH:MM helps 'flag-up' if soemthing has gone badly wrong. The ; (semi-colon) allows negative values to have their own formatting rather than just defaulting to whatever +ve values have. A next ; allows us to set how we want 0 values to be treated so HH:MM;[RED]HH:MM ;- puts a - instead of a 0 value. Note that values that are close enough to 0 to be rounded to 0 are still shown as either black or red 00:00s in that example. I tried adding more ; to see if that would let us give a - for an empty value but it didn't work. I'm sure there is a tick-box somewhere to do that but it doesn't seem to be in the format cells dialogue box in 3.3.2. Regards from Tom :) - Original Message From: PLO protect.libreoff...@inboxshield.co.uk To: users@libreoffice.org Sent: Sat, 4 June, 2011 14:58:49 Subject: Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4? Hello prholland, On Sat, 4 Jun 2011 06:26:56 -0700 (PDT) (your time) you said: The following screenshot is taken from a spreadsheet that previously showed 00:00 in LibreOffice 3.3.2, where LibreOffice 3.4.0 is now showing #VALUE! (NB: all the cells are formatted as Time values, and the text cells contain -): Tom is correct, just tried it. If the cell is formatted as time, you get '#VALUE!' as '-' isn't recognised as a time value. Taking the '-' out and leaving the cells empty works. I've worked with time sheets in 3.3.2 as well but then I never used a hyphen to indicate 'no hours'. Maybe it was a 'fix' in this new version. -- Si (PLO) #32955. Ego Sow Whir Den? ¶ Auxiliary Information: • LibreOffice 3.4.0 OOO340m1 (Build:12) • Windows XP Pro 5.1.2600 Service Pack 3 -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
On 4 Jun 2011 at 6:06, plino wrote: Date sent: Sat, 4 Jun 2011 06:06:55 -0700 (PDT) From: plino pedl...@gmail.com To: users@libreoffice.org Subject:[libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4? Send reply to: users@libreoffice.org Actually I had never noticed that spreadsheets (all including Excel) ignore text values mixed with numbers. This worries me a lot! I work with 300.000+ line spreadsheets and if one line has a text value (because of a typo) I wouldn't notice that. If you use count on a range it only counts numeric cells, but counta counts numeric and non-empty cells, so if they don't give the same results for the same range, there is an error? Is there any setting that triggers a warning (instead of ignoring the cell(s)) in this situation? -- View this message in context: http://nabble.documentfoundation.org/Calc-Has-anyone-tested-the-backward-compatibility-of-LibreOffice-3-4-tp3022764p3023116.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted +--+ Michael D. Setzer II - Computer Science Instructor Guam Community College Computer Center mailto:mi...@kuentos.guam.net mailto:msetze...@gmail.com http://www.guam.net/home/mikes Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ +--+ http://setiathome.berkeley.edu (Original) Number of Seti Units Returned: 19,471 Processing time: 32 years, 290 days, 12 hours, 58 minutes (Total Hours: 287,489) BOINC@HOME CREDITS SETI10835199.920471 | EINSTEIN 5971444.300851 ROSETTA 3209136.449584 | ABC 6134395.303758 -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
Hi prholland, prholland schrieb: The following screenshot is taken from a spreadsheet that previously showed 00:00 in LibreOffice 3.3.2, where LibreOffice 3.4.0 is now showing #VALUE! (NB: all the cells are formatted as Time values, and the text cells contain -): http://nabble.documentfoundation.org/file/n3023141/%23VALUE-screenshot.png The ODF spec has some rules for converting text to numbers. Those conversion is different whether the text occurs in a simple calculation with operator or the text occurs in a range in function SUM or similar. As LO claims to follow ODF spec, it has to respect this. In future you have to respect, that + - * / ^ will not work on text. You have to ensure, that the operands are numbers. In OOo this can be done by the function N. Unfortunately the spec makes it implemention defined what N does with text. In OOo N returns 0 for text, which I think is useful. In LO it returns #VALUE although the help saws it would return 0. So this behavior seems a bug to me in the N function. Otherwise I would have recommend to use N(C1)-N(B1) instead of C1-B1. I notice, that Tom has already found the solution, to enter 0 and format it to show - . Kind regards Regina -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
Michael D. Setzer II wrote: If you use count on a range it only counts numeric cells, but counta counts numeric and non-empty cells, so if they don't give the same results for the same range, there is an error? Yes, that would work but it forces me to do this verification for each column. And still it won't tell me where the error is (although I can find it with some filters) It would be much better if there was some AI in these functions warning me that Data in Cell A12345 is not a number. What do you want to do? A) Jump to cell A12345 and manually fix it or B) Ignore this warning and Sum all other cells? Why not use the computing power of the PC to help us? -- View this message in context: http://nabble.documentfoundation.org/Calc-Has-anyone-tested-the-backward-compatibility-of-LibreOffice-3-4-tp3022764p3023698.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
Brian Barker wrote: One useful facility in this case is Value Highlighting. Go to View | Value Highlighting or press Ctrl+F8. The font colour for text (temporarily) becomes black, for numbers and other values blue, and for formulae green. (Formulae should be no problem, since it's possible to construct them sufficiently carefully that you can be sure of the type of the result, of course.) Repeat the process to toggle the facility back off. I didn't know that. Could be useful sometime ;) If I wanted to visually check I would use Conditional formatting and set Font to Red and Bold and the Background to Bright Yellow :) But as you said, I really don't want to browse 300.000 lines to spot errors ;) -- View this message in context: http://nabble.documentfoundation.org/Calc-Has-anyone-tested-the-backward-compatibility-of-LibreOffice-3-4-tp3022764p3023917.html Sent from the Users mailing list archive at Nabble.com. -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
2011/6/4 Regina Henschel rb.hensc...@t-online.de: Hi prholland, prholland schrieb: The following screenshot is taken from a spreadsheet that previously showed 00:00 in LibreOffice 3.3.2, where LibreOffice 3.4.0 is now showing #VALUE! (NB: all the cells are formatted as Time values, and the text cells contain -): http://nabble.documentfoundation.org/file/n3023141/%23VALUE-screenshot.png The ODF spec has some rules for converting text to numbers. Those conversion is different whether the text occurs in a simple calculation with operator or the text occurs in a range in function SUM or similar. As LO claims to follow ODF spec, it has to respect this. In future you have to respect, that + - * / ^ will not work on text. You have to ensure, that the operands are numbers. In OOo this can be done by the function N. Unfortunately the spec makes it implemention defined what N does with text. In OOo N returns 0 for text, which I think is useful. In LO it returns #VALUE although the help saws it would return 0. So this behavior seems a bug to me in the N function. Otherwise I would have recommend to use N(C1)-N(B1) instead of C1-B1. I notice, that Tom has already found the solution, to enter 0 and format it to show - . Kind regards Regina Someone would better file a bug report then, right? Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
Hi Johnny, Johnny Rosenberg schrieb: 2011/6/4 Regina Henschelrb.hensc...@t-online.de: [..] In OOo this can be done by the function N. Unfortunately the spec makes it implemention defined what N does with text. In OOo N returns 0 for text, which I think is useful. In LO it returns #VALUE although the help saws it would return 0. So this behavior seems a bug to me in the N function. Otherwise I would have recommend to use N(C1)-N(B1) instead of C1-B1. [..] Someone would better file a bug report then, right? It is already there https://bugs.freedesktop.org/show_bug.cgi?id=33705 Kind regards Regina -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
2011/6/4 Regina Henschel rb.hensc...@t-online.de: Hi Johnny, Johnny Rosenberg schrieb: 2011/6/4 Regina Henschelrb.hensc...@t-online.de: [..] In OOo this can be done by the function N. Unfortunately the spec makes it implemention defined what N does with text. In OOo N returns 0 for text, which I think is useful. In LO it returns #VALUE although the help saws it would return 0. So this behavior seems a bug to me in the N function. Otherwise I would have recommend to use N(C1)-N(B1) instead of C1-B1. [..] Someone would better file a bug report then, right? It is already there https://bugs.freedesktop.org/show_bug.cgi?id=33705 Kind regards Regina Oops… unfortunately I already wrote one. I tried to search but failed. Got that ”internal server error” thing when searching. I can't see how to vote for it, though. Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?
- Original Message From: Johnny Rosenberg gurus.knu...@gmail.com To: users@libreoffice.org Sent: Sat, 4 June, 2011 20:27:51 Subject: Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4? 2011/6/4 Regina Henschel rb.hensc...@t-online.de: Hi Johnny, Johnny Rosenberg schrieb: 2011/6/4 Regina Henschelrb.hensc...@t-online.de: [..] In OOo this can be done by the function N. Unfortunately the spec makes it implemention defined what N does with text. In OOo N returns 0 for text, which I think is useful. In LO it returns #VALUE although the help saws it would return 0. So this behavior seems a bug to me in the N function. Otherwise I would have recommend to use N(C1)-N(B1) instead of C1-B1. [..] Someone would better file a bug report then, right? It is already there https://bugs.freedesktop.org/show_bug.cgi?id=33705 Kind regards Regina Oops… unfortunately I already wrote one. I tried to search but failed. Got that ”internal server error” thing when searching. I can't see how to vote for it, though. Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ Hi :) Me too, i don't know how to vote either. I think the triagers are able to link bug-reports together if you have posted a new one or perhaps you could help them and link it yourself? I think that any activity like that tends to bump the thread so that people notice it again. I think writing a comment also bumps it but that's frowned on. Regards from Tom :) -- Unsubscribe instructions: E-mail to users+h...@libreoffice.org In case of problems unsubscribing, write to postmas...@documentfoundation.org Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/www/users/ All messages sent to this list will be publicly archived and cannot be deleted
