![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
Someone would better file a bug report then, right?
Kind regards
Johnny Rosenberg
ジョニー・ローゼンバーグ
Hi Johnny,
Johnny Rosenberg schrieb:
[..]
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
Hello Tom,
... 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.
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
From: PLO <protect.libreoffice@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,
> ... 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+help@libreoffice.org
In case of problems unsubscribing, write to postmaster@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
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-B227>0,C227-B227,0), 0)
=IF(ISNUMBER(F227),IF(ISNUMBER(E227),IF(ISNUMBER(G227),IF(F227-E227-G227>0,F227-E227-G227,0),IF(F227-E227>0,F227-E227,0)),IF(F227>0,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!
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
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
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)
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
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?
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
Date sent: Tue, 7 Jun 2011 11:47:25 +0200
tested the
backward-compatibility of LibreOffice 3.4?
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.htmlSee 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.
Hi Jack
Jack wrote:
See the formula in cell C2. Let me know whether or not you succeed.
It does work perfectly. In fact the formula was already working on my
spreadsheet (after I used your array trick).
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
Thank you very much! This will save me a lot of time in the future!
I hope this will also contribute for the array bug to be fixed since I
reported it on the bug tracker...
Regards
Pedro
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
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
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
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).
Regards
Stephan
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!