Wrong Data Type

Question

This is super basic!

After copy and pasting data from a Mac Numbers file into excel, I need to simply subtract one cell from another. Both have monetary numbers in them. 

I am writing the formula  =SUM(G3-E3)

I am getting the Wrong Data Type error.

Please advise and thank you!

Answer 1

PS....

Re: Both have monetary numbers in them.

Oh, the problem might be:  the currency symbol or some part of the __form__ of the currency is not recognized as currency in Excel.

One cause of that problem is differences in region/language settings on the two computers (Mac and PC, right?).

For more help, we will need to see the problem, in one form or another.

Again, upload an example Excel file per the instructions in my previous response.

It might also be help for you to show us how currency is normally formatted on your computer.

Show us the region/language settings.  Or simply enter a number into a cell, format it as Currency, and show us what it looks like.

Answer 2

Please clarify what you mean by "the Wrong Date Type error".  Do you mean the #VALUE error?  Or do you mean a green triangle in the upper-corner of the cell?

If you are referring to a green triangle, that is just a warning, not an error. And yes, it is annoying.

I avoid them by disabling "background error checking", a misnomer.  Click File > Options> Formulas, and uncheckmark "Enable background error checking".  Also click Reset Ignored Errors to clear existing warnings.

---

If you see a #VALUE error....

Yes, you can write =G3-E3 instead of =SUM(G3-E3).  And you should.

But I would be surprised if that remedies the problem.

If G3 and/or E3 is text that Excel can interpret as a number, G3-E3 will work just fine in both formulas.

The #VALUE error means that G3 and/or E3 is text that Excel __cannot__ interpret as a number.

Usually, Excel tolerates leading and trailing regular spaces (ASCII 32).  So that should __not__ cause a #VALUE error in this context.

But with copy-and-paste, the problem is often with non-breaking spaces (ASCII 160).  Much less often, there are other extraneious characters, notably tab characters (ASCII 9).

For quick solution, try entering formulas of the form =CLEAN(TRIM(SUBSTITUTE(G3,CHAR(160),""))) into a cell, then copy the cell and paste-value into G3.  Do the same with E3.

I expect that will remedy the immediate problem.

But the long-term solution is to avoid the problem altogether.  To that end, we would might need more information.  LMK.

---

If that does not remedy the problem, it is best for us to see the Excel file.

Upload an example Excel file (redacted) that demonstrates the problem to a file-sharing website.  I like box.net/files; others like dropbox.com.  You might like onedrive.live.com because it shares the same login as this forum.

In any case, post the public download URL in a response.  First, test the download URL, being careful to close all windows that might share the same login.

Answer 3

Hi

The formula should be simply    =G3-E3

Regards

Jeovany