IFERROR formula from Excel

[Mardana]

I have an Excel spreadsheet sent to me with the following formula in various cells. iferror(L18/L19;0) When I apply it to OO I get the following #NAME?
=if(iserror(SUMPRODUCT((((--TEXT($B5;"[mm]")<=--TEXT(MOD($Data.$E$5:$E$2550;1);"[mm]"))*(--TEXT($C5;"[mm]")>=--TEXT(MOD($Data.$E$5:$E$2550;1);"[mm]")))*($Data.$E$5:$E$2550<>""))*(Q$4=$Data.$I$5:$I$2550));SUMPRODUCT(((if(iserror((--TEXT($B5;"[mm]")<=--if(iserror(--TEXT(--MID($Data.$E$5:$E$2550;FIND(" ";$Data.$E$5:$E$2550)+1;LEN($Data.$E$5:$E$2550));"[mm]");0));0)*if(iserror((--TEXT($C5;"[mm]")>=--if(iserror(--TEXT(--MID($Data.$E$5:$E$2550;FIND(" ";$Data.$E$5:$E$2550)+1;LEN($Data.$E$5:$E$2550));"[mm]");0));0)*($Data.$E$5:$E$2550<>""))*(Q$4=$Data.$I$5:$I$2550)))). Can someone please advise the solution to how to fix this Excel formula to OpenOffice.

Edit: Split from Add column to page 1 printout as this is a different question.

[FJCC]

iferror(L18/L19;0)

OpenOffice does not have the iferror() function. You can get the same result with

Code: Select all

=IF(ISERROR(L18/L19);0;L18/L19)

I can't make any sense out of the second formula you posted. Is that the complete formula?

[robleyd]

OpenOffice Calc does not have the function IFERROR; you'll need to re-write the formulae to use IF() and ISERROR(). For instance

Code: Select all

=iferror(L18/L19;0)

would be rewritten as

Code: Select all

=IF(ISERROR(L18/L19);0;L18/L19)

Alternatively you may try LibreOffice which does have the IFERROR function.

[MrProgrammer]

… iserror(--TEXT(--MID($Data.$E$5:$E$2550;FIND(" ";$Data.$E$5:$E$2550)+1;LEN($Data.$E$5:$E$2550));"[mm]");0) …

This part of your Excel monster formula is bogus. FIND takes two parameters. LEN takes one parameter. MID takes three parameters. TEXT takes two parameters. ISERROR takes one parameter but your formula supplies two. I doubt if this is a valid Excel formula. Perhaps Ithe original function in Excel was IFERROR, which does take two parameters. In Excel IFERROR(expression;0) would mean: if the expression returns an error the function result is 0 otherwise it's the expression's value. Others have told you how to do that in OpenOffice.

ISERROR(--TEXT(--MID($Data.$E$5:$E$2550;FIND(" ";$Data.$E$5:$E$2550)+1;LEN($Data.$E$5:$E$2550));"[mm]");0)
                                        FIND(111;222222222222222222)   LEN(111111111111111111)            
                 MID(111111111111111111;222222222222222222222222222222;33333333333333333333333)           
          TEXT(11111111111111111111111111111111111111111111111111111111111111111111111111111111;222222)   
ISERROR(11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111;2)

There may be other problems as well. I decline to attempt to fix the bogus Excel formula. The best way to determine what is wrong with a monster formula is to break it into multiple cells, as many as needed, each of which calls just one or two functions. Then you can examine each cell's formula and value until you find the problem. You will make more work for yourself if, after finding the trouble, you reassemble the monster formula.

I can't make any sense out of the second formula you posted.

This does look like other horrid Excel formulas I've seen. Excel considers boolean values to be distinct from numeric values, so numeric operations on booleans like SUM(boolean;boolean) can fail because the values are ignored, and boolean operations on numbers can fail for the same reason. The double negative is used to convert logical values to numeric values. The AND(number;number) operation is written as (number)*(number). These difficulties go away in Calc since boolean FALSE and TRUE are equivalent to 0 and 1. Maybe ‑‑MID and ‑‑TEXT are being used to convert text from those functions to numeric values, though I don't recall seeing this method elsewhere. I would use the VALUE function to make the intent clear.

[keme]

There is a problem with your posted "monster formula" structure. It shows 40 "open parenthesis" and only 35 "close parenthesis", and all appear to be part of formula structure (none are quoted). This discrepancy may be caused by a limitation on formula length or nesting depth in your installed version of Calc, or in some other software your file has "visited", which "chopped off" the last part of the formula.

A few suggestions:

• Can you explain what the formula is supposed to do? A different formula, hopefully also compatible with Excel, may then be devised.
• Can you share the file, so we can observe the formula "live"?
• Can you open the file in Excel and copy the offending formula from there?

[Mardana]

There is a problem with your posted "monster formula" structure. It shows 40 "open parenthesis" and only 35 "close parenthesis", and all appear to be part of formula structure (none are quoted). This discrepancy may be caused by a limitation on formula length or nesting depth in your installed version of Calc, or in some other software your file has "visited", which "chopped off" the last part of the formula.

A few suggestions:

• Can you explain what the formula is supposed to do? A different formula, hopefully also compatible with Excel, may then be devised.
• Can you share the file, so we can observe the formula "live"?
• Can you open the file in Excel and copy the offending formula from there?

[Mardana]

We attached for the file as we provide from excel, when we operated in excel there is normal, when we open in OOc, the problem was, #name, after we changed every if error with If(iserror), the result is err:508

[Mardana]

Actually the formula which we submit to excel as follow
=iferror(SUMPRODUCT((((--TEXT($B5;"[mm]")<=--TEXT(MOD($Data.$E$5:$E$2550;1);"[mm]"))*(--TEXT($C5;"[mm]")>=--TEXT(MOD($Data.$E$5:$E$2550;1);"[mm]")))*($Data.$E$5:$E$2550<>""))*(Q$4=$Data.$I$5:$I$2550));SUMPRODUCT(((iferror((--TEXT($B5;"[mm]")<=--iferror(--TEXT(--MID($Data.$E$5:$E$2550;FIND(" ";$Data.$E$5:$E$2550)+1;LEN($Data.$E$5:$E$2550));"[mm]");0));0)*iferror((--TEXT($C5;"[mm]")>=--iferror(--TEXT(--MID($Data.$E$5:$E$2550;FIND(" ";$Data.$E$5:$E$2550)+1;LEN($Data.$E$5:$E$2550));"[mm]");0));0)*($Data.$E$5:$E$2550<>""))*(Q$4=$Data.$I$5:$I$2550)))). And the result well done, but when we changed to OO, there is no result, and mismistake. I was follow for the instruction with another person, but the result still not matched.

[Alex1]

Function names displayed in lowercase are unknown to Calc. LibreOffice Calc does know IFERROR.
D5 contains =IF(Data.E5:E36 <>"", COUNTIFS(Data.E5:E36, ">=" & TIME(2, 0, 0), Data.E5:E36, "<=" & TIME(2, 59, 59), Data.G5:G36, "Airport"), "Data tidak valid")
It contains commas instead of semicolons as separators, which is wrong.
When I open it in Excel I see only a zero, not a formula. Something must have gone wrong with the conversion. Do you have the original XLSX version?

[Mardana]

hi Alex.
here with we attached the xlsx . Actually when we changed the profil and suburb they Will work

[robleyd]

File is not attached. See How to attach a document for details regarding attaching a document.

[Mardana]

File is not attached. See How to attach a document for details regarding attaching a document.

Here with we attach the file xlsx

[Hagar Delest]

Nothing useful it seems:

Calc_scrshot.png (15.65 KiB) Viewed 548 times

[Alex1]

This contains mostly errors, and I cannot find anything resembling the formulas you gave. Is this the wrong file?

[mikele]

Hi,
instead of

Code: Select all

--TEXT($B5;"[mm]")

and

Code: Select all

--TEXT(MOD($Data.$E$5:$E$2550;1);"[mm]")

use

Code: Select all

MINUTE($B5)

and

Code: Select all

MINUTE($Data.$E$5:$E$2550)

[karolus]

Hi,
instead of

Code: Select all

--TEXT($B5;"[mm]")

and

Code: Select all

--TEXT(MOD($Data.$E$5:$E$2550;1);"[mm]")

use

Code: Select all

MINUTE($B5)

and

Code: Select all

MINUTE($Data.$E$5:$E$2550)

unfortunatly no, but:

Code: Select all

INT(MOD($B5;1)*1440)

and

Code: Select all

INT(MOD($Data.$E$5:$E$2550;1)*1440)