Complex formulas do no longer work after I imported the Excel spreadsheet to Numbers

Question

Arne1972 Author

Level 1

0 points

Complex formulas do no longer work after I imported the Excel spreadsheet to Numbers

Hi There,

Perhaps I am not alone with this issue.

We´d like to provide our field sales team with our price calculation tool which is built on Excel.

It contains rather complex formula work as it looks up several values from different sheets.

It works perfectly within Excel (Windows PCs) but all formulas stop working on the iPad.

I´ve tested simple formula Excel sheets and they work on the iPad, too.

Will I have to invent the wheel again to make the more complex calculator on the iPad or is there another way making our standard price calculation sheet work on the iPad? It works with Excel now for more than 8 years (of course with some adjustments due to new products but that´s content and not formula invention work) so it is not an unevaluated sheet containing logical mistakes etc.

Help is very much appreciated.

I am looking forward to hearing from you soon.

Kind regards

Arne

iPad, iOS 7.0.2

Posted on Oct 23, 2013 10:53 PM

Reply

Answer 1

Best reply

Jerrold Green1

Level 7

30,211 points

Oct 24, 2013 7:05 AM in response to Arne1972

There are some Excel expressions that never worked on any version of Numbers. I don't think there's a simple answer to this problem except to use a device that runs Excel natively.

The more complex solution if you want to use an iOS device is to get in and sort out the compatibility issues one expression at a time.

Jerry

Reply

Answer 2

Oct 24, 2013 7:42 AM in response to Arne1972

If you used array functions in excel those will not work in Numbers

Reply

Answer 3

Arne1972 Author

Level 1

0 points

Oct 24, 2013 10:51 PM in response to Arne1972

Thanks Jerold and Wayne,

Ouch! Doesn´t sound too positive:-(

Is there a list or table available that provides information about what works and what not?

Best would be a replace list "not supported -> replace by ..." to make it work.

If it is known that some functions are not available I do not understand why Apple isn´t coming up with a solution to it.

It cannot be rocket science to make them available. Not for a company like Apple which has and is able to revolutionize the market of mobile devices.

Especially, as Apple claims to provide productivity to business users, too, trying to replace Windows machines enterprises.

Have a great day.

Kind regards

Arne

Reply

Answer 4

Oct 25, 2013 11:34 AM in response to Arne1972

Hello,

In fact it is not only Excel formulas that no longer work, previous Number version's are also no longer supported, such as SUMIFS.

And I also get the very user-unfriendly " Unsupported conditional formal were removed from table cells" for tables which were created and used for several years with Numbers '09.

This upgrade seems to be a real downgrade.

Reply

Answer 5

SGIII

Level 8

35,336 points

Oct 25, 2013 12:14 PM in response to Alain Joaris

In fact it is not only Excel formulas that no longer work, previous Number version's are also no longer supported, such as SUMIFS.

Hi Alain,

I understand some new functions have been added. SUMIFS works fine on my machine. (You had me worried there for a minute).

Maybe try reinputing some of the formulas that are giving you trouble.

SG

Reply

Answer 6

Oct 25, 2013 12:20 PM in response to Alain Joaris

Alain,

Could you post some of the formulas and formats that aren't working?

Jerry

Reply

Answer 7

Nov 3, 2013 12:47 AM in response to Jerrold Green1

Jerrold,

Sorry was off for some time.

This is one of the formulas that does no longer work:

SUMIFS(BNP Paribas::$B$2:$B$267,BNP Paribas::$A$2:$A$267,B4,BNP Paribas::$A$2:$A$267,C4,BNP Paribas::$C$2:$C$267,"=Amexco")

In B4 and C4 I setting a period condition:

>=10/1/2013

<11/1/2013

Column B in the "BNP Paribas" sheet contains the amount of the operation the formula is supposed to search, A is the first search criteria concerned with the date range given above, whereas C contains the label I seeking a match for.

Date	Montant	Transaction

Oct 3, 2013	-€ 2'519.68	Pristina
Oct 1, 2013	-€ 1'840.85	Amexco
Oct 1, 2013	€ 30.02	Rmb frais médicaux
Sep 28, 2013	-€ 110.16	Bruxelles
Sep 29, 2013	-€ 600.00	Pristina
Sep 30, 2013	€ 170.10	Rmb frais médicaux
Sep 30, 2013	€ 196.83	Rmb frais médicaux

As far as I can figure out, the syntax is correct. Moreover, this used to work with a problem before the upgrade.

Let me know if there is anything I miss.

Best,

Alain

Reply

Answer 8

Nov 3, 2013 3:39 AM in response to Alain Joaris

I meant worked without a problem.

Reply

Answer 9

SGIII

Level 8

35,336 points

Nov 3, 2013 3:49 AM in response to Alain Joaris

Hi Alain,

Your formula works on my machine:

However, I see your thousands separator is ' rather than , . This suggests that your region settings are different from mine. The problem may be with those settings. Non-English region settings seem to be an issue when dates are involved.

You could try temporarily setting your region settings to match mine (see below) and reinputing the dates in the spreadsheet. If that clears up the problem then you could go to Numbers>Provide Numbers Feedback in your menu and file a bug report mentioning the region settings.

If it does turn out to be a region settings issue, it would be great if you could report that here too.

SG

Reply

Answer 10

Nov 3, 2013 10:31 AM in response to SGIII

Dear SGIII,

Thanks for the analysis and the advice.

Changing the date settings does indeed address the problem, although changing the settings only was not enough. I also had to change the dates to: CONCATENATE(">=",DAY($A4),"/",MONTH($A4),"/",YEAR($A4)) instead of (MONTH/DAY/YEAR) in the reference for the SUMIFS(BNP Paribas::$B$2:$B$267,BNP Paribas::$A$2:$A$267,B5,BNP Paribas::$A$2:$A$267,C5) formulas. But the data remains in the MDY format.

I followed your advice and reported the bug using the feedback.

Best

Alain

Reply