Skip navigation

Which functions and features in Numbers are NOT exported correctly to Excel?

974 Views 8 Replies Latest reply: May 10, 2013 4:58 PM by MyndSurfers RSS
MyndSurfers Calculating status...
Currently Being Moderated
May 7, 2013 5:55 PM

I wish to know EXACTLY which functions I use in a Numbers sheet WILL FAIL to export correctly to an Excel or Neoffice spreadsheet.

 

For instance, I created a Numbers spreadsheet using prolific use of the IFERROR function. However, when I EXPORT to XLS, all the cells that used the IFERROR function were replaced by the literal value of the cell. My IFERROR formulae were not EXPORTED as I had expected.

 

I note that some formulae were converted correctly in my EXPORTED spreadsheet: eg MAX, MIN, AVERAGE.

 

I understand that IFERROR was a function introduced recently to Excel, ie in the 2007 version. Consequently, I suspect that Numbers exports to a pre-2007 version of Excel. If so, which version?

 

I have found this list that shows which functions are compatable amongst various versions of Excel:

 

Excel Functions: Compatibility Reference (2010, 2007 & 2003). (n.d.). Retrieved from https://docs.google.com/spreadsheet/ccc?key=0AsHau4_IeCfwdG45c0VhcjBBUGNreVo2ZzN NRU1BT0E#gid=0

 

This list shows that, for instance, IFERROR exisits inb both Excel 2007 and Numbers-09. So why is IFERROR not exported?

 

Curmi, J. (n.d.). Summary of function in excel and numbers-09. Retrieved from http://curmi.com/blog/wp-content/uploads/2009/01/functions-in-excel-and-numbers- 09.pdf

 

Some examples of my use of ISERROR in Numbers are as follows:

=IFERROR(RANK(C47,$C47:$J47,1),"")

=IFERROR(MEDIAN(C51:C58),"")

=IFERROR($C32-C88+1,"")

 

I use the above function to  carry out the calculation ONLY IF  a cell is non-blank and contains a number.

More questions

What are the rules that Numbers uses to convert to Excel?

 

Is Numbers converting to the 2003 version of Excel? ... or an earlier version? ... which version?

 

Why desn't Numbers advise me there were conversion errors or simplifications made?

eg: "Cell A33 Function IFERROR was exported to the cell value for Excel"

 

Is there a preference that I can select which version of Excel that Numbers will export to?

 

In the meantime, I must abandon Numbers and use NeoOffice!

Numbers, OS X Mountain Lion (10.8.3)
  • Wayne Contello Level 6 Level 6 (12,660 points)

    If you REALLY need to share with other users who use MS Excel you should work in Excel.  Numbers is not the tool for you.  There are too many gotchas and not all of the are known.  I like Libre Office as an Office substitute but use Numbers exclusively when I do NOT intend to share.

     

    I do know a direct answer to your question.  I can tell if I were attempting to export a fancy spreadsheet to Excel from Numbers I would test many times along the way to attempt to identify formulas that are compable for both.

  • Badunit Level 6 Level 6 (10,765 points)

    I don't know which formulas get converted and which get replaced by the latest value. There are at least two functions that are available in both Excel and Numbers but Numbers won't export them to Excel. No idea why that is.

     

    Numbers does give you a statement upon export if formulas were replaced by their values. I don't believe it isspecific about which cells were affected but it does let you know something didn't export fully.

  • jaxjason Level 4 Level 4 (3,320 points)

    In your case i would stick with Office or an alternative that suopports direct manipulation and support for the format your required to work in.

     

    a partial answer:

    In your spreadsheet of functions in excel you noted above, mark anything in 2007 or higher as not able to export to excel from numbers.

     

    Numbers has tables that are based on 256 available columns and 65536 available rows, many tbls on a "worksheet". These tables are converted directly to individual sheets of their own in a 2003 XL format (which is that latest MS version that had the same row and column counts). For this reason anything that is not supported in 2003 version of XL will go to values, just like save as from 2007 will hold the values only.

     

    Going the other direction, array equations do not translate to Numbers, along with a few other items. You should always check by exporting a sample first.

     

     

    Jason

  • Badunit Level 6 Level 6 (10,765 points)

    While not as pretty or concise, you can use ISERROR instead of IFERROR. It will export.

     

    =IF(ISERROR(C32-C88+1),"", C32-C88+1)

  • jaxjason Level 4 Level 4 (3,320 points)

    first there is nothing truly "open" about any M$ format or application. "More common" would be more accurate

     

    Second, if your not sharing it everything you want to do for a simple attendance spreadsheet can easily be done on Numbers. If you are having an issue finding an equivalent formula or function, post it here and we can usually help find a workaround or alternative.

Actions

More Like This

  • Retrieving data ...

Bookmarked By (1)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.