[Issue] OOo equivalent to IFERROR?

[NVANLAAR]

I have been an avid MS office user at my old office, but on my own now can no longer afford that.

I need help porting some of my spreadsheets over. My first problem is IFERROR functions in MS don'e translate, so is there some sort of equivalant function or work-around here in Open Office?
Example statement:

Code: Select all

=IFERROR((IF(I10=0;0;((D10)*(E10/I10))));"-")

Basically what happens is if an error is returned I have "-" displayed. I do this because quite often some cells are blank in the form and create a #DIV/0!
How can this be rewritten to work in OO?

Thanks for any help
Nick

[aloarjr810]

Unless I'm miss reading your post. This is what you want.

Code: Select all

=IF(I10=0;"-";((D10)*(E10/I10)))

The cell this formula is in will show a - if I10 is blank.
If you replace the "-" with "" the cell will remain blank till something is entered in I10.

[acknak]

There is an issue tracking this problem: Issue 92677: ODFF: implement IFERROR and IFNA
You can register there and add your vote (up to two) or comment.

The target was OOo 3.x, but the comments suggest that may well slip. Better to simply avoid the function and use IF() and ISERROR(), or explicitly test for empty cells: =IF(ISBLANK(I10);"-";IF(I10=0;0;(E10/I10)))

[aloarjr810]

explicitly test for empty cells: =IF(ISBLANK(I10);"-";IF(I10=0;0;(E10/I10)))

That formula is missing the I10* part of the O.P's original formula, with out that the formula doesn't return a full answer. It should be more like this:

Code: Select all

=IF(ISBLANK(I10);"-";IF(I10=0;0;(D10*(E10/I10))))

But I would think the ISBLANK is unneeded. Its testing for a empty cell in I10 and if empty return a "-". But Then the next IF IF(I10=0;0; is testing for the same thing and if empty wants to return a 0.

I think this is all you need, it does the same thing and is more compact.

Code: Select all

=IF(I10=0;"-";((D10)*(E10/I10)))

[acknak]

That formula is missing the I10* part...

Quite right. Fumble fingered cut/paste. Thanks!

[spiders]

I personally use the rather cumbersome, but completely working method :

IF(ISERROR(STATEMENT);"";(STATEMENT))

example: =IF(ISERROR(H12/(B12-C12)) ;"";(H12/(B12-C12)))


This results in any errors just leaving blank cells. Great for when you are using date entered data and you want to define the entire table when you create it, without seeing DIV/0 errors in every row that doesn't have data in it yet. if you would like to have a - on there, simply replace the "" with "-"

(and yes, I know this topic is ancient - but it's what comes up when you search for IFERROR openoffice on google )

[Lupp]

Beyond the length of the formulae it's a pity that returning a value (no error) by this method may require a complicated calculation to be performed twice.

[RusselB]

Agreed. I used to use a spreadsheet back in high school (DOS based) that allowed the usage of special variables, specifically $V1 and $V2 (case sensitive so that they wouldn't get confused with the absolute cell references for column V row(s) 1 &/or 2).
They were used in a format like =if(<calculation>=<result>;$V1;$V2) where $V1 returns the same as <calculation> and $V2 returns the same as <result> (which, of course, could be another calculation)

[Villeroy]

Agreed. I used to use a spreadsheet back in high school (DOS based) that allowed the usage of special variables, specifically $V1 and $V2 (case sensitive so that they wouldn't get confused with the absolute cell references for column V row(s) 1 &/or 2).
They were used in a format like =if(<calculation>=<result>;$V1;$V2) where $V1 returns the same as <calculation> and $V2 returns the same as <result> (which, of course, could be another calculation)

named_expression.ods

Testing preconditions by named expression with relative ref

(12.9 KiB) Downloaded 396 times

[acknak]

... this method may require a complicated calculation to be performed twice.

Not necessarily. Calc may cache the value of the subexpression. I don't know if it does or not; most spreadsheets are small enough that it doesn't really matter.

[estatistics]

I have a problem


I cannot mask the following error in libreoffice with iferror!

SMALL(IF(ISTEXT(V$4:V$1002)<>1;ROW(B$4:B$1000));ROWS(B$4:B372)))

Why?

[RusselB]

While I don't have LibreOffice installed, I'm wondering if the reason might be due to the type of error.
That said, what is the error message you are getting?

[estatistics]

#value error!

i am trying to use now, somethign liek this! (ISNUMBER(V$4:V$1002)+1)*1

[F3K Total]

CROSSPOSTING

[Lupp]

(Again: There is no [not yet an] OO / AOO 4.2.8.2. "estatistics" is supposed to use V4.2.8.2 of LibreOffice.)

Code: Select all

SMALL(IF(ISTEXT(V$4:V$1002)<>1;ROW(B$4:B$1000));ROWS(B$4:B372)))

Don't you think you should study the fundamentals a bit first?

A Remarks concerning the syntax.
1. The above is not a formula because it is missing the leading "=". If thought to be evaluated in array mode it is also missing the surrounding pair of curly brackets.
2. It cannot be a correct formula in addition because the parentheses do not pair.
3. If we read the part

Code: Select all

IF(ISTEXT(V$4:V$1002)<>1;ROW(B$4:B$1000))

including the final closing parenthese we get an incomplete alternative. (The ELSE part will therefore always evaluate to FALSE. This a remark concerning the semantics.)
4. If we omit that closing parenthese, the IF expression will read

Code: Select all

IF(ISTEXT(V$4:V$1002)<>1;ROW(B$4:B$1000);ROWS(B$4:B372))

and will be the only parameter of SMALL. SMALL, however requires a second parameter.

B Semantics
1. As SMALL requires an array (to be exact: NumberSequenceList) as its first parameter, the so called formula you posted is supposed to only make sense if evaluated in array mode. Otherwise at least the condition would evaluate in intersection mode. If the IF expression passes an array (NumberSequenceList?) nonetheless to SMALL in this case it is not clear to me how to disambiguate the effects of IF in specific cases. It surely cannot work this way outside SMALL.
2. "SMALL(ROW(B$4:B$1000);k)" has the much simpler equivalent "3+k" for k=1 (1) 997. "SMALL(ROW(B$4:B372);j)" ("$" missing?) again equals "3+j" but now restricted to j=1 (1) 369.
3. Thus the experimentally rectified formula "=SMALL(IF(ISTEXT(V$4:V$1002)<>1;ROW(B$4:B$1000);ROWS(B$4:B372);n)" should evaluate to 3+n for at least n=1 (1) 369, and at most n=1 (1) 997. The exact limit depending on the contents of column V.

C Remarks
1. It often is more promising to ask about something you want to achieve instead of expecting contributors to follow a possibly fruitless approach.
2. I am open for every hint showing me in what way my above statements are wrong (or based on a misunderstanding.)
3. Also see bug tdf#95226 (https://bugs.documentfoundation.org/sho ... i?id=95226). The patch should work in LibO V4.4.6 and higher.

[estatistics]

from here http://libreofficeforum.org/node/12277#comment-45494,

they told me this: " See bug tdf#95226"

I think this is a bug actually. thanks for the long desciption of my formula.
Yes some typos exist. sorry for that!

dI did whatever I wanted but i lost time, trying to see if am wrong when the actual program was buggy about the way evaluating some things.