Excel integer function - incorrect result?

  • Thread starter Thread starter Tony Holdgate
  • Start date Start date
T

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
 
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
 
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
 
Sandy Mann said:
=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
Click to expand...



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
 
Back
Top