Excel Rounding

Copper Contributor

In excel, I have to multiply hours by a bill rate.  I need the rounding to stay as if if the third digit is 5 or less, I need the rounding to move to the next digit if the third digit is higher than a 5.

2 Replies

@kelly_weedman 

With an amount in A2, you can use =ROUND(A1-0.000000001, 2)

@kelly_weedman 

Another way of overriding the convention that final 5s round up is to subtract the number from a large integer before rounding

 

= largeInteger - ROUND(largeInteger - unrounded, 2)

 

Of course, provided you use 365 and think it is worth the effort

 

= ROUNDλ(unroundedValues, 2)

where

ROUNDλ
= LAMBDA(num,p, 
    INT(num) + 1 - ROUND(1 - MOD(num,1), p)
  )

 

would do the job and conceal the irrelevant calculation steps.