Cell Contents

[DanMun]

When I enter the number 12 in cell C6 and the number 13 in C7, in cell C8 it shows the answer as being 25, the formula in cell C8 is =SUM(C6:C7) and the special formatting in cell C8 is number, custom, #. In cell D9, with the formula of =C8/2, and special formatting of number, custom, #, it shows the answer as 13 which should be 12.5. I can go to the ribbon on top and move the decimal point and it corrects this problem. When I do this the special formatting changes to number, custom, #.0. When I remove the numbers from C6 and C7 whats left in D9 is .0 to which I only want a blank cell with the formatting of number, custom, #.0. Any help would be appreciated!

Thanks-
Dan

 

[Gerald Higgins]

The fact that D9 shows 0 is not only because of the formatting, and what's in C6 and C7, it's also influenced by what is in C8.

So, if you delete the entries from C6 and C7, C8 shows 0, which is mathematically correct, and D9 also shows 0, because 0/2 is actually 0.

If you want it to show as something else, there are various options, for example this in D9

=if(c8=0,"",c8/2)

 

[DanMun]

The fact that D9 shows 0 is not only because of the formatting, and what's in C6 and C7, it's also influenced by what is in C8.

So, if you delete the entries from C6 and C7, C8 shows 0, which is mathematically correct, and D9 also shows 0, because 0/2 is actually 0.

If you want it to show as something else, there are various options, for example this in D9

=if(c8=0,"",c8/2)

Hi Gerald and thanks for your response! Example: In cell D9 I'm showing .0 with a formula of =C8/2 and formatting of number, custom,#.0. What I would like to accomplish is all of the formulas and formatting for this cell to remain the same but only not showing .0 in the cell! Thanks

 

[Gerald Higgins]

If you want all of the formulas and formatting to remain the same, nothing's going to change

You have to change SOMETHING to make a difference.

 

[Scott Huish]

It's not possible to have a custom number format that limits a decimal place but doesn't show the decimal if it is not needed. It will take VBA to format the cell like that.