Excel to R, value changes when importing with readxl

[vabm]

I am quite new to R. I need to work with a number of lists that I have in excel, but when importing them with readxl the numbers seem to be changing.

I have a spreadsheet (example here), and when I subtract the sum of the tab "target_intputs" to the sum of the tab "target_outputs" in excel, the total is 0 (as it should be, see "comparison" tab). However, after importing it to R, if I compare them using print(sum(target_inputs) - sum(target_outputs)), it gives me 6.007031e-08 as the total.

Here is a sample of the code:

library(readxl)
target_inputs <- read_excel("C:\\A Matrix - Table 5 2019-20.xlsx", sheet="target_inputs")
target_outputs <- read_excel("C:\\A Matrix - Table 5 2019-20.xlsx", sheet="target_outputs")

target_inputs <- as.numeric(target_inputs)
target_outputs <- as.numeric(target_outputs)

print(sum(target_inputs) - sum(target_outputs))

I assumed there could be a problem with the decimal points, so I tried the round() function and options(digits=20), etc., but I cannot get rid of the problem. This seems to be happening only in R, as when I export it back again using the code below, the problem seems to disappear.

library(writexl)
write_xlsx(target_inputs, "C:\\target_inputs.xlsx")
write_xlsx(target_outputs, "C:\\target_outputs.xlsx")

This happens with some datasets, others don't give me the problem.
Any help would be much appreciated, thanks!

[Dissipation123]

Hello,

This is actually a general problem with programming languages (not just an R problem). Binary has some issues representing float numbers. The history lesson on that is here: r - Why are these numbers not equal? - Stack Overflow

To skip the history lesson however, you can use the floor() or trunc function to get the nearest rounded down integer or no decimal points, respectively. You can wrap this around a function or ifelse() statement if not all of your differences will be zero.

### Testing floats
num =2.21e-09
print ("original number")
print (num)
 
print ("modified number")
format(num, scientific = FALSE)
#prints number in non-scientific format

floor(num)
#Prints 0

trunc(num)
#Prints 0

Hope this helps!

[vabm]

Thanks @Dissipation123, this solves the issue. I can't believe this is the first time I come across this.