Show Decimals only if needed

schneitodd

New Member
Joined
Mar 18, 2003
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
I have a Summary sheet that adds up numbers from several sheets. In the summary table, I would like to only show decimals if they are needed. For example, if the sum is 4, show as "4" not "4.0", but if the sum is 3.1, show as "3.1".
How can I do this?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
You can kinda do this via number formatting, but I can't seem to get a way of losing the decimal point if there are no fractional values.

Anyway, format in the custom box is:

0.#;-0.#

Best regards

Richard
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Simply set the cell format back to General should do the trick.

So simple!

I would love to know if it is possible to get rid of the decimal separator (.) though thru a custom format.

Richard
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
I found this post educational (I never knew what the heck "general" really did) and interesting. I know that this will do exactly what the OP wanted, but it seems funny to me - here is why:

Let's say the a4's cell format is general, and the contents are =A2+A3
Let's also say that a2 has 3.1 and a3 has 7.9. A4 will then show 11, even though any math teacher would say that the answer (because of "significant digits") is 11.0

Gene, "The Mortgage Man", Klein
 

Forum statistics

Threads
1,136,263
Messages
5,674,710
Members
419,521
Latest member
Jasonnie

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