Excel - adding numbers before decimal place

Question

Hi all,

I've a strange one here. If I copy from notepad the following numbers into Excel in column A, then perform a formula (=A2-A3) which subtracts the number below from the number above I get all these crazy numbers before the decimal place.....

The formatting is the same in every cell.

What gives?

thanks

Dave

Answer 1

OK. Understood. This is floating point arithmetic at work. To handle this in your CF or anywhere else, you will need to use ROUND formula.

Read here, you will understand this - https://www.microsoft.com/en-us/microsoft-365/b...

Answer 2

Hello Dave

I am V. Arya, Independent Advisor, to work with you on this issue. Investigation would be easier and comprehension for you would also be easier if we can work on your workbook. May I request you to post the file with dummy data (no production / real data) to Onedrive or any other cloud sharing site and post the link here? If you need help about how to do - https://support.office.com/en-us/article/share-...

Answer 3

Hi V. Arya,

here's the link to the Excel document...

https://1drv.ms/x/s!Avxu4mJ4y9kRiBHhZkXPwfbL0VQj?e=DgogzS

rgds

Dave

Answer 4

Your answers are right as per your formula. Looks like, you wanted following formula to subtract the number below from the number above

=A1-A3

For Row2, A1 is above and A3 is below

In your files, this will be =B1-B3

If you are looking to limit number of decimal places, select your columns - Right click - Format cells - Number - Make decimal places to 2 or to whatever precision you wanted.

Now, let me know if above solves the issue. If not, I would need more from you in terms of problem being faced.

Answer 5

Hi V. Arya,

thanks for responding so quickly.

No it doesn't solve my problem. Why are the numbers before the decimal place being added? I need these gone and at the very least understand why it's happening.

I came across the error as I'm trying to do conditional formatting, and I can't because of all these extra numbers.

thanks

Dave