how to save Excel Mac 2008 to open it in earlier PC versions

**phstol** · 08-20-2009, 03:55 PM

Hello,
I have a Workbook written in Mac Excel 2008. My distribution must include users of older PC and Mac versions.
The workbook contains several PasteSpecial>Link operations from other Workbooks. It also contains nested IF and other nested functions. One nested IF is a conversion from letter score A-J to number score 1-10. I mention this one because after adding this one I could no longer save as .xls (error msg "will loose some of the functions... etc", and then Excel "quits unexpectedly".
My 2 PCs in the house have Excel 2007 and 2003 and will not open .xlsx.

Question.....
1. Should my 2 PCs be able to open .xlsx? The system with 2007 is supposedly updated automatically, as is the Mac.
2. What ver must I save in to allow widest back compatibility?

Thanks much for help

phstol

**martindwilson** · 08-20-2009, 06:03 PM

nested IF is a conversion from letter score A-J to number score 1-10

don't forget earlier versions only allow 7 nested if's
what function are you using? if the function is not available in earlier versions this is what you get,end of, no argument.
perhaps you should replace your nested if with some other function!
choose(),vlookup() or something that's compatible.
perhaps
=LOOKUP(A1,{"a","b","c","d","e","f","g","h","I","j"},{1,2,3,4,5,6,7,8,9,10})

**phstol** · 08-21-2009, 02:17 PM

Hi Martin,
very helpful, thanks.
I suspected there ws a limit to the number of IFs, at the time because of the way the save to .xls didn't work.
I have your suggested LOOKUP formula working, BUT ....
My letter grade is formatted {"A+", "A", "A-",...... "C-",D"},{10, 9,8,.....1} This format seems to trip up LOOKUP because it returns incorrect values as soon as + and - are introduced. eg, "A+"=8;"A"=9;"A-"=8. I did try ascending/descending etc.

Is there a fix for this?

I followed yr suggestion and tried VLOOKUP. This works but the table is inconvenient in my worksheet, so I'd rather use you way if I can get around the above incorrect values returned.

Thanks,

PHSTOL

**martindwilson** · 08-22-2009, 09:16 PM

=LOOKUP(A1,{"a","a-","a+","b","b-","b+","c","c-","c+","d"},{9,8,10,6,5,7,3,2,4,1})
its the first bit that has to be ascending

**mikerickson** · 08-22-2009, 10:25 PM

You may be interested in this converter from Microsoft.
http://support.microsoft.com/kb/953823
It allows earlier versions of Excel to open .xlsm files, but it does not add the new functions, etc.

**phstol** · 08-23-2009, 10:57 AM

Martin,
Thanks much for the LOOKUP clarification, it works...,
best,
phstol

**phstol** · 08-23-2009, 11:55 AM

Mike,
Thanks for the info, although I think it complicates my life.....

My need is to have the workbook openable by earlier PC versions of Excel.
I had thought that saving an .xlsx workbook in .xls would meet my need for earlier PC versions to open, enter data in some cells, then save.
But maybe not??
A question... so, what compatibility does saving in .xls provide?

My workbook does have some transgressions mentioned in the link you gave me, example it links to ranges in other workbooks, and I would like it to be password protected.
What are my options?
1. Rewrite from scratch in Excel 2007 or 2003 (I have these on PC), but what a chore!
2. Use the Msft Open File Format Converter to convert as far as allowed, then fix any areas that fail the conversion (how to do this?).
3. Use save to .xls if it gives sufficient back compatibility (seems like the least pain).

Help would be much appreciated

Best,
phstol

**mikerickson** · 08-23-2009, 04:03 PM

It would probably be best to rewrite it in Excel '97.

Since you are working with 2008, you don't have to worry about VBA compatibility issues.
(I hope that 2011 will re-introduce VBA to Mac Excel)

In general, writing in the your customer's oldest version is the safest way to go. (More critical with web design than Excel.)

**phstol** · 08-23-2009, 05:16 PM

Hi Mike,
Thanks again.
Rewrite in excel '97, wow, thts going back in time.... I found I have a copy of Office 2000, in addition to the 2003 installed.
My concern was that if I wrote it in say 2003, then more recent versions could not open it. But, it seems the opposite.

Would appreciate your help in better understanding this compatibility issue, so that I can get the widest compatibility. eg, was I so off track thinking that "saving as .xls" would give reasonable compatability??

Especially if I have to spend untold hrs on a rewrite

In that regard, any shortcuts you would suggest for doing the rewrite?

Best,
phstol