Excel integer function - incorrect result?

[Tony Holdgate]

I've googled extensively for this one but have not come up with an answer,
despite finding many references.
The following formula in excel
=INT((20.7-19.1)/0.4) gives the answer 3. The correct answer mathematically
is 4.

If you remove the integer function and extend the decimal places it
eventually changes to
3.99999999999999 which explains why the integer function rounds it down.
However the school I went to says that 20.7- 19.1 = 1.6 1.6 /.4 = 4. Thats
4.0000000000000000 not 3.999999999999

Can anyone explain firstly what is happening and then the best approach to
fix it?

Thanks

Tony

 

[Tony Holdgate]

Thanks John,

I was using Excel to illustrate the same problem I was having in Access.
Yes- wrong forum but to fix it in access I used a function (not available in
Excel) to turn the number from a double data type to a single data type.
Trouble is I didn't understand why this fixed it.

Is there a way of doing this in Excel?

Tony

 

[John]

Tony,

Sorry, I don't know too much about how numbers are stored in Access.

I'll take a guess and suggest that numbers in Access may be stored with an
implied decimal point.
That is to say, 9.99 is stored as 999 with the software keeping track of
where the decimal point is during calculations.
Therefore any calcuations is on integers which gives a more accurate result,
especially if the answer is also a whole number or integer.

This was a way to prevent loss of accuracy in such languages as COBOL used
for business use.

Someone who knows more about MS Access may be able to confirm or destroy
this argument.

One way to make your formula work is to add on an itsy-bit, e.g.

=INT((20.7-19.1)/0.4+0.000000000000001)

This is not such a great solution because the itsy-bit will stop working for
larger numbered results.

regards,

John

 

[Harlan Grove]

...
...

INT((10*20.7-10*19.1)/10*0.4)

No, make that

INT((10*20.7-10*19.1)/(10*0.4))

 

[Sandy Mann]

=INT((A1*10^(IF(ISERROR(FIND(".",A1)),0,LEN(A1)-FIND(".",A1)))-B1*10^(IF(ISE
RROR(FIND(".",B1)),0,LEN(B1)-FIND(".",B1))))/(C1*10^(IF(ISERROR(FIND(".",C1)
),0,LEN(C1)-FIND(".",C1)))))

Is there a more elegant way of doing it?

Regards

Sandy

By the mind concentrating powers of the *Send* button I see that the logic
of that formula is wrong, please just ignore it.

My apologies

Sandy

 

[Tony Holdgate]

Thanks all. Great group and appreciate your help

Tony