I’m getting the above in a cell when I open an Excel spreadsheet in LibreOffice. Since Excel and LibreOffice both support the IFERROR function, I’m unclear as to why the _xlfn is there. Any clarifications would be appreciated.
Please upload the problematic file.
In the .xlsx archive:
The application in which the file was created is not specified (\docProps\app.xml).
Tags for cell D4 (\xl\worksheets\sheet1.xml ):
<c r="D4" s="15">
<f>_xlfn.IFERROR($B4/C4,0)</f>
<v>1.6</v>
</c>
Should be (as Excel would save):
<c r="D4" s="15">
<f>IFERROR($B4/C4,0)</f>
<v>1.6</v>
</c>
Similarly for other cells.
The comments are exactly the same.
In which application is the file saved?
This is very interesting. The 1st version I sent was opened in the Numbers app on my iMac then saved back as an Excel file. The 2nd version is the one I was sent but duplicated and the name changed. I’ve downloaded another copy that I’ve not altered in any way and I’ve attached it. I’m curious to see if it is exactly the same too. In the mean time, the sender has a Mac and I’m now wondering if he created it in Numbers then saved it as an Excel file presumably in order to have it readable by a larger group of people (it’s a spreadsheet that was produced as part of a course on photo printing). I’ll check with the author to see if that’s what they did. The question now, I suppose, is whether it opens OK in Excel itself. Are you able to test that?
Thanks very much for your assistance with this issue!!!
Cost of Printing Calculator.xlsx (8.7 KB)
About _xlfn In Excel see here.
In Excel, your file opens “correctly”.
Excel probably treats the file as having been originally created in a “modern” version of Excel, and then saved in an “old” (<2007) version of Excel that does not have the IFERROR function.
I don’t think you should expect this behavior from LibreOffice Calc.
This file is opened by my LibO 24.8.1.2 under Win 10 permitting recalculation without any problems.
However, the cells containing a formulae trying to use IFERROR() show errors.
After removing the prefix _xlfn. from those formulae (F&R / Ctrl+H) they seem to work.
(I can’t see anything useful done.)
Thanks! Do all 3 files I sent open in Excel or just the last one?
@Lupp. Thanks. Yes, I’ve already done that and everything works OK. The thing is this spreadsheet is part of a photo printing course and I suspect that not everyone will have Excel and some may not be familiar with spreadsheets at all. If they have a Mac then they’re OK but if not then LibreOffice is a good alternative (ie free) but if they’re not familiar with spreadsheets they won’t be able to go into one and make those sorts of modifications.
All 3 files open “normally” in Excel. They use the same construct.
OK. That seems to mean then that the original Excel file was actually created in Apple Numbers then saved as an Excel file. In doing this Numbers must be saving as a pre 2007 Excel file when IFERROR didn’t exist. I’ll check with the original author and let you know.
Thanks again.