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

eemhuynh

New Member
Joined
Feb 1, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
 

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
Joined
Mar 31, 2015
Messages
2,382
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 8, 2018
Messages
1,788
Office Version
  1. 365
  2. 2010
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!!!
 

eemhuynh

New Member
Joined
Feb 1, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Feb 1, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Mar 31, 2015
Messages
2,382
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top