Currently Being ModeratedMay 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.
Currently Being ModeratedMay 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.
Currently Being ModeratedMay 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.
Currently Being ModeratedMay 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!
Currently Being ModeratedMay 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.
Currently Being ModeratedMay 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.
Currently Being ModeratedMay 10, 2013 4:52 PM (in response to Badunit)
I applied your suggested ISERROR transformation to examples such as:
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:
Curiously, earlier I found/suspect this does not work equivalently in Numbers and NeoOffice:
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!
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
Currently Being ModeratedMay 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 (?).