Rounding only if the 3rd decimal place is greater than 5

[eemhuynh]

Hi everyone,

I have a task where I need to round a number up to 2 decimals ONLY IF the third decimal place is greater than 5, otherwise it'd stay as it is.

For example, if the figure is $26.24701 then round it to $26.25
If it's $26.24301 then keep the 5 decimals.

I've been trying to run an IF but cannot find a way to specifically select the 3rd decimal place.

Thanks in advance.
Emily

 

[DRSteele]

Welcome to the forum.

There are several ways to skin this cat. Here is one: if your number is in A2, =IF(ROUND(A2,2)>A2,ROUND(A2,2),A2)

 

[kweaver]

Welcome to Mr. Excel. I think this is one way:

Book5
	A	B
1	26.24701	26.25
2	26.24301	26.24301
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=IF(5>TRUNC(1000*A1)-10*TRUNC(100*A1),A1,0.01*TRUNC((0.5+100*A1)))

I like DRSteele's way!!!

 

[Phuoc]

Another way

=MAX(ROUND(A2,2),A2)

 

[eemhuynh]

Thank you so much for your prompt reply!!! It worked indeed!! I was on a deadline at work so really appreciate this.

Welcome to the forum.

There are several ways to skin this cat. Here is one: if your number is in A2, =IF(ROUND(A2,2)>A2,ROUND(A2,2),A2)

 

[eemhuynh]

Thanks! This is also clever!

Another way

=MAX(ROUND(A2,2),A2)

 

[eemhuynh]

Welcome to Mr. Excel. I think this is one way:

Book5
	A	B
1	26.24701	26.25
2	26.24301	26.24301
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=IF(5>TRUNC(1000*A1)-10*TRUNC(100*A1),A1,0.01*TRUNC((0.5+100*A1)))

I like DRSteele's way!!!

This works too. Thanks!

 

[DRSteele]

Thank you so much for your prompt reply!!! It worked indeed!! I was on a deadline at work so really appreciate this.

You're welcome. The solution by Phouc is clearly superior, though.