Simple minus formula not working

[kakhuis]

Hi everyone. Hope your are well. I need some help with a excel formula. Just a smple cell - cell and answer displayed in another cell. So =A1-A2 answer in A3. My problem is the number in the two cells ( A1 and A 2 ) are as follow 3,037.660 For some reason if the number has a" , "and a " . " in the the formula give me a error #VALUE!. Any sugestions ?

Thank you
Bet the answer will be something obvious !!!

[alansidman]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[kvsrinivasamurthy]

Cell format may be text, to be changed to number format.

[Debraj Roy]

Instead of A1 - a2.. try to use..

Please Login or Register  to view this content.

[AlKey]

There is nothing wrong with the number 3,037.660. I suspect that it was entered as a text. But it would be helpful if you could attach a spreadsheet with the data in it so we could find a solution to your problem.

[kakhuis]

Hi Thanks for the replies. Here is the cheet.
Line 1 is the error line. I use the = Value(A1)-Value(A2) but does not work
Line 2 is was I would like
Line 4 and 5 just another example. Here use the formula I am using now.

Thanks
PS: I do not enter the number manually. I high light them on a webpage and drag them on to a cell.

[AlKey]

Just like I said. The numbers were entered as text. You can use Debraj Roy advice to get the right answer.

[Debraj Roy]

Try this..

Please Login or Register  to view this content.

[kakhuis]

Hi. Tried it, but no luck.

[AlKey]

=VALUE(D10-C10) this one worked fine for me.

[kakhuis]

Hi Alkey. Tried it, but does not work for me. Did you download my cheet and tried it on there ?

[Rambo4711]

If all data in column C and D is in the textformat - Use Search and replace

First replace the character "," with nothing
Then replace the character "." with ","

[AlKey]

Yes, I did and it worked fine.

[kakhuis]

ALKey. Would min change it on the cheet and then uploading it ?

Thanks

[AlKey]

Here is the file.

[AlKey]

I think i sent a wrong file. Sorry.

[kakhuis]

ALKey I think you uploaded the wring file. In the cheet there is info on Code,Name, Age and so on

[AlKey]

Look at the second file Example-New.xlsx

[Pepe Le Mokko]

TWIMC
=a1-a2 is the same as value(a1)-value(a2) The use of arithmetic operators automatically coerces text to numbers. The use of the VALUE function is therefore redundant

In this case it's only a regional settings problem

[kakhuis]

AlKey Did you enter the number manually ?

[AlKey]

No. I just used the formula that I have suggested earlier.

[kakhuis]

Using the search and replace:

15,711.80 is the number. I find it and replace the "," with nothing. So the new number is 15711.80 Even now it still give the value error. AGRRRRRRRR

[kakhuis]

AlKey . I opened your file and copied your formulaa to my sheet and still the same error

[kakhuis]

As soon as I retipe the number, so basically I am manually entering them then it works

[kakhuis]

Basically if I highlight a number like this: 1.78656 and then drag it on to a cell it appears like this: 178.656. Notice the . has move ! Anyway it work with the formula. But when I drag and drop a number the contains a , and a , then the formula does not work unless I re enter the number manually. Even if I use ctrl F and replace/take away one of the two symbols, still the same result.

[AlKey]

I am not sure what else to offer. But try this method to convert text to numbers.

In any blank cell, type the value 1.
Make sure the cell you typed 1 in is formatted as a number.
Select the cell in which you typed 1, and then right click and choose Copy.
Select the cells with the values that you want to convert to numbers.
Right click and choose Paste Special.
Under Operation, click Multiply and then click OK.

[kakhuis]

No did not work. Thanks for the help anyway.

[JMax]

Multiply your columns by 1 and convert to a number, should work fine.

[kakhuis]

Hi JMax would you mind giving a few steps

[Debraj Roy]

Italy.. Eu Country .. hhhhmmmmm...
Can you please open your excel..

Open VBA (Alt + F11)
Open Immediate Winodw.. Press Ctrl +G
Write below line.. just copy & paste..

Please Login or Register  to view this content.

and give us the output..

1>
.
,



or

2>

,
.


PS: Office verison "mantova".. then you location may be.. MACExcel..

[kakhuis]

Hi Debraj Roy.

I get this:
.
,

[kakhuis]

Hi debraj Roy

I get this:
? application.ThousandsSeparator & chr(10) & Application.DecimalSeparator
.
,

[JMax]

Hi JMax would you mind giving a few steps

Not sure if I am looking at this too simply but it worked when I downloaded your first attachment.

Enter a "1" in an empty cell.
Copy
Paste Special (Multiply Value) to your two columns

Converts your text to #'s....

[kakhuis]

Hi have this

C13 = 17.507.90 $ D13 = 17.503.20 then in F13 = =D13-C13 That comes out as #VALUE!

I then enter 1 in a empty cell. Copy it. Select C13 and D13, right click and select special past. Then I click Multiply and then done.
But nothing happens.
What am I doing wrong ?