Excel rounding to look at all decimal points - Stack Overflow

admin管理员组

文章数量:1405308

I'm trying to ensure excel rounds to 2 decimal decimal points after looking at the entire number. Currently if I do this =round(6.2445,2) its gives me 6.24. However it should actually give me 6.25.

I know one workaround would be doing =round(round(6.2445,3),2), but that won't work for 6.24445.

I can't use roundup as that would be incorrect while rounding 6.24145

I'm trying to ensure excel rounds to 2 decimal decimal points after looking at the entire number. Currently if I do this =round(6.2445,2) its gives me 6.24. However it should actually give me 6.25.

I know one workaround would be doing =round(round(6.2445,3),2), but that won't work for 6.24445.

I can't use roundup as that would be incorrect while rounding 6.24145

• excel
• excel-formula

Share Improve this question Follow edited Mar 23 at 15:37 Mayukh Bhattacharya 27.8k99 gold badges2929 silver badges4242 bronze badges asked Mar 22 at 23:13 Raunak ThomasRaunak Thomas 1,41511 gold badge1414 silver badges2929 bronze badges 4

• 3 This is the normal rounding rules, everything beyond the first non-significant digit is ignored only the number immediately to the right of that last significant digit (the first non significant digit) is considered when rounding. For reference see "Rules for Rounding" here, but is the convention I've always used as well and seen in practice. chem.libretexts./Bookshelves/General_Chemistry/Chem1_(Lower)/… – shaunhusain Commented Mar 22 at 23:22
• 4 Definitely not standard rounding but Maybe REDUCE(A1,SEQUENCE(10,,11,-1),LAMBDA(a,b,ROUND(a,b))) – Ron Rosenfeld Commented Mar 23 at 0:14
• Cool @RonRosenfeld you could make it dynamical using: =REDUCE(A1,SEQUENCE(LEN(A1),,,LEN(A1)+1,-1),LAMBDA(a,b,ROUND(a,b))) – P.b Commented Mar 23 at 20:19
• 1 @P.b Since the values will be numeric, I'd probably just set n=15 or 16 or whatever the largest number of digits the OP thinks might be of significance. – Ron Rosenfeld Commented Mar 24 at 0:00

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 5

Rounding rules in mathematics generally apply when on the "border". Everything above the border gets rounded up, and below the border gets rounded down.

What happens in the borderline condition can vary: round up; round away from zero; round to the nearest even number; and a few other more esoteric methods.

In your example, you are not really rounding to two decimals. Since 6.2445 is less than 6.25, it should properly round to 6.24.

What you are asking for is rather to sequentially round the number starting at the penultimate digit and working your way back until you have two digits left.

The formula in my comment will do that sequential rounding. More generally written:

=REDUCE(A1,SEQUENCE(n,,n+(number_of_digits_minus_one),-1),LAMBDA(a,b,ROUND(a,b)))

• A1: Number to be sequentially rounded
• n: Maximum number of digits to account for in the decimal portion of A1. No need for this to be greater than 15, but it can be greater than the number of digits in your actual number.

Note that this is an extended version of your workaround =round(round(6.2445,3),2)

本文标签： Excel rounding to look at all decimal pointsStack Overflow