Hi Guys,

I have come across the following problem/error=IF((Y25+AG25)> I have this at the start of a fairly long formula. However, I have noticed through an error occurring and then, after investigating using 'formula evaluation' that the following occurs

Y25 = 6.1 AG25 = 0.1 This should calculate to a value of 6.2

However, when I evaluate the formula, the calculated value is 6.19999999999999

This has a detrimental effect within my worksheet. I have sourced an answer to resolve this, but can any one enlighten me as to why this has/might happen

Where are the numbers in cell Y25 (6.1) and AG25 (0.1) coming from?

I would guess that they are the result of a formula.

This is where your problem probably begins.MIKE

Binary math strikes again. Basically, base-2 is fine with whole numbers, but trips over itself pretty badly once you introduce fractions. Most partial numbers simply cannot be represented in base-2. Base-10 has the same issue, but with a much smaller subset of numbers. 1/3 or π, for instance. This is why money should only be done with whole numbers; a count of cents instead of dollars. a Microsoft suggests either using ROUND(), or setting precision as displayed.

Hi Mike,

Thanks for responding. There is a formula involved to produce the value 0.1, or 0.3, or 0.5. However, on the specific worksheet there are other cells that are adding the 0.1 and these are coming out as whole , ie .2, or .7

Just this one cell value is affected.

As the other reply suggests, my solution is to use the ROUND function

Quite a bit of work involved to adjust the worksheet but, hey at least I have a vague idea as to what has caused the issue

Razor2.3

Thanks for your response. Not as technically minded as your good self but I think I understand your reply.

As I mentioned, I have already come up with the solution (ROUND), but many thanks for confirming this is the way to go

There could be a couple of things going on. First, it could be as complicated as the precision with which Excel stores number internally. See here:

http://en.wikipedia.org/wiki/Numeri...

Second, it could as simple as the way Excel displays numbers based on the format of the cell vs. how Excel stores the number internally.

If the result of formula, or even a hardcoded number, is 6.09999999999999, but the cell is formatted to only show 1 decimal place, it will be displayed as 6.1 but actually stored as 6.09999999999999. Add 0.1 to that and you'll see 6.2 in the cell, but internally Excel sees it as 6.19999999999999.

Depending on the formula, the Formula Evaluator will either show you the exact number as Excel is storing it or just the result of the formula.

=SUM(A1:B1) will simply show the result in the Formula Evaluator (6.2) assuming a single decimal point format.

=SUM(A1+B1) will evaluate A1, then B1, (showing you the internal value of each) then the SUM (showing the exact value), then show the "displayed value".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think Jony Ive could make a big impact on Airbnb?

Discuss in The Lounge

Poll History