Rounding excel numbers

[StefanoM]

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.

Thank you for your help,
Stefano

 

[admiral100]

Hi,

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

 

[StefanoM]

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]

Is this what you need?

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

 

[admiral100]

It's work for me....Are you sure A1 is a number ?

 

[StefanoM]

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]

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.