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

#### eemhuynh

##### New Member
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.

Emily

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### DRSteele

##### Well-known Member
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

##### Well-known Member
Welcome to Mr. Excel. I think this is one way:

Book5
AB
126.2470126.25
226.2430126.24301
Sheet1
Cell Formulas
RangeFormula
B1:B2B1=IF(5>TRUNC(1000*A1)-10*TRUNC(100*A1),A1,0.01*TRUNC((0.5+100*A1)))

I like DRSteele's way!!!

#### Phuoc

##### Active Member
Another way

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

#### eemhuynh

##### New Member

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

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

Book5
AB
126.2470126.25
226.2430126.24301
Sheet1
Cell Formulas
RangeFormula
B1:B2B1=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

##### Well-known Member
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.

Replies
2
Views
42
Replies
3
Views
676
Replies
6
Views
447
Replies
2
Views
354
Replies
2
Views
450

1,129,983
Messages
5,639,380
Members
417,084
Latest member
elitepaper

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back