Implied decimal point

Question

In Excel I regularly key in numbers with the decimal point implied similar to add mode on a calculator, e.g. (14.99 is keyed as 1499, 5.00 is key as 500). Is there a way to key a number with the decimal point implied but have it appear with the decimal point, e.g. key 1499 and have it appear as 14.99. In the past I have also used a formula to multiply the column by .01 but I would rather find another way.

Answer 1

GWnAtl wrote:

I don't see a tab named VALUE when I select Format | Cell. I see a tab named NUMBER that has a custom option but placing ##.00 in the TYPE box does not cause 1499 to appear as 14.99, at least in Excel 2007

Yclhk should have written #.00 .  But I'm not sure why you are pursuing it, other than idle curiosity and self-edification.  Note that formatting affects only the appearance, not the value.  It is __not__ the same as multiplying by 0.01, as you indicated you have been doing as a work-around.

Answer 2

Hi,

Tools options - Edit tab and check 'fixed decimal places.

But note that if toy key in 149 youl'll get 1.49 

1490 will convert to 14.9

---

If this post answers your question, please mark it as the Answer.

Mike H

Answer 3

yclhk wrote:

Another suggestion to solve your problem, format the cell(s) for entries as the following :

Format -> Cell(E) -> Tab "Value" -> select "Custom" -> in "Type (T)" -> type ##.00 -> OK

Then, if you type 1499 in the cell, the value will be 14.99 and type 1490, the value will be 14.90.

No.  The cell will __appear__ to be 14.90, but the __value__ will still be 1490.

Arguably, GWnAtl did write "have it __appear__ as 14.99" (emphasis added).  But since GWnAtl wrote "[i]n the past I have also used a formula to multiply the column by .01", it seems that he/she wants the __actual_value__ to be 14.90.

You would see a difference if you used a formula like INT(A1) to extract the integer part.

Answer 4

In Excel I regularly key in numbers with the decimal point implied similar to add mode on a calculator, e.g. (14.99 is keyed as 1499, 5.00 is key as 500). Is there a way to key a number with the decimal point implied but have it appear with the decimal point, e.g. key 1499 and have it appear as 14.99. In the past I have also used a formula to multiply the column by .01 but I would rather find another way.

Another suggestion to solve your problem, format the cell(s) for entries as the following :

Format -> Cell(E) -> Tab "Value" -> select "Custom" -> in "Type (T)" -> type ##.00 -> OK

Then, if you type 1499 in the cell, the value will be 14.99 and type 1490, the value will be 14.90.  You may format all the cells for entries in the way.

P.S.  My Office is Trad Chinese, hope the English named steps are corrected, Sorry.

Answer 5

Son of a gun. All this time I've been multipying by .01 and this was right under my nose.....thanks

Btw, is it done the saem way in Excel 2007. I have 2003 at home and 2007 at work