×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

EXCEL is automatically converting to decimal

EXCEL is automatically converting to decimal

EXCEL is automatically converting to decimal

(OP)
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?

RE: EXCEL is automatically converting to decimal

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.  

RE: EXCEL is automatically converting to decimal

(OP)
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.

RE: EXCEL is automatically converting to decimal

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.

RE: EXCEL is automatically converting to decimal

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.

RE: EXCEL is automatically converting to decimal

(OP)
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.

RE: EXCEL is automatically converting to decimal

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 =.

RE: EXCEL is automatically converting to decimal

(OP)
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.

RE: EXCEL is automatically converting to decimal

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

RE: EXCEL is automatically converting to decimal

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


RE: EXCEL is automatically converting to decimal

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.

RE: EXCEL is automatically converting to decimal

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

RE: EXCEL is automatically converting to decimal

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

RE: EXCEL is automatically converting to decimal

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources