Getting false divide by zero in Excel

Multi tool use
Multi tool use











up vote
0
down vote

favorite












I have a column of numbers.



When I try calculating the standard deviation in Excel by using STDEV(A1:A97) I get a '#DIV/o!' notice. But there are no zeroes or false values in that range.



All the data is formatted as numbers with 3 decimal places.



What am I missing?






excel statistics







share|improve this question






















  • are you sure that you have the range correctly selected? The only case how I can reproduce #Div/0! is if I select only one cell for STDEV (as it divides by N-1 it would make sense to show this kind of error in such case). Also - sometimes even if the value is formatted as number it is still considered a text for functions. Try re-entering some of the values.
    – Pavel_V
    yesterday












  • Could you please share some data.. according to your description it should work.
    – Wizhi
    yesterday

















up vote
0
down vote

favorite












I have a column of numbers.



When I try calculating the standard deviation in Excel by using STDEV(A1:A97) I get a '#DIV/o!' notice. But there are no zeroes or false values in that range.



All the data is formatted as numbers with 3 decimal places.



What am I missing?






excel statistics







share|improve this question






















  • are you sure that you have the range correctly selected? The only case how I can reproduce #Div/0! is if I select only one cell for STDEV (as it divides by N-1 it would make sense to show this kind of error in such case). Also - sometimes even if the value is formatted as number it is still considered a text for functions. Try re-entering some of the values.
    – Pavel_V
    yesterday












  • Could you please share some data.. according to your description it should work.
    – Wizhi
    yesterday















up vote
0
down vote

favorite









up vote
0
down vote

favorite











I have a column of numbers.



When I try calculating the standard deviation in Excel by using STDEV(A1:A97) I get a '#DIV/o!' notice. But there are no zeroes or false values in that range.



All the data is formatted as numbers with 3 decimal places.



What am I missing?






excel statistics







share|improve this question













I have a column of numbers.



When I try calculating the standard deviation in Excel by using STDEV(A1:A97) I get a '#DIV/o!' notice. But there are no zeroes or false values in that range.



All the data is formatted as numbers with 3 decimal places.



What am I missing?






excel statistics




excel statistics






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked yesterday









Mate Mrše

459214




459214












  • are you sure that you have the range correctly selected? The only case how I can reproduce #Div/0! is if I select only one cell for STDEV (as it divides by N-1 it would make sense to show this kind of error in such case). Also - sometimes even if the value is formatted as number it is still considered a text for functions. Try re-entering some of the values.
    – Pavel_V
    yesterday












  • Could you please share some data.. according to your description it should work.
    – Wizhi
    yesterday




















  • are you sure that you have the range correctly selected? The only case how I can reproduce #Div/0! is if I select only one cell for STDEV (as it divides by N-1 it would make sense to show this kind of error in such case). Also - sometimes even if the value is formatted as number it is still considered a text for functions. Try re-entering some of the values.
    – Pavel_V
    yesterday












  • Could you please share some data.. according to your description it should work.
    – Wizhi
    yesterday


















are you sure that you have the range correctly selected? The only case how I can reproduce #Div/0! is if I select only one cell for STDEV (as it divides by N-1 it would make sense to show this kind of error in such case). Also - sometimes even if the value is formatted as number it is still considered a text for functions. Try re-entering some of the values.
– Pavel_V
yesterday






are you sure that you have the range correctly selected? The only case how I can reproduce #Div/0! is if I select only one cell for STDEV (as it divides by N-1 it would make sense to show this kind of error in such case). Also - sometimes even if the value is formatted as number it is still considered a text for functions. Try re-entering some of the values.
– Pavel_V
yesterday














Could you please share some data.. according to your description it should work.
– Wizhi
yesterday






Could you please share some data.. according to your description it should work.
– Wizhi
yesterday














1 Answer
1






active

oldest

votes

















up vote
0
down vote













Figured it out.



The problem was some of the cells contained a carriage return after the number, so I guess Excel counted those as zero.



After using the TRIM() function, I was able to calculate what I needed.






share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














     

    draft saved


    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53203676%2fgetting-false-divide-by-zero-in-excel%23new-answer', 'question_page');
    }
    );

    Post as a guest


















    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    0
    down vote













    Figured it out.



    The problem was some of the cells contained a carriage return after the number, so I guess Excel counted those as zero.



    After using the TRIM() function, I was able to calculate what I needed.






    share|improve this answer

























      up vote
      0
      down vote













      Figured it out.



      The problem was some of the cells contained a carriage return after the number, so I guess Excel counted those as zero.



      After using the TRIM() function, I was able to calculate what I needed.






      share|improve this answer























        up vote
        0
        down vote










        up vote
        0
        down vote









        Figured it out.



        The problem was some of the cells contained a carriage return after the number, so I guess Excel counted those as zero.



        After using the TRIM() function, I was able to calculate what I needed.






        share|improve this answer












        Figured it out.



        The problem was some of the cells contained a carriage return after the number, so I guess Excel counted those as zero.



        After using the TRIM() function, I was able to calculate what I needed.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered yesterday









        Mate Mrše

        459214




        459214






























             

            draft saved


            draft discarded



















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53203676%2fgetting-false-divide-by-zero-in-excel%23new-answer', 'question_page');
            }
            );

            Post as a guest






































































            tfcly2xLZc1nHijYEUMKvVgF8wFn5fqKuZC3OBl7xOTV 4Ww,fAKZKVgEPxh9o,M,9EgjnCb3tW1
            -
            tx,mLM3dm3P O,9,ZrTDhACSQeuYhcbQHz0d6N3Aw lXZ,JzoMSN8P M QgxxFjVi5Sl1rlSq

            Popular posts from this blog

            Schultheiß

            Image
            Der Titel dieses Artikels ist mehrdeutig. Weitere Bedeutungen sind unter Schultheiß (Begriffsklärung) aufgeführt. Ein Schultheiß. Holzschnitt von Peter Flötner (16. Jahrhundert) Der Schultheiß oder Schuldheiß (von althochdeutsch  sculdheizo ‚Leistung Befehlender‘, vgl. mittelniederdeutsch  schult(h)ēte , latinisiert (mittellat.) sculte(t)us , schwäbisch heute noch Schultes für „Bürgermeister“) bezeichnet einen in vielen westgermanischen Rechtsordnungen vorgesehenen Beamten, der Schuld heischt : Er hatte im Auftrag seines Herren (Landesherrn, Stadtherrn, Grundherrn) die Mitglieder einer Gemeinde zur Leistung ihrer Schuldigkeit anzuhalten, also Abgaben einzuziehen oder für das Beachten anderer Verpflichtungen Sorge zu tragen. Sprachliche Varianten des Schultheißen sind Schulte , Schultes oder Schulze . Früher wurde zwischen dem Stadtschulzen und dem Dorfschulzen unterschieden. In der städtischen Gerichts- und Gemeindeverfassung war er ein vom städtischen Rat oder vom L...
            Read more

            how to define a CAPL function taking a sysvar argument

            Image
            up vote 0 down vote favorite In Vector CANoe, is it possible to define a function that takes a system variable argument like the system function TestWaitForSignalMatch() ? For my use case it is not sufficient to supply the current value of the system variable because I want to pass the system variable to TestWaitForSignalMatch() or similar system functions. The CANoe help seems to show examples: long TestWaitForSignalMatch (Signal aSignal, float aCompareValue, dword aTimeout); // form 1 long TestWaitForSignalMatch (sysvar aSysVar, float aCompareValue, dword aTimeout); // form 3 I tried like this void foo(sysvar aSysvar) {} ^ or this void foo(sysvar *aSysvar) {} ^ but I get a parse error at the marked position of the sysvar keyword in both cases. I successfully created functions that take a...
            Read more

            Krone von Aragonien

            Image
            Wappenschild der Krone von Aragonien Die Länder der Krone Aragon (hellblaue Gebiete: nur kurzzeitig oder indirekt unter aragonesischer Herrschaft) Herrschaftsgebiet zur Zeit Ramon Berenguers IV. Krone von Aragonien im 13. Jahrhundert Krone von Aragonien im 15. Jahrhundert Unter dem Begriff Krone von Aragonien ( spanisch Corona de Aragón , aragonesisch Corona d’Aragón , katalanisch Corona d’Aragó ) werden Herrschaftsgebiete unterschiedlicher Verfasstheit zusammengefasst, die zwischen 1137 und 1516 bzw. 1714 in Personalunion von den Königen von Aragonien regiert wurden. Dazu gehörten Königreiche wie Aragonien, Mallorca, Valencia, Sizilien, Sardinien, Korsika und Neapel, das Herzogtum Athen und Neopatria, die Markgrafschaft Provence, die Grafschaften Barcelona, Roussillon und Cerdanya und die Herrschaft Montpellier. [1] Die Herrscher der Krone von Aragonien und von Spanien zählten und zählen in ihrer Titulatur eine große Anzahl von Herrschaftsgebieten auf. Dies...
            Read more