Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Formula showing in the cell but not the result.

This is a discussion on Formula showing in the cell but not the result. within the Excel Questions forums, part of the Question Forums category; I have a formula in a cell and when I use the formula bar it shows me the correct answer ...

  1. #1
    New Member
    Join Date
    Aug 2002
    Posts
    3

    Default

    I have a formula in a cell and when I use the formula bar it shows me the correct answer but it doesn't show me it in the worksheet. Only the formula shows up.

    The same formula could be another place in my worksheet and work fine.

    I have tried changing format. Copying formula from another location and changing the information to fit my needs it won't show me the result.

    Help!

    Thank you.

  2. #2
    Board Regular
    Join Date
    Aug 2002
    Location
    Emmen, Netherlands
    Posts
    256

    Default

    Majen,

    is there a "=" missing?

    today() instead of =today()

    Henk

  3. #3
    New Member
    Join Date
    Aug 2002
    Posts
    3

    Default

    =MID(E68,4,2)+2& "X"& LEFT(E68,2)+4
    here is the formula.

  4. #4
    MrExcel MVP
    Join Date
    Jun 2002
    Location
    North Canton, OH USA
    Posts
    791

    Default

    I'm not sure what you a looking for as a result but if you put 1234 into E68

    =MID(E68,4,2) & "2X"& LEFT(E68,2) & "4"

    will give you 42X124

    =VALUE(MID(E68,4,2)+2) & "X" & VALUE(LEFT(E68,2)+4)

    will give you 6X16

    Hope this helps!
    Jim

    Excel 2000; Windows 2000

  5. #5
    Board Regular
    Join Date
    Aug 2002
    Posts
    83

    Default

    I have had this problem too, many a time.

    The best thing I can suggest is that, instead of typing "=" in the beginning of the formula, click on the = button just to the left of the little edit line. This will bring up a formula editor dialog.

    That doesn't always work, but it seems to work most of the time.

    There is something goofy with Excel in that it shows, sometimes, the formula even though, yes, indeed, you began the formula with "=". In fact, even if you cut and paste from a working cell to another, sometimes the formula works and the output is shown, sometimes the formula, complete with leading "=", just appears.

    But it doesn't always happen when cutting and pasting. My guess is some as yet unknown way to reproduce in which Excel is thinking you're pasting a string instead of a formula, even though you copied it from another cell where it worked as a formula.

    Anyone wanting to explore further might make permutation tests of source and destination cells being text or not, and copying the source cell by way of selecting the sell and copying (marching ant lines around the cell) vs. editing the sell and copying the formula via text based highlight, as in a text editor. In fact...

    Yes! That's it!

    At least this combo works:

    Source and destination cells are formatted as normal, not text. Enter =4 + 5 in a cell. You see 9, and =4 + 5 remains, correctly, as the hidden formula.

    Now convert it and a destination cell into text. Copy using either internal text edit copy or whole-cell copy (marching ants). Paste into destination, and wham! You see =4 + 5 instead of 9 as the cell's visible value.


    Whew, that takes a load off my mind. I thought Excel was buggy instead of just merely clunkily designed.


    To sum up my long-winded thought process of discovery, perserved for historical reasons above, somewhere between the copy and paste Excel gets confused and converts a formula into text.



    [ This Message was edited by: AChimpNamedCornelius on 2002-08-23 13:52 ]

  6. #6
    Board Regular
    Join Date
    Aug 2002
    Location
    Emmen, Netherlands
    Posts
    256

    Default

    you are right AChimpNamedCornelius

    Then:
    select the cell containing "=4 + 5"
    press F2 to edit
    make no changes and hit return
    the result is "9"

    Henk

  7. #7
    New Member
    Join Date
    Aug 2002
    Posts
    3

    Default

    Thank you very much it works now!!!

  8. #8
    Board Regular shades's Avatar
    Join Date
    Mar 2002
    Location
    Near the Land of Oz
    Posts
    1,550

    Default

    I had this same problem earlier this week.
    - old, slow, and confused
    ... but at least I'm inconsistent -

    (retired Excel 2003 user, 3.28.2008)

  9. #9
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,945

    Default

    General method for fixing the following problem:

    "All my formulas have ended up in text formatted fields & are held as text values. Even changing the format doesn't seem to help".

    1) Go to - Edit | Go to | Special | Formulas. Press OK
    2)Go to - Edit - Replace...
    3) Find: =
    4) Replace with : =
    5) Click Replace all

    In most cases, has the 'effect' of re-entering the formula(s), & getting Excel to recognise it as a formula not a text string.

    Paddy



  10. #10
    Board Regular XL-Dennis's Avatar
    Join Date
    Jul 2002
    Location
    Ístersund, Sweden
    Posts
    1,922

    Default

    Paddy,

    Thanks for that trick

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com