[Dropped] 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.

[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 2607 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)