+ Reply to Thread
Results 1 to 10 of 10

Display fraction instead of result in formula bar (Excel 2016)

  1. #1
    Registered User
    Join Date
    08-14-2012
    Location
    Quebec
    MS-Off Ver
    Excel 365
    Posts
    4

    Display fraction instead of result in formula bar (Excel 2016)

    I've recentely upgraded to Excel 2016 (Office 365) and I have a small problem.
    In my spreadsheet, I need to convert feet&inches to metric, so I have set my formula to convert feet to meters from an input cell.
    However, in my input cell, when I type +188/12, the formula bar displays 15.6667 instead of =+188/12. The cell is allright, it displays 15.67' as I expect, but I want the formula bar to keep this fraction. not the result.
    I tried elsewhere in my sheet where I have no custom cell format and I retain the formula, which is weird.

    How do I correct this?

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Display fraction instead of result in formula bar (Excel 2016)

    The formula bar always displays formulas when a formula was entered in the cell. The cell always display the results of a formula.
    BTW, you don't need to use + sign as it totally useless when it is entered right after the equal sign.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Contributor
    Join Date
    08-25-2015
    Location
    Near Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    152

    Re: Display fraction instead of result in formula bar (Excel 2016)

    If you use the PLUS SIGN (+), you will see the result, not the "formula" in the formula bar.
    If you start the formula with an EQUAL SIGN (=), then you will see the formula, not the result, in the formula bar.
    Not sure why, tho!

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Display fraction instead of result in formula bar (Excel 2016)

    I cannot recreate what you describe. When I enter =188/12 or =+188/12 or +188/12 into a cell, the formula bar retains the formula (it seems to convert them all to =188/12) and the cell result is 15.666... as expected.

    What is the difference, in the way you are using the result, between the value 15.66666.... and 188/12 (other than the inevitable rounding error caused by a non-terminating decimal)?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    08-14-2012
    Location
    Quebec
    MS-Off Ver
    Excel 365
    Posts
    4

    Re: Display fraction instead of result in formula bar (Excel 2016)

    Quote Originally Posted by MrShorty View Post
    I cannot recreate what you describe. When I enter =188/12 or =+188/12 or +188/12 into a cell, the formula bar retains the formula (it seems to convert them all to =188/12) and the cell result is 15.666... as expected.
    It's easier to type a + witht the numeric keypad than the = on the regular keyboard
    " = + " is what used to be displayed in Excel 2010, I usually only type " + ", old habits since Lotus 1-2-3 DOS...

    What is the difference, in the way you are using the result, between the value 15.66666.... and 188/12 (other than the inevitable rounding error caused by a non-terminating decimal)?
    To double check for typos. Maybe I typed 189/12 instead of 188, maybe I type 180/12. Sometimes it's more like +3+4/12+3/16/12. I just want to make sure I typed the right thing, and it's near impossible when I only see the result and not what I typed.

    I cannot recreate what you describe. When I enter =188/12 or =+188/12 or +188/12 into a cell, the formula bar retains the formula (it seems to convert them all to =188/12) and the cell result is 15.666... as expected.
    It seems to happen only since I upgraded to Office 2016 (I had 2010 prior to that) and only in formatted cells. Looking at it, elsewhere in my spreadsheet, where I don't use a custom format for the cells, it seems to display the formula correctly, not the result. That is really weird. I don't think I've ever seen Excel do that, or another spreadsheet for that matter.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Display fraction instead of result in formula bar (Excel 2016)

    To double check for typos. Maybe I typed 189/12 instead of 188, maybe I type 180/12. Sometimes it's more like +3+4/12+3/16/12. I just want to make sure I typed the right thing, and it's near impossible when I only see the result and not what I typed.
    It seems to happen only since I upgraded to Office 2016 (I had 2010 prior to that) and only in formatted cells.
    If it is a new "feature" of Excel 2016, I cannot test it. I might suggest that you provide some of the details of your spreadsheet, so that one of us can recreate the spreadsheet as accurately as possible. if you can upload a sample sheet (with formatting and such all applied), that would probably be the easiest way to show the different variations in entry and formatting details that you are encountering.

  7. #7
    Registered User
    Join Date
    08-14-2012
    Location
    Quebec
    MS-Off Ver
    Excel 365
    Posts
    4

    Re: Display fraction instead of result in formula bar (Excel 2016)

    Ok. here is my file:
    http://rg.to/file/9d14a79724c0a69673...CIER.xlsx.html

    It's just a basic worksheet to convert lengths of steel rebars in weight (kg), no macros, nothing confidential.

    If you look at cell E4, it's where I type my fraction (actually, all yellow cells in column E, depending on what I need, but suffice for the exercise).
    =188/12 gives me =188/12 in the formula bar, but if I type +188/12 I get 15.66666667.
    If you go to one of the blank cells, say E12 and type +188/12, it will correctly display =188/12 in the formula bar.
    I figured there was some new setting I didn't know of.
    Attached Files Attached Files
    Last edited by viper37; 06-02-2016 at 02:48 PM.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Display fraction instead of result in formula bar (Excel 2016)

    viper37,

    Unable to download file from hosting site. Please try to upload to the forum direct. Instructions on how to do this are in the FAQs.

    In the meantime would something like this suffice?

    In A1

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    then in B1
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    That will return "=188/12".

    FORMULATEXT was introduced in Excel 2013 so it should work 2016 I would think.
    Dave

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Display fraction instead of result in formula bar (Excel 2016)

    Now I can replicate what you are seeing in 2007, so it is not specific to 2016. It does seem related to formatting, though I haven't explored thoroughly. If I set the format to some of those yellow cells to "general", then it stops converting the formula to numbers. Is there a problem with setting these cell's number format to General?
    I also note that if I turn on "Transition formula entry" under Lotus compatibility settings, that it keeps the formula even in formatted cell. (though I would not recommend this one because I do not know all that changes with Lotus compatibility settings).
    I note that longer entries only partially substitute for values. +3+4/12+3/15 shows up as =3+0.33333+3/15.

    I don't have any solutions. It might depend on how important your number format is to the operation of the spreadsheet. As noted, it seems to work correctly for General formatted cells, is there a reason that these cells cannot be formatted as general?

  10. #10
    Registered User
    Join Date
    08-14-2012
    Location
    Quebec
    MS-Off Ver
    Excel 365
    Posts
    4

    Re: Display fraction instead of result in formula bar (Excel 2016)

    Quote Originally Posted by MrShorty View Post
    I don't have any solutions. It might depend on how important your number format is to the operation of the spreadsheet. As noted, it seems to work correctly for General formatted cells, is there a reason that these cells cannot be formatted as general?
    Strange, I never noticed it in 2010.
    It's not terribly important to set in to general for this one, more so in another spreadsheet where I have the same problem.

    Well, I'll have to settle with using = instead.

    Thanks for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Why won't Excel 2016 display my custom ribbon?
    By rkjudy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-05-2015, 04:20 PM
  2. Replies: 7
    Last Post: 11-27-2015, 11:12 AM
  3. Replies: 6
    Last Post: 10-04-2014, 12:39 PM
  4. Replies: 7
    Last Post: 12-17-2010, 04:23 PM
  5. how do i get excel to display the result of a formula i build?
    By Mike in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-25-2006, 03:30 AM
  6. [SOLVED] display words in a formula result in Excel
    By dhollifield in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-17-2005, 11:45 PM
  7. [SOLVED] Can I make a formula in Excel to display result in same cell?
    By Neiko in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-01-2005, 06:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1