Excel file opened with LO has a formula that reads #VALUE!

[rangerjim]

I’m using version 6, also have the same problem with version 5.
Here is the formula. {=IF(A7="","",INDEX($par_équipe.$B$5:$B$42,SMALL(IF(IF($par_équipe.$A$6:$A$43=“m”,$par_équipe.$C$5:$C$42=B7),ROW($par_équipe.$C$5:$C$42)-ROW($par_équipe.$C$5)+1),COUNTIF($B$7:B7,B7))))}

I think the problem is the two IF statements together. This works with excel.
Because this formula works (it does not have the double IF statement.
{=IF(Y7="","",INDEX($par_équipe.$B$5:$B$42,LARGE(IF($par_équipe.$E$5:$E$42=Z7,ROW($par_équipe.$E$5:$E$42)-ROW($par_équipe.$D$5)+1),COUNTIF($Z$7:Z7,Z7))))}

Looks like I need to modify my formula but I don’t know what to do.

Here is the complete spreadsheet. The excel file has no errors but the LO does, anywhere there are two IF statements together.
Go to the Calculate page, notice C7 has error, all other cells with same formula have this error. On the far right in column AA the formula is the same, except, there is no if reference to “m”.

Dards Stats 2017-2018 1803.07.ods

Dards Stats 2017-2018 1803.07.xlsx

HELP.
Hopefully, someone has an answer.

[jimk]

The formula with two IF statements can work in Calc. Here is an example file that shows a result of 9 in cell AI5 (that’s capital A, capital I, number 5) using the formula: two if statements.ods

Here is the array formula indented for readability.

=IF(A7="";"";INDEX(
    $par_équipe.$B$5:$B$42;
    SMALL(
        IF(IF($par_équipe.$A$6:$A$43="m";$par_équipe.$C$5:$C$42=B7);
            ROW($par_équipe.$C$5:$C$42)-ROW($par_équipe.$C$5)+1);
        COUNTIF($B$7:B7;B7))))

Tested with LO 5.4.3.2.

[rangerjim]

Thanks for the answer, apparently you know what you are doing, but unfortunately I’m I don’t understand it.
I get a #VALUE! in the formula.
Obviously there is something I’m doing wrong, but don’t know what to do.
I can send you my spreadsheet and perhaps you will see the error.
How do I send you a copy of my spreadsheet? I can send you my Excel spreadsheet (it works) and my LO spreadsheet (it has the error).
Thanks

[jimk]

Edit the original question and attach the LO file. You should have enough karma points now.

[rangerjim]

Hi all,
I’ve added the excel and LO file to the original support.
Hopefully someone has an answer to my problem.
Thanks,

[erAck]

The reason is that this part of the formula

IF(IF($par_équipe.$A$6:$A$43="m";$par_équipe.$C$5:$C$42=B16);ROW($par_équipe.$C$5:$C$42)-ROW($par_équipe.$C$5)+1)

in array mode in some elements delivers #VALUE! errors, you can check if you enter that standalone as array formula.

Underlying seems to be that the nested IF has no code path for the Else case, which can be easily work-arounded by using 0 or FALSE(), modifying the formula thus works:

IF(IF($par_équipe.$A$6:$A$43="m";$par_équipe.$C$5:$C$42=B16;0);ROW($par_équipe.$C$5:$C$42)-ROW($par_équipe.$C$5)+1)

I’d say this is a bug, because an omitted Else path should return FALSE if hit, which it also does if

IF($par_équipe.$A$6:$A$43="m";$par_équipe.$C$5:$C$42=B16)

is entered standalone as array formula.

[erAck]

Bug submitted as tdf#116324.

[rangerjim]

THANK YOU! THANK YOU! THANK YOU!
This was driving me crazy and the only thing that was holding me back from going with LibreOffice.
It works great.