not allowing more than 2 decimals

[Guest]

Is there a way to only allow 2 decimals to be entered....formating or
validating.
I dont want to show more than 2 decimals and sometimes operator errors allow
3 decimals to change the calculation sums by rounding up because of the 3
digit decimal entry.

 

[Dave Peterson]

How about Data|Validation

With a formula of:

=100*A1=INT(100*A1)

(with A1 the activecell in your selection)

 

[Peo Sjoblom]

One possible way

=OR(AND(ISNUMBER(A1),LEFT(RIGHT(A1,2))="."),AND(ISNUMBER(A1),LEFT(RIGHT(A1,3))="."),INT(A1)=A1)


would allow

123
123.1
123.12

but not

123.123

use data>validation, allow custom, change cell references to fit

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Guest]

Thank you so much ....this was driving me crazy...

 

[Guest]

Thank you so much...I will give it a try

How about Data|Validation

With a formula of:

=100*A1=INT(100*A1)

(with A1 the activecell in your selection)

 

[Guest]

I am not sure what do you actually mean karen, because this is very easy to
do that and the answers given to you are a lil confusing,
why don't you just choose the cells which you want to show just 2 decimals
and use Format/cells , then in Number tab choose the number item and set the
"decimal places" to "2" ,
or even simply choose the cells and use the "increase decimal" and "decrease
decimal" buttons in format bar to set the decimal points.

 

[Peo Sjoblom]

I believe that was the problem she had, format to 2 decimal places,
someone will enter 123.12345 and it will display as 123.12 while the real
value
will be 123.12345. Having said that Dave's answer was definitely slicker
than mine

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Dave Peterson]

yeah but I don't check to make sure it's a number.

(and you got the nice thank you back!)

(I typed this while wiping the tears away with my left hand. <vbg>)