Rounding while still seeing ".0"

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
655
Good Morning,

I'm trying to use a simple rounding formula but I don't want to lose the ".0" when a number, say 14.96, is rounded up to 15. I cannot format the cell itself. Ideas on how to do this. It's probably super simple but I've been brain farting all afternoon/evening....
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
The formula give you a result (15 for example); the way the result is displayed in the cell depends on the cell formatting.

To se ".0" just select the cell, right click, choose "Cell format"; in the "Number" tab select Number and specify 1 decimal digit; complete with Ok.
If this doesn't do the job, then please specify wich formula you used to round the result.

Bye
 
Upvote 0
The formula give you a result (15 for example); the way the result is displayed in the cell depends on the cell formatting.

To se ".0" just select the cell, right click, choose "Cell format"; in the "Number" tab select Number and specify 1 decimal digit; complete with Ok.
If this doesn't do the job, then please specify wich formula you used to round the result.

Bye

Fair enough- here ya go!
A1 is the number to be rounded off.
Code:
="F/"&ROUND(A1,1)&"//"
 
Last edited:
Upvote 0
Fair enough- here ya go!
A1 is the number to be rounded off.
Code:
="F/"&ROUND(A1,1)&"//"
Well, your formula returns a "String", not a number; so Formatting will not change the way it is displayed.

Try changing your formula to
Code:
="F/"&TEXT(ROUND(A1,1),"0.0")&"//"

Bye
 
Upvote 0
Yes. Thank you. That’s why I posted on here trying to figure out how to change this....


Ah yes. Duh. That makes sense. I’ll give it a shot
 
Last edited:
Upvote 0
You can drop the decimal in the text format, omitting the need for the round function,

="F/"&TEXT(A1,"0.\0")&"//"

Or even shorter

=TEXT(A1,"F\/0.\0\/\/")

You could also use the format in the text function above as a custom format, removing the formula entirely.
 
Upvote 0
You can drop the decimal in the text format, omitting the need for the round function,

="F/"&TEXT(A1,"0.\0")&"//"

Or even shorter

=TEXT(A1,"F\/0.\0\/\/")

You could also use the format in the text function above as a custom format, removing the formula entirely.


So I've gone with the formula here but the only issue with it is that I want to remove the physical decimal mark (while keeping the decimal value. So 13.2 would be "132" on here.

Thanks
="F/"&TEXT(ROUND(A1,1),"0.0")&"//"
 
Upvote 0
So I've gone with the formula here but the only issue with it is that I want to remove the physical decimal mark (while keeping the decimal value. So 13.2 would be "132" on here.
Thanks for beeing a little bit more clear (but you should also modify the title of the discussion)...

With the formula I proposed:
Code:
="F/"&TEXT(ROUND(A1*10,1),"0")&"//"

Bye
 
Upvote 0
Rounding, having the decimal place with seeing the decimal point

Good Morning,

Your first formula (the one with "0.0" was closer than your second proposition, however, I'm looking to have the decimal point disappear without losing the decimal place.

So I never see a whole number as the output without a "0" behind it. Here are a few examples of the desired output:

14.0 = 140
13.9 = 139
12.98 = 130
12.54 = 125

Make sense?
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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
Back
Top