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

Question

Level 1

12 points

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

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-OTHER, OS X Mountain Lion (10.8.3)

Posted on May 7, 2013 5:55 PM

Reply

Answer 1

Best reply

Wayne Contello

Level 7

26,529 points

May 7, 2013 6:32 PM in response to MyndSurfers

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.

Reply

Answer 2

Badunit

Level 7

25,784 points

May 8, 2013 5:20 AM in response to MyndSurfers

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.

Reply

Answer 3

jaxjason

Level 5

5,047 points

May 8, 2013 6:25 AM in response to MyndSurfers

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

Reply

Answer 4

MyndSurfers Author

Level 1

12 points

May 9, 2013 3:48 PM in response to Badunit

Ooops! I meant to say I "liked" Badunit's answer, rather than stating it solved my problem.

The reason I started out using Numbers was (In response to Wayne Costello):

I want to be able to use the spreadsheet on my iPad and my Mac. eg: I created an Atendnce-Gradebook spreadsheet on my iMac, then in my teaching class, I collect Attendance on my iPad. Then I analyse the data on my iMac.
The spreadsheet I created that gave rise to my Discussion Board question was used to solve an issue I had. However, my spreadsheet solution had generic applicability for other people's issues. Hence my desire to share the spreadsheet in an "open" format such as Excel or NeoOffice .odt.
I have got to really enjoy the ease of setting up spreadsheets using the Numbers user interface, in contrast to NeoOffice, and Excel.

Oh well ... thanks all, and back to the drawing board!

Reply

Answer 5

Badunit

Level 7

25,784 points

May 10, 2013 5:06 AM in response to MyndSurfers

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

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

Reply

Answer 6

jaxjason

Level 5

5,047 points

May 10, 2013 6:06 AM in response to MyndSurfers

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.

Reply

Answer 7

MyndSurfers Author

Level 1

12 points

May 10, 2013 4:52 PM in response to Badunit

Thanks,

I applied your suggested ISERROR transformation to examples such as:

=IFERROR(RANK(C47,$C47:$J47,1),"")
=IFERROR(MEDIAN(C51:C58),"")
=IFERROR($C32-C88+1,"")

However, a caution for young players....

Certainly, the ISERROR function exports to .xls. However, it behaves differently in NeoOffice.

Items 1 and 2 function identically in the case of say, ranking a row in which some cells are blanks.

Item 3, when translated using ISERROR to =IF(ISERROR(C32-C88+1),"", C32-C88+1), results in "" (ie error is raised) in Numbers but yields figure 0 in NeoOffice when there is a blank in either of the cells.

I recall from programming school in the mid-1970s that using GOTO statements and On Error branch statements were not admissible as routine programming steps. In fact, we received an automatic fail grade if we used a GOTO or GOTO like construction. So, I have found a more programmatically elegant way to solve my problem: eg:

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

Curiously, earlier I found/suspect this does not work equivalently in Numbers and NeoOffice:

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

When you try using ISBLANK you always get an intuitively unexpected result because ISBLANK ONLY gives TRUE when there is absolutely nothing in a cell. A Formula reference in the cell, even if the function result yields "", gives FALSE... because, according to the definition of ISBLANK the cell contains a formula.

As I tell my students, ALWAYS lookup the HELP on any function you use. ALWAYS test the function to see how it performs!.... and lesson 3: Always test your spreadsheet on different systems.... then hope!

FYI:

Here's a link to my Spreadsheet (Neoffice .xls) and a video explaining its use.

Team Contribution: Introduction (Prototype). (2013). Retrieved from http://www.youtube.com/watch?v=bj87FGLoplk&feature=youtube_gdata_player

I don't guaranteee this link for ever! ... and I continue to "tweak" it

https://www.dropbox.com/s/1jz7gzksejnbqo1/Team%20Contribution.xls

Reply

Answer 8

MyndSurfers Author

Level 1

12 points

May 10, 2013 4:58 PM in response to jaxjason

Thanks for the offer of help.

My Numbers spreadsheet began as a simple Attendance register. Then I added grades. Then I added forecast grades based on previously-determined equations utilising course attendance and early grades. Then I added graphs to show my students how attendance seemed to be associated with their grades... and later good grades associated with early good grades .... (They liked that evidence instead of perennial cries from their lecturers...). My attendance spreadsheet has grown 'kinda topsy' as I began to get heavy with students who failed to attend class - sending out 'please explain' emails and other acts of chidence (?).

Reply