Page 1 of 1
[Dropped] IFERROR formula from Excel
Posted: Tue Oct 29, 2024 2:57 am
by 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.
Re: Formula from Excel to OO
Posted: Tue Oct 29, 2024 3:36 am
by FJCC
Mardana wrote: ↑Tue Oct 29, 2024 2:57 am
iferror(L18/L19;0)
OpenOffice does not have the iferror() function. You can get the same result with
I can't make any sense out of the second formula you posted. Is that the complete formula?
Re: Formula from Excel to OO
Posted: Tue Oct 29, 2024 3:37 am
by robleyd
OpenOffice Calc does not have the function IFERROR; you'll need to re-write the formulae to use IF() and ISERROR(). For instance
would be rewritten as
Alternatively you may try LibreOffice which does have the IFERROR function.
Re: Formula from Excel to OO
Posted: Tue Oct 29, 2024 11:44 am
by 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?
Re: Formula from Excel to OO
Posted: Wed Oct 30, 2024 3:21 am
by Mardana
keme wrote: ↑Tue Oct 29, 2024 11:44 am
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?
Re: Formula from Excel to AOO
Posted: Wed Oct 30, 2024 3:24 am
by 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
Re: Formula from Excel to AOO
Posted: Wed Oct 30, 2024 4:04 am
by 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.
Re: Formula from Excel to AOO
Posted: Wed Oct 30, 2024 8:48 pm
by 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?
Re: Formula from Excel to AOO
Posted: Fri Nov 01, 2024 3:16 am
by Mardana
hi Alex.
here with we attached the xlsx . Actually when we changed the profil and suburb they Will work
Re: Formula from Excel to AOO
Posted: Fri Nov 01, 2024 3:20 am
by robleyd
File is not attached. See
How to attach a document for details regarding attaching a document.
Re: Formula from Excel to AOO
Posted: Sat Nov 02, 2024 2:41 am
by Mardana
Here with we attach the file xlsx
Re: Formula from Excel to AOO
Posted: Sat Nov 02, 2024 12:50 pm
by Hagar Delest
Nothing useful it seems:
- Calc_scrshot.png (15.65 KiB) Viewed 2611 times
Re: Formula from Excel to AOO
Posted: Sun Nov 03, 2024 3:03 am
by Alex1
This contains mostly errors, and I cannot find anything resembling the formulas you gave. Is this the wrong file?
Re: Formula from Excel to AOO
Posted: Sun Nov 03, 2024 4:41 pm
by mikele
Hi,
instead of
and
Code: Select all
--TEXT(MOD($Data.$E$5:$E$2550;1);"[mm]")
use
and
Re: Formula from Excel to AOO
Posted: Sun Nov 03, 2024 6:15 pm
by karolus
mikele wrote: ↑Sun Nov 03, 2024 4:41 pm
Hi,
instead of
and
Code: Select all
--TEXT(MOD($Data.$E$5:$E$2550;1);"[mm]")
use
and
unfortunatly no, but:
and
Code: Select all
INT(MOD($Data.$E$5:$E$2550;1)*1440)