Unwanted decimal places when entering integers

[Alex Bell]

Forgive me if this is not the correct group for this problem; if it
isn't please tell me where to go.

I have been using a particular spreadsheet for 3-4 years and was able to
enter integers into a column of cells, and having formatted the cells as
numbers having no decimal place have them show as integers and behave as
integers in formulae.

Some weeks ago for reasons entirely unknown to me Excel insists on
dividing the numbers by 100 so that e.g. 1 becomes 0.01; it shows as 0
if I format the cell as an integer but acts like a 0.01 in formulae.
Excel also does this in new spreadsheets.

I have noticed some weird things about this problem, in particular that
the same spreadsheet run on another computer behaves the way it was
originally designed to - i.e, when I enter 1 I get 1. The problem is
not present when I run Excel in safe mode or when I run Windows XP in
safe mode, but it is very inconvenient to run Excel in safe mode and I
shouldn't have to anyway. I have checked for add-ons for Excel but they
there don't seem to be any.

I don't get the problem when I run the spreadsheet under OpenOffice Calc
as an Excel program, but I'd like to be able to run it under Excel.

Can anyone help please or point me to a document in the Microsoft labyrinth?

 

[Roger Govier]

Hi Alex

Tools>Options>Edit>Uncheck Fixed Decimal places

 

[Ken Johnson]

Forgive me if this is not the correct group for this problem; if it
isn't please tell me where to go.

I have been using a particular spreadsheet for 3-4 years and was able to
enter integers into a column of cells, and having formatted the cells as
numbers having no decimal place have them show as integers and behave as
integers in formulae.

Some weeks ago for reasons entirely unknown to me Excel insists on
dividing the numbers by 100 so that e.g. 1 becomes 0.01; it shows as 0
if I format the cell as an integer but acts like a 0.01 in formulae.
Excel also does this in new spreadsheets.

I have noticed some weird things about this problem, in particular that
the same spreadsheet run on another computer behaves the way it was
originally designed to - i.e, when I enter 1 I get 1. The problem is
not present when I run Excel in safe mode or when I run Windows XP in
safe mode, but it is very inconvenient to run Excel in safe mode and I
shouldn't have to anyway. I have checked for add-ons for Excel but they
there don't seem to be any.

I don't get the problem when I run the spreadsheet under OpenOffice Calc
as an Excel program, but I'd like to be able to run it under Excel.

Can anyone help please or point me to a document in the Microsoft labyrinth?

Hi Alex,

Check this...

Tools|Options|Edit|Fixed Decimal Places should NOT be ticked.

Ken Johnson

 

[Alex Bell]

Hi Alex,

Check this...

Tools|Options|Edit|Fixed Decimal Places should NOT be ticked.

Ken Johnson

Many thanks to you both. It's so simple if one knows what one is doing.
Any ideas why it suddenly changed? I seem to remember that I upgraded
around the same time.

 

[Roger Govier]

Hi Alex

You're very welcome. Thanks for the feedback.
As to why it happened, I'm not sure.
Very Happy New Year to you.

 

[Ragdyer]

As to WHY and/or HOW it happened:
This option (fixed decimal), *as well as* the (manual - auto) calculate
option are determined per session, by the saved option of the *first* file
opened in that session.

See if this old post explains it to you:

http://tinyurl.com/yj22zq

Don't forget, in this context, "calc" and "fixed decimal" are
interchangeable.
--
HTH,


RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Roger Govier]

Hi RD
Thanks for that info.
I knew it applied to calculation mode, but not that it also applied to
fixed decimal.
The first of many new things to be learned in 2007 - I hope!!!

 

[Alex Bell]

As to WHY and/or HOW it happened:
This option (fixed decimal), *as well as* the (manual - auto) calculate
option are determined per session, by the saved option of the *first* file
opened in that session.

See if this old post explains it to you:

http://tinyurl.com/yj22zq

Don't forget, in this context, "calc" and "fixed decimal" are
interchangeable.

Thanks for your help, but I'm afraid I'm still puzzled. I certainly do
not remember doing anything with the Fixed Decimal places option when I
designed the spreadsheet 4-5 years ago, nor at any time since then. In
fact I didn't even know of this option's existence. That is, I'm as
certain as I can be that I didn't change anything (apart from upgrading)
before this problem suddenly appeared.

When I went to try the fix Ken and Roger gave me I tried the fix on the
spreadsheet which has been giving me grief and on a new spreadsheet, and
of course it worked. But I did not save the spreadsheets. A moment ago
I started a new dummy spreadsheet and the problem is still fixed.

And I can't see how the explanation Ragdyer gives explains why the same
spreadsheet works as expected on one computer and not on another.

--
Regards, Alex

PS Is there a convention about top posting or bottom posting in this
newsgroup? I'm used to bottom posting, but am happy to follow the
convention.

 

[Roger Govier]

Hi Alex

If the first workbook opened on computer A did not have Fixed decimals
set, then the file would work fine.
If the first workbook opened on computer B had Fixed decimals set, then
that would be the default mode and the file would not work.

Remember, the first workbook opened could be the hidden Personal.xls and
the default Book.xlt.
If these are different on the 2 computers, this could explain what you
are seeing.

 

[Ragdyer]

Speaking of 2007 ... Does anyone know if this stupid "feature" is *STILL* a
"feature" in the 2007 version ? ! ? ! ?

 

[Gord Dibben]

RD

The thinking that FD is a workbook setting is incorrect IMO.

It is a global setting and is stored in the Registry.

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options

Right-side under REG_DWORD AutoDec

(4) is FD set for 2 DP and disabled.

(5) is FD set for 2 DP and enabled.

There are various values for different DP settings like (17) is FD enabled with
8 DP

At least this is how my Excel interacts with the Registry when changing FD
settings.


Gord

 

[Gord Dibben]

Corrections

See in-line

RD

The thinking that FD is a workbook setting is incorrect IMO.

It is a global setting and is stored in the Registry.

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options

Right-side under REG_DWORD AutoDec

(4) is FD set for 2 DP and disabled.

Should have read DWORD value is 0000004(4)

(5) is FD set for 2 DP and enabled.

There are various values for different DP settings like (17) is FD enabled with
8 DP

Should read 00000011(17)

 

[Ragdyer]

You're right Gord.
I've been under this impression for several years now, and I don't remember
where and/or how I got it. I seem to remember testing it somehow ... one
way or another ... but it's a mute point now.

Thanks for the correction ... and apologies to any and all that I might have
misled.

BUT ... the question still stands as to whether or not that other stupid
"feature" (auto-calc), is still a "feature" in the 2007 version.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Corrections

See in-line

RD

The thinking that FD is a workbook setting is incorrect IMO.

It is a global setting and is stored in the Registry.

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options

Right-side under REG_DWORD AutoDec

(4) is FD set for 2 DP and disabled.

Should have read DWORD value is 0000004(4)

(5) is FD set for 2 DP and enabled.

There are various values for different DP settings like (17) is FD enabled with
8 DP

Should read 00000011(17)

At least this is how my Excel interacts with the Registry when changing FD
settings.


Gord

 

[Gord Dibben]

Thanks RD

As far as 2007 and "calc mode" goes, I have yet to install 2007 so others will
have to feedback to us.

Roger G. Are you out there?


Gord

 

[Roger Govier]

Hi Gord and RD

Sorry to have to report it is still the same as earlier versions.
I saved a file with Calculation set to Manual. Closed XL2007. Opened
XL2007, loaded the file then created a new Workbook and Calculation mode
was Manual

 

[RagDyeR]

Thanks for the info Roger.

Can only guess that this was small potatoes to Redmond, considering the
gigantic changes that were made.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Hi Gord and RD

Sorry to have to report it is still the same as earlier versions.
I saved a file with Calculation set to Manual. Closed XL2007. Opened
XL2007, loaded the file then created a new Workbook and Calculation mode
was Manual

 

[Gord Dibben]

Thanks Roger.

IF I ever install 2007 I will watch for that, same as I do now.


Gord