=round - Excel Rounding Problem

respree · Apr 22, 2003

Does anyone know if Excel has a rounding problem?

I am taking a number, dividing it by 1000 and rounding it to 1 decimal point.

i.e. Cell A1 - 41349
Cell A2 - =round(a1/1000),1)

formula "appears to" correctly produces 41.4 as the answer

Cell B1 - 49199
Cell B2 - =round(B1/1000),1)

formula "appears to" correctly produces 49.2 as the answer

However, A2 minus B2 (formula in C2) doesn't produce the expected (7.8)

Its very close, but the actual answer when displayed in 14 decimal positions is (-7.80000000000001) - note the "1" in the 14th position.

This may seem like trivial rounding difference, but the reason I'm asking is because I'm trying to validate the answer by matching it against Cell C2, which has the formula: if(c2=-7.8,"true","false")

The answer 'should be' true, but instead its saying its false because of the 1 in the 14th position of the answer.

Is this a design flaw with Excel or does anybody know a way around this.

Thanks very much for your input.

PaddyD · Apr 22, 2003

Hi.

see:

http://www.mrexcel.com/board2/viewtopic.php?p=123249&highlight=precision#123249

paddy

Barry Katcher · Apr 22, 2003

Just use the same ROUND to 1 decimal place on your A1-B1 formula. And do check the link Paddy gave you; if you can understand 10% of it, yer a better man than me, McGhee.

New Book.xls

A

B

C

D

1

41349

49199

2

41.3

49.2

-7.900000000000010000000000

false

3

-7.900000000000000000000000

true

Sheet2

tusharm · Apr 23, 2003

While the references you got about floating point numbers in digital computers are relevant and worth understanding...

respree said:
Does anyone know if Excel has a rounding problem?

I am taking a number, dividing it by 1000 and rounding it to 1 decimal point.

i.e. Cell A1 - 41349
Cell A2 - =round(a1/1000),1)

formula "appears to" correctly produces 41.4 as the answer

The correct answer is 41.3, which is what I get with XL2002SP1.

respree · Apr 23, 2003

Hi tusharm: Yes, I agree. I'm afraid I made a typo on my question.

I figured a workaround the computational flaw, so no need for further responses.

instead of =if((a2-b2)=c2,"true","false")

I said:

=if((a2-b2)-c2<.001,"true","false") (i.e. a virtual match)

assuming the computational flaw is 'very' insignificant.

Barry Katcher · Apr 23, 2003

Respree, no need for a work-around. See my suggestion above.

tusharm · Apr 23, 2003

Barry Katcher said:
Respree, no need for a work-around. See my suggestion above.

which is a workaround. Isn't it? {g}

As far as the 'workaround' of testing that a result is less than a threshold, it is the *standard* way of working with floating point numbers. Though, typically, the threshold is of the magnitude of 1e-6 or 1e-8 or even smaller.

Seti · Apr 23, 2003

To make your fix more universal, you might want to consider taking the absolute value of the result rather than the actual result. It is possible that the result might be -1E-14 rather than 1E-14.

Barry Katcher · Apr 23, 2003

Tushar, I thought of that a half-second after I clicked the "Submit" button, but said "Oh, well, this will just give someone on the board an opportunity to embarrass me."

kjacw · Dec 19, 2011

ALERT!!!

Can anyone help with my gradesheet workbook. I am having serious problems with rounding. Sometimes the grade averages are not rounding when they should up to the nearest whole number. Many times I see this in my grade workbook that is causing many problems. I cannot use the 'Set precision as displayed' because it affects all other grades with inaccurate grades. It seems to have problems when it needs to round numbers like 85.5, 78.5, 91.5, etc. Please help. These are simple grades and not big numbers.

Has anyone seen this?

=round - Excel Rounding Problem

respree

Active Member

Excel Facts

PaddyD

MrExcel MVP

Barry Katcher

Well-known Member

tusharm

MrExcel MVP

respree

Active Member

Barry Katcher

Well-known Member

tusharm

MrExcel MVP

Seti

Well-known Member

Barry Katcher

Well-known Member

kjacw

Board Regular

Similar threads

Forum statistics

Share this page

=round - Excel Rounding Problem

Active Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

Active Member

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock