Announcement

Collapse
No announcement yet.

Is Excel Infalliable?

Collapse

Unconfigured Ad Widget

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    Originally posted by bad hammy View Post
    It's based on one simple thing that's been broken for over three decades: in Excel, a spreadsheet program dedicated to the manipulation of numeric data, when you enter numbers in a cell they are treated as text instead of numbers unless preceded by an operator (=,+,-, etc). This is dumb: this is a numeric spreadsheet program - when I type in a number it should be treated like a number unless I say otherwise.
    I've done thousands of spreadsheets over the last 25 years and I don't understand this comment at all. The default cell format in Excel is General. That treats numbers as numbers. Other than the obvious minus signs before negative numbers, I never have to put an operator in front of a number to tell Excel it is not text.
    Sunlight is said to be the best of disinfectants

    Comment


    • #17
      I agree with Master403. All our Olympic databases, and my T&F databases, are done in Excel (originally) and the default variable type is general and it recognizes numbers as numbers. In fact, its the opposite problem - to get Excel to recognize a numeric cell as Text, if I want that, I have to use the workaround of starting any cell with a ' mark. I've never had the problem badhammy is describing.

      Comment


      • #18
        Originally posted by bambam1729 View Post
        I..n fact, its the opposite problem - to get Excel to recognize a numeric cell as Text, if I want that, I have to use the workaround of starting any cell with a ' mark. ....
        why not just go into Format and tell it to recognize that cell(s) as text?

        Comment


        • #19
          OK, you made me take another look. I was wrong about basic pure numeric entry - those are treated as numbers. (Shoulda tested my rant!) My new modified hangout rant is: if I enter 800+500 into a cell Excel doesn't calculate 1300, instead it treats the 800+500 as text.
          bad hammy
          Senior Member
          Last edited by bad hammy; 03-23-2020, 07:10 PM.

          Comment


          • #20
            Originally posted by bambam1729 View Post
            I agree with Master403. All our Olympic databases, and my T&F databases, are done in Excel (originally) and the default variable type is general and it recognizes numbers as numbers. In fact, its the opposite problem - to get Excel to recognize a numeric cell as Text, if I want that, I have to use the workaround of starting any cell with a ' mark. I've never had the problem badhammy is describing.
            Originally posted by gh View Post
            why not just go into Format and tell it to recognize that cell(s) as text?
            gh is correct, with one qualification: You have to format the cell before you enter any data. Once you have entered, say, 4:56.1 in a cell, Excel sees that as a time of day on January 1, 1900, and stores that as a decimal equivalent of fractions of a day from the reference day. From that point, Excel allows you to change the way that date and time are displayed, but the stored value is maintained. Once Excel has characterized the cell as a date, I have not been able to change its mind. So let's say you want to import combined event data as text to score the event. Format the cell first as text, then enter or import the data.
            Sunlight is said to be the best of disinfectants

            Comment


            • #21
              Originally posted by bad hammy View Post
              OK, you made me take another look. I was wrong about basic pure numeric entry - those are treated as numbers. (Shoulda tested my rant!) My new modified hangout rant is: if I enter 800+500 into a cell Excel doesn't calculate 1300, instead it treats the 800+500 as text.
              If you want Excel to perform an operation, put an equal sign in front of it. If there is no equal sign, it should be interpreted as text. Imagine the frustration if you tried to enter ZIP+4, say 94040-1234, and Excel changed it to 92806.
              Sunlight is said to be the best of disinfectants

              Comment


              • #22
                another thing that works is if you have a cell that's a number and you want it text, simply cut and paste from a known text cell and then retype the data; can be faster than running trhough the menu to change formatting.

                Comment


                • #23
                  I do format as text for large sets of text cells, but for an isolated cell, here or there, quicker to do the ' fix.

                  Comment


                  • #24
                    I dont think there is a pre-formatted cell in the column ,, both results are from same column of numbers... I turned it over to Master 403.

                    Comment


                    • #25
                      It's a rare day here when there are more posts on Excel than TNF!

                      I do feel everybody's pain, especially about the problems caused by Excel "helping" you with data typing. That's not the act of entering data by typing on your keyboard but deciding on the type of data your entry will be stored as - text, time, numerical etc.

                      This is different from formatting which is solely about presentation, although Microsoft unhelpfully confuse people by using the term formatting for both.

                      The problem is not really the help, which is usually pretty good for most people but the fact that it is not transparent and not controllable, except in very limited cases.

                      Your other options for bypassing auto-data typing are limited to annoying and incomplete workarounds. Even when you think you've killed it, it will spring back into life if you are not careful and ruin your day.

                      bad hammy is absolutely right about some aspects of Excel being broken for 30 years and this is one of them. There is nothing more basic in data handling than being able to control your data exactly how YOU want it, easily and reliably, and Microsoft has failed to provide a proper model and user interface for this since the beginning.

                      But hey, I had to use it for work since V2.0, so don't get me started listing all the issues!



                      Comment


                      • #26
                        Originally posted by Master403 View Post

                        If you want Excel to perform an operation, put an equal sign in front of it. If there is no equal sign, it should be interpreted as text. Imagine the frustration if you tried to enter ZIP+4, say 94040-1234, and Excel changed it to 92806.
                        Why? It should be the other way around like Lotus was. The vast majority of the time you enter something like 5+5 is to calculate not to show as a text. It's a spreadsheet not a word doc. Having to put an equal sign before it is an inefficient waste of keystrokes.





                        Comment


                        • #27
                          Originally posted by 502CD View Post
                          Why? It should be the other way around like Lotus was. The vast majority of the time you enter something like 5+5 is to calculate not to show as a text. It's a spreadsheet not a word doc. Having to put an equal sign before it is an inefficient waste of keystrokes.
                          Files>Options>Advanced>Lotus compatibility Settings>Transition formula evaluation
                          Check the box.

                          There is a second option for formula entry.
                          Sunlight is said to be the best of disinfectants

                          Comment


                          • #28
                            Excel is exonerated. It was a 45 cent error in writing check. discovered when I received this month's statement with a 45 cent credit.. Thanks to Master 403 for his prompting.
                            Today problem is printer refuses to print selected area of large spreadsheet,, say I have 13 print job backlog.. I do not know how to clear and Geek Squad is shut down for virus.

                            Comment


                            • #29
                              Originally posted by Master403 View Post
                              Files>Options>Advanced>Lotus compatibility Settings>Transition formula evaluation
                              Check the box.

                              There is a second option for formula entry.
                              Nice tip - thank you - I guess I'll have to totally retire my Excel rant! Don't use it enough to find other irritants. Pretty funny - Lotus 123 has been essentially dead for 25 years and Excel still maintains a Lotus mode.
                              bad hammy
                              Senior Member
                              Last edited by bad hammy; 03-24-2020, 10:16 PM.

                              Comment


                              • #30
                                Lotus 123 was a nice program that I started out using a lot. But moved to Excel about 1997 or so and actually liked it better than 123. Remember the Microsoft mantra in the 80s-90s - Windows isn't done until Lotus (123) doesn't run" They hated it which is why they pushed to make Excel, as at first all they had was Windows and Word

                                Comment

                                Working...
                                X