1

I just got to know that R or Python have different ways of defining the round function compared to Excel.

Statistical argument aside, my business users are primarily Excel users. And this has led to some confusion as the numbers generated by R/Python scripts can differ due to this rounding convention.

It seems to me that Excel always 'round up' when it needs to calculate numbers like 1.5, 2.5, 3.5, etc while R/Python will round to the nearest even number instead.

Potentially there're other assumptions I'm not aware of which differentiate both Excel and R/Python.

Is there any way to implement the exact round function from Excel in R?

In Excel

enter image description here

In R

enter image description here

MrFlick
  • 195,160
  • 17
  • 277
  • 295
Afiq Johari
  • 1,372
  • 1
  • 15
  • 28
  • It's trivial if we know exactly how Excel defines its round function. I'm asking here with assumptions that others have investigate further than me. Currently I know that for anything with .5, Excel will always round up. But maybe there're other technicalities that I'm not aware of. Yes round(1.5) is 2 in R, I don't see the point why you're saying that. It's explicit in the description above when I mentioned nearest even number. – Afiq Johari Dec 18 '19 at 13:38
  • You are probably aware already: https://stackoverflow.com/questions/9508518/why-are-these-numbers-not-equal – s_baldur Dec 18 '19 at 13:41
  • Round in Excel works the same as how I was taught maths many years ago (before excel or even Visicalc) .5 and above goes up and less than .5 goes down. – Solar Mike Dec 18 '19 at 13:42
  • @sindri_baldur added with screenshots – Afiq Johari Dec 18 '19 at 13:46
  • @SolarMike yes, my expectation with round is the same in Excel, but I just learn today that programming languages like R or Python follow different convention. I'm not debating which ones has a better definition here, I just want to know how exactly Excel implement it so that I can rewrite in R. I've found that Excel always round up, BUT is there any other things that I potentially miss? Thus this question. – Afiq Johari Dec 18 '19 at 13:49

1 Answers1

6

Based on the observed behaviour that round in Excel rounds away from zero for numbers ending in 0.5, it could be replicated in R with:

> x <- c(-1.5, -0.5, 0.5, 1.5, 2.5, 3.5)
> round(x+2*sign(x)*.Machine$double.eps)
# [1] -2 -1  1  2  3  4
Miff
  • 7,486
  • 20
  • 20