Excel into Numbers formula conversion! Help!

That is a perfectly valid, simple, and common formula structure and it should import without any problems, yet it does not. I tried it here and got the same result as you. I can't even import a formula that is as simple as =AT3_ProfitLoss!A1. In Excel if I change the worksheet name from AT3_ProfitLoss to AT_ProfitLoss (which changes it in the formula also), it imports perfectly fine. Numbers is choking on the "3" in the worksheet name but I see absolutely no reason why. I tried Numbers 14.4 and 13.1, the only two versions I have, and both do the same thing.

This has to be a bug, and a pretty stupid one at that.

Regardless, even if this had imported correct I recommend using the app that the professor is using (i.e., Excel). Numbers and Excel are too different. Future assignments are going to get more complicated and might use features or formulas from Excel that have no equivalent in Numbers.

There's almost zero chance of fixing this without seeing the data.

In either case, I don't think it's the $O$16 that's causing the issue - that just means a direct reference to cell O16 that remains fixed (i.e. doesn't increment as the formula is filled across the columns or down the rows).

Instead, I think it's the !

! is used by Excel to target a different spreadsheet document -in this case, AT#_ProfitLoss!$O$16 means 'Cell $O$16 in the document called AT3_ProfitLoss

This is not compatible with Numbers. Numbers does not support linking between documents, and therefore this reference is invalid.

Instead, in the Numbers world, you're expected to create multiple sheets, rather than discrete documents.. You can link between sheets in the same document, just not across documents.

Your simplest solutions are going to be importing each Excel document into separate Numbers sheets, then updating the references as necessary.

Or get a copy of Excel for Mac. If you have 10 documents to cross-link it could take some significant work effort to make the transfer... especially if it's not a one-off and needs to be done several times throughout the semester.

Or, do the really nice thing and do it once, then send the document back to your professor so he can use the modern method, rather than relying on cross-linking files (even Excel recognizes the downside of linking between files, and supports multiple sheets within the same document)