EXCEL is automatically converting to decimal 1

[PatBethea]

Recently, I've noticed EXCEL automatically converting my formulas which contain fractions to a decimal format (for instance if I type in "4/12", it converts this to "0.33333333333" in the formula line). This is not desirable. The program is allowing me to go back into the formulas and replace the calculated number with the original fractions, but this is of course double-work. I can't think of anything I did to make this occur and the Help system is, of course, no help. Does anybody have any ideas?

 

[doct9960]

PatBethea,

Have you tried formatting the cell? Try right-clicking to the cell, select Format Cell, click the Number tab, select Fraction in the Category, select Type.

 

[PatBethea]

Doct,

Thanks for the reply, but I apparently did not pose the problem properly. I want my final reuslt to be a formatted number. The problem is in the formula itself - the fractions are changing to decimals. When I go back and look at a formua, I see decimals instead of what I typed in.

 

[electricpete]

I have never encountered that. I created a spreadsheet with formula =4/12. I saved it in several different formats (like excel 95, excel 2.1, lotus 123 wk1). In all cases upon reopening, the formula is 4/12

Must be some weird hidden setting somewhere

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[doct9960]

PatBethea,

That's weird. That hasn't happened to me yet. Are you using a Macintosh? Also, try changing some parameters in Tools menu, Options.

 

[PatBethea]

Thanks for the replies. I've played with it for a couple of hours now. I have found that if I lead a formula with "=", it doesn't happen. If I lead the formula with "+", it does. I don't understand it. When entering numerical data, it is so much more convenient to use the number pad, but I suppose I will re-train myself to use the "=" key (unless someone can figure out how to fix it).

And no, I am not using a Mac.

 

[SteveGregory]

It sound like you are a former Quattro Pro user like myself. Entering an equation starting with a + or just a number works fine in Quattro Pro, but in Excel all equations must start with an =.

 

[PatBethea]

Steve,

I did "cut my teeth" on Quattro Pro, but I've used Excel exclusively for the past 6-7 years. I've not had any problems until 2 or 3 weeks ago.

 

[yogia]

Hi Pat:

I am using EXCEL 97, and I have entered =4/12, as well as +4/12, both in cell as well as in the formula bar, and the formula bar always ends up with =4/12.

What version of EXCEL are you using ... could it be a version thing? ... but wait you said it started to happen recently.

It may have to do with something else that may be going on either with EXCEL, the Operating System, or some piece of hardware. I have a suggestion, close EXCEL, restart it and see if the normal behavior is restored; if not, I suggest shut the computer down, restart EXCEL, and see if the normal behavior is restored.

Let us know how it sorts out.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[IRstuff]

Actually, if I enter 4/12 by itself, it converts it to April-12.

"+4/12" results in "=4/12"
"=4/12" results in "=4/12"

This is using Excel97 SP3.

TTFN

Eng-Tips Policies FAQ731-376

 

[dimjim]

Hi light all cells and select format cell
and see what the default is. It should
be changed to fraction. Sounds like it is
defaulting to date.

 

[wannabeSE]

Pat,

You are not alone. I use Excel 2003. If the cell is formatted as a "number" before entering +4/12, the equation is lost and the answer (0.333...) replaces it. If the cell is not formatted (left as default which is general), +4/12 becomes =4/12. Also using the default, 4/12 becomes 4/12/2007 and is displayed as 12-Apr.

In the past, this has been bothersome. I never took the time to see when or why it happened until I saw this thread. I migrated to Excel from Lotus 123 where "+" is used to begin formulas.

Phil

 

[handleman]

You will get the behavior you desire if you go to Tools->Options, choose the Transition tab, and choose "Transition formula entry".

 

[PatBethea]

Handleman,

That's exactly what I was looking for. Thank you. Have you (or anybody else) got any idea why this problem began recently? I wonder if I didn't download an "update" from Microsoft that may have switched that option 'off'. I don't know.

Thanks again.