# Rounding excel numbers

#### StefanoM

##### New Member
Dear All,
I have to display a rounded number in protected spreadsheet letting the user to dynamically select how may decimal places to put. It works quite well, except when I want a zero at the end of the number.
number to round A1 = 2.002345
decimal places B1 = 1
formula ROUND(A1, B1)
result of the formula = 2
But I want 2.0 to be displayed.

Stefano

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

##### Well-known Member
Hi,

Try =TEXT(ROUND(A1,B1),"####.0")

#### StefanoM

##### New Member
Hi,

Try =TEXT(ROUND(A1,B1),"####.0")

Doesn't work.
It works only in the case I've shown in my example but is not dynamic.
I put
B1 = 2
I expect 2.00 as result and the formula does not work

#### Tetra201

##### MrExcel MVP
Is this what you need?

=TEXT(ROUND(A1,B1),IF(B1>0,"0."&REPT("0",B1),"General"))

##### Well-known Member
It's work for me....Are you sure A1 is a number ?

#### StefanoM

##### New Member
It works to display the number correctly, but the number is a text, so I cannot use it in further calculation I need (from example using the formula SUMIF).

#### Tetra201

##### MrExcel MVP
It works to display the number correctly, but the number is a text, so I cannot use it in further calculation I need (from example using the formula SUMIF).
Let's assume you have your rounded number in cell C1. You have two options:

1) Stick with the =TEXT(...) solution and use VALUE(C1) in your subsequent formulas.

2) Conditionally format cell C1 with a set of rules like this:
Rule =\$B\$1=1, custom number format 0.0
Rule =\$B\$1=2, custom number format 0.00
Rule =\$B\$1=3, custom number format 0.000 and so on.

Replies
4
Views
392
Replies
3
Views
215
Replies
0
Views
182
Replies
1
Views
141
Replies
8
Views
301

1,191,686
Messages
5,988,067
Members
440,125
Latest member
vincentchu2369

### 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