Excel think a number is million and not just 1.

[stockman]

I have imported a large dataset into Excel using "from text" CSV file

So I am calculating the change in percentage between 2 numbers. Everthing went fine until it came to these kind of difference between 2 numbers:

Example:

Number:
CELL:A1: 1.002.105
Cell: A2: 0.952894

So I think excel believes that CELL A1 is 1 million!

But I want Excel to understand that this is just 1.002105

How can I fix this?

[AliGW]

You need to get rid of the rogue decimal point.

Can you provide a workbook containing some sample data that we can test for you?

[stockman]

Sure.

For example the problem occur in Cell: C1673

[AliGW]

OK - you will know why it's happening, I presume? It's because of the way your locale is reading the separators, I think.

Here's a workaround:

=(IF(B1673>1,B1673/1000000,B1673)-IF(B1672>1,B1672/1000000,B1672))/IF(B1672>1,B1672/1000000,B1672)

You will need to replace commas with sem-colons for your locale.

[stockman]

Wow. Thanks!

So if I understand correctly: I am located in Europe so you are saying that I should go into option-advanced of Excel and change to semi colons? Its now NOT checked on "use system seperators"

Your workourand seems to work nicely. When I opened your Excel file commas had automatically been replaced with ;

And is it possible to format the "close" data so that it's correct? I want to make a graph of that data and now the highest number is understood by excel as 9.6 million. instead of 9.6

[AliGW]

No, you should not change your settings. If you are given a formula here with commas (which are used as separators in English-speaking locales), you should change them to commas. I added a workbook because I knew that it would render correctly for your locale.

You might find this handy: https://en.excel-translator.de/translator/

Glad to have helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[stockman]

I understand. Thanks.
And I have this question also: Is it possible to format the "close" data so that it's correct? I want to make a graph of that data and now the highest number is understood by excel as 9.6 million. instead of 9.6

[AliGW]

If you convert it in the same way that I have done to a new column, then use that as the chart source, then yes.

[stockman]

Thanks, but the code is complicated for me. I don't know how to do what you suggest.

So it's not about calculating the percentage, but maybe make a new column with correct formatting for the "close" data

[stockman]

I think I managed myself. At least the graph looks good now:

solution: =(IF(E2>1;E2/1000000;E2))

[AliGW]

That's it - well done!

Glad to have helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.