Maintaining formulas when saving as .xls

[johnlovesbeer]

Hi all,

I've created a decent sized spreadsheet which involves sumifs where the source is on one sheet and the formulas showing the calculations are on a second sheet. It all works beautifully in Excel 2007 as both .xls and .xlsx files (I've saved it as both) but unfortunately when I open it on a PC which runs Excel 2003 the formulas no longer calculate. This is a problem because the people who will be using the spreadsheet all run 2003 and upgrading is not an option. I've opened the sheet in 2003 and cleared the target cells which employ the formulas and put in the same formulas fresh, all to no avail. Is this an export/import issue from 2007 to 2003 or should the formulas read differently for 2003?

I figure this is still a 2007 question and relevent to this forum as Excel 2007 is what I used to create the sheet.

Any help from anyone will be greatly appreciated!

Cheers,

JLB.

[DonkeyOte]

SUMIFS like COUNTIFS, AVERAGEIF, AVERAGEIFS etc are new to XL2007 and thus not backwards compatible.

SUMIFS is generally replaced with SUMPRODUCT for backward compatibility - unfortunately SUMPRODUCT is less efficient (it is processed like an Array).

For more info. on SUMPRODUCT see the link in my sig. to Bob Phillips' white paper on the Function

If you have functions dependent upon the Analysis ToolPak like EDATE, EOMONTH, NETWORKDAYS, WORKDAY, MROUND, RANDBETWEEN etc then you should note that pre XL2007 the Analysis ToolPak must be activated on each client machine else #NAME? errors will result
(in 2007 these functions are available by default).

[johnlovesbeer]

Thanks DonkeyOte,

Nice Christmas hat!

I thought that something like this would be the case. I'll give sumproduct a try and see how I go.

Merry Christmas,

JLB.