• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Zero the mystery

A

AMIT ANAND

New Member
Dear All,

When we keep format for any cell an general and enter a number that cell......Zero at the end of a decimal number is not displayed in output sheet. For example if i enter 99.60 it is diaplaying 99.6 . I want to know is there any way to diaply this zero. I also want that if i enter 99.6 it should not take additional zero.
 
You would need to change the cell format to number, and then set number of decimals to display. In General format, XL only displays the literal value, and the literal value of 99.60 is 99.6
 
Thanks Luke M . I know this . Problem is number of decimal is not fixed. Some time one decimal other time two decimal. If two decimal and last digit is zero then it is not displayed. If i fix two decimal then for single decimal entry extra zero comes and that too is not acceptable.
 
Might I ask then, in all seriousness, why does it matter? For all mathematical and function purposes, the two values are the same. If there is absolutely no need for the number to be used in calculations, and is display only, you could format the cell as Text and then enter the value. This would preserve your trailing zeros.
 
It matters. i am in pharma field, If certificate of analysis says potency is 96.70 then regulatory needs me to show it as 96.70 and if it is 96.7 then as 96.7. I am using the potency as number for further calculation in my input spreadsheet. But in output spreadsheet i am just displaying it.
 
Then I would have your original input cell be formatted as text, and input your value as you wish, so that trailing zeros are preserved. Then, perhaps in a column to the right somewhere, have a formula like:
=VALUE(A2)
to convert the text input into a number. Then, you can base all the downstream calculations off of this cell.

Other idea, going in reverse, is to input number regularly, losing trailing zero, and have another cell where you input # of significant digits/decimals. Then in your output sheet, you could do:
=TEXT(InputCell,"0." & REPT(0,NumDecimals))
 
Yeah......You are right can be done in this way. But converting the number to text excel shows small green triangle how to get rid of that. Alternative option is not feasible as i can not ask user to input number of decimal. I can count the number of digits after decimal (after converting the input into text) using formulae: len(a1)-find(".",a1). Then in output sheet fix the number of decimal as obtained by the above formuale using FIXED formulae.Will try and let you know. Thanks Buddy
 
Hi Amit,

To get rid off the green triangle as error.

Try below steps:
Under File==>Options==>Click on Formulas de-select Enable background error checking in Error Checking (3 from Top)

Regards,
AM:)
 
You must log in or register to reply here.
Back
Top